HELP!!!!!!!!!! How to recover suspect sql 2000 database
I have a sql 2000 database that has been made suspect by having the log file deleted then replaced with an older copy. This is what happened. The database went into readonly mode because the log file had gotten so big it filled the volume. Well my programmer deleted the log file thinking he could just put in an empty dummy in its place. As you know this would not work so he restored a log file that was on a 3 day old backup. This made the database suspect. I have detached the database at this time and it won't reattach. Any suggestions?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My database is already dettached. When Iry to attach without a log file I get an error that tells me "the physical file name may be incorrect. When I use a dummy empty text file I get an i/o error telling me I reached the end of file trying to read the log file. When I dettached this database, it was already in the suspect mode.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That second link doesn't work correctly, it will need to be copied and pasted into a browser
http://msdn2.microsoft.com/en-us/library/ms189493(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms189493(SQL.90).aspx
I agree -- you have very likely damaged the db, possibly beyond the point of recovery.
For the future, remember these rules:
Rule 1) *NEVER* detach a db that has any problem.
SQL may not allow the db to be re-attached.
Rule 2) *NEVER* use EM to do a detach/attach. *ALWAYS* use QA.
EM is too flaky and can't be trusted for that, I've seen too many issues with it.
Rule 3) *ALWAYS* backup a detached mdf before attempting to (re)attach it if you suspect that for any reason the attach won't work.
For each attach attempt, use a fresh copy of the original .mdf. The attach attempt will update the .mdf, likely preventing future attach even once you correct the previous error(s).
If the db is very large, you might have to skip this. If so, if possible, take a complete backup of the db before beginning any activity.
For the future, remember these rules:
Rule 1) *NEVER* detach a db that has any problem.
SQL may not allow the db to be re-attached.
Rule 2) *NEVER* use EM to do a detach/attach. *ALWAYS* use QA.
EM is too flaky and can't be trusted for that, I've seen too many issues with it.
Rule 3) *ALWAYS* backup a detached mdf before attempting to (re)attach it if you suspect that for any reason the attach won't work.
For each attach attempt, use a fresh copy of the original .mdf. The attach attempt will update the .mdf, likely preventing future attach even once you correct the previous error(s).
If the db is very large, you might have to skip this. If so, if possible, take a complete backup of the db before beginning any activity.
Sadly you may have to walk away from trying to reattach the damaged db and do a restore with move option to restore the 3 day old backup and lose all of the changes since then (unless you have transaction backups).
ASKER
Thanks for the input from everyone. I called mircosoft and with some assistance from them I was able to recover my server. FYI. here are the steps I took.
I copied my mdf, ndf and ldf files of the orignal database to another location. I then deleted the files from the locations they were at on the drive.
I "created" the same exact database with the same structure and names. I had my database spread across 2 drives.
I then took the server down, renamed my log file, copied my original mdf and ndf files over the newly created files.
Start sql and the database of course came up suspect.
I started query analyzer to the master or temp database.
I then ran these series of statements
exec sp_configure 'allow override',1
reconfigure with override
update sysdatabases set status = 32768 where name='dbname'
dbcc rebuild_log
This rebuilt the transaction log.
I then ran
dbcc checkdb('dbname')
all the tables checked out and everything seems to be fine.
I copied my mdf, ndf and ldf files of the orignal database to another location. I then deleted the files from the locations they were at on the drive.
I "created" the same exact database with the same structure and names. I had my database spread across 2 drives.
I then took the server down, renamed my log file, copied my original mdf and ndf files over the newly created files.
Start sql and the database of course came up suspect.
I started query analyzer to the master or temp database.
I then ran these series of statements
exec sp_configure 'allow override',1
reconfigure with override
update sysdatabases set status = 32768 where name='dbname'
dbcc rebuild_log
This rebuilt the transaction log.
I then ran
dbcc checkdb('dbname')
all the tables checked out and everything seems to be fine.
ASKER
This question can be closed
Once it is done, u can dettach the database, delete the log file, reattach the mdf file. U need not have to create a dummy log file. SQL Server will create one if it is not present.
Regards,
RKM.