DATABSE IS SUSPECT BECAUSE OF MISSING FILES

My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James Colbert
See if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>
|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James
|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James
|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James

DATABSE IS SUSPECT BECAUSE OF MISSING FILES

My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James ColbertSee if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx
.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
---
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx
.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/defaul...id=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx
.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James

database use syntax help

I have database called 'test1' and when i use the code below im getting this error
Server: Msg 170, Level 15, State 1, Line 9
Line 11: Incorrect syntax near '@.dbName'.

plz help, here is the code

DECLARE @.dbName varchar(50)
DECLARE @.index int

SET @.index = 1
SET @.dbName ='test'

set @.dbName = @.dbName + CAST(@.index as varchar)
use @.dbNameu cannot do that. if u want to select rows from table1 of test1 database
select * from test1..table1

or u can use dynamic sql like

DECLARE @.dbName varchar(50)
DECLARE @.index int

SET @.index = 1
SET @.dbName ='test'

set @.dbName = @.dbName + CAST(@.index as varchar)
exec ('select * from ' + @.dbName + '..table1')

remember dynamic SQLs r having security issues if not managed carefully|||thanks for replying. but i want to use 'use' syntax not 'exec'

this works fine
use test1
select * from table1

but when i use this i get the error
use 'test' + '1'
select * from table1|||Everything Upsalen has said is correct. His code is equivalent to what you requrest.

If you really must use USE then:
DECLARE @.dbName varchar(50)
DECLARE @.index int

SET @.index = 1
SET @.dbName ='test'

set @.dbName = @.dbName + CAST(@.index as varchar)
exec ('USE ' + @.dbname + ' GO select * from table1')|||The reason i want to use 'USE' is i have around 20 database (test1,test2,test3,...test20) with same schema, everytime when i make update or fix scripts i have to run it 20 times. now i want to use loop and run it once for all the databases.

here is what the code looks like...

DECLARE @.dbName varchar(50)
DECLARE @.index int
DECLARE @.NUM_OF_DB int

SET @.NUM_OF_DB = 20

SET @.dbName = 'test'
set @.index = 1

while @.index <= @.NUM_OF_DB
begin

set @.dbName = dbName + CAST(@.index as varchar)
USE @.dbName

-- paste here the scripts (script is thousands of lines long)

set @.index = @.index + 1
SET @.dbName = 'test'
end
or if you know easy way to do this plz ...|||yes, i know easy way to do this

if you have 20 databases which are essentially identical in structure (they would have to be if you can run the same thousand-line script on their tables), then just combine them into one database and vwalah, your USE problem goes away

:)|||Rudy's suggestion. My Code. Upsalen's code. Your code does not, and cannot, work. Unless perhaps you write something in .NET or similar and execute it from there. You cannot do what you want to do from QA\ SSMS.|||yes, i know easy way to do this

if you have 20 databases which are essentially identical in structure (they would have to be if you can run the same thousand-line script on their tables), then just combine them into one database and vwalah, your USE problem goes away

:)
c'mon......i have good reason not to do that|||okay, you have good reason

how about this: write 20 scripts, each with a different USE, each calling the same common script

:cool:|||You may be able to write some code using ADO\ ADO.NET - execute your script, looping through the various databases, changing the database at each pass.

I suspect that Rudy's point is that if you have 20 databases of all identical schemas then perhaps your design is flawed... Perhaps it isn't. But perhaps it is.|||You may be able to write some code using ADO\ ADO.NET - execute your script, looping through the various databases, changing the database at each pass.

I suspect that Rudy's point is that if you have 20 databases of all identical schemas then perhaps your design is flawed... Perhaps it isn't. But perhaps it is.

about design, 20 of them are same database. i just use them for training, testing,etc which means at the end it is one database.

where can i find code for ADO\ ADO.NET.|||were can i find code for ADO\ ADO.NET.You'll have to write it I am afraid. I don't have time to come up with any right now.|||I suspect that Rudy's point is that if you have 20 databases of all identical schemas then perhaps your design is flawed... Perhaps it isn't. But perhaps it is.well, yes, that was my point in post #6

but then, after having been assured that there is good reason for 20 databases, i put forth in post #9 a modest programming suggestion (not requiring ADD or whatever that was)

this suggestion, had it been undertaken, would have solved the problem elegantly

and about half an hour ago

:)|||well, yes, that was my point in post #6

but then, after having been assured that there is good reason for 20 databases, i put forth in post #9 a modest programming suggestion (not requiring ADD or whatever that was)

this suggestion, had it been undertaken, would have solved the problem elegantly

and about half an hour ago

:)
okey im just looking for easy way. how can i call common script?
to explain about db design i have one database copied 20 times for different purpose.|||ADO - ActiveX Data Objects
not to be confused with Data Access Objects which are totally different ;)

Yes - I missed the calling thingy - probably easier than ADO.|||another simple way to do this is to write a .bat file that calls osql.exe or sqlcmd.exe 20 times with the same script, each time connecting to a different database.|||another simple way to do this is to write a .bat file that calls osql.exe or sqlcmd.exe 20 times with the same script, each time connecting to a different database.
nice one i will give it a try|||Just wondering, does

DECLARE @.dbName varchar(50)
SET @.dbName = 'test'
USE @.dbName

Work?
If so, I might have a suggestion :p|||Just wondering, does

DECLARE @.dbName varchar(50)
SET @.dbName = 'test'
USE @.dbName

Work?
If so, I might have a suggestion :pNope :)|||another simple way to do this is to write a .bat file that calls osql.exe or sqlcmd.exe 20 times with the same script, each time connecting to a different database.
as your suggestion i write simple batch file and its working great just the way i want it. thank you men you saved me lot of time.
here is what the batch file looks like

SET YOGI_HOME=%cd%
SET /P IN_PUT_FILE=Script file name:
SET OUT_PUT_FILE=out.txt
SET NUM_DB=20
SET i=1
.
.
.

:Loop
Echo Executing script file %IN_PUT_FILE% on test%i%. . .
cd C:\Program Files\Microsoft SQL Server\80\Tools\Binn\
isqlw -s [Local] -d [test%i%] -E -u [sa] -p [password] -i [%YOGI_HOME%\%IN_PUT_FILE%] -o [%YOGI_HOME%\%OUT_PUT_FILE%]
.
.
.