February 28, 2003 at 6:22 am
My DB server completely locked up while doing a transaction log backup. The server had to be re-booted. Upon restart, the DB was 'Suspect'. I tried resetting the status but it failed. So, due to time issues, I made copies of the .mdf and .ldf files and restored the DB from backup. This worked fine. To try and retrieve the data missing since last backup, I am trying to reattach the .mdf and .ldf as a new DB. However running the sp_attach_db command fails with:
Server: Msg 3624, Level 20, State 1, Line 1
Location: pageref.cpp:3899
Expression: pPage->GetLsn () == lp->GetPrevPageLsn ()
SPID: 18
Process ID: 612
Connection Broken
Trying to use sp_attach_single_file_db @dbname='CCW', 'f:\datacopy\tlwcopy.mdf' fails with:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'd:\mssql7\data\tlw.ldf' may be incorrect.
The physical file referenced ('d:\mssql7\data\tlw.ldf') is the original log file. I thought that this SP was supposed to create a new log file and I assumed it would give it the same name as the database with .ldf extension ('ccw.ldf').
Anyone have any suggestions on what I should try next to get the DB reattached so I can retrieve data?
Thanks!
February 28, 2003 at 7:40 am
I think you'll have to detach the new db and temporarily rename the files.
Andy
March 4, 2003 at 8:46 am
Andy, thanks for the suggestion. I tried it and it failed. I then tried on a completely different server and that failed too.
Eventually a co-worker called MS and they were able to re-attach us in emergency mode so that we could retrieve our data. I was not involved in the phone call or the re-attaching process, but my co-worker took some basic notes which I would be happy to share but I would hate to post the procedure if it is inaccurate or incomplete...
March 4, 2003 at 9:28 am
Add a disclaimer! Please do post.
Andy
March 4, 2003 at 10:29 am
Here are the following steps that got my corrupted and detached DB re-attached and into emergency mode so that I could retrieve data from it. NOTE THAT DATA WAS LOST USING THIS PROCEDURE, but it was the lesser of two evils. This was done by a co-worker so I can't confirm its completeness but am posting it for informational purposes.
Commands are bolded and Italicized
Made copies of the corrupted .mdf and .ldf files prior to starting.
sp_configure 'allow', 1
Sets global configuration settings for the current server and puts the database in a mode that allows us to change the system tables
reconfigure with override
Updates the value from previous command sp_configure system stored procedure.
update sysdatabases set status=32768 where name='DB_recover'
where DB_recover is the name of the new DB to be recovered to and sets it to emergency mode
dbcc checkdb('DB_recover')
checked the database. In my case it failed to load due to errors
dbcc checkdb('DB_recover', repair_allow_data_loss)
opened the database, fixed what it could and ignored what it couldn't. NOTE THAT DATA WAS LOST DOING THIS. See books on-line for other options in place of repair_allow_data_loss.
See this article for putting the DB in Emergency mode:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;165918
March 4, 2003 at 12:54 pm
Great post! Thanks for taking the time to add good notes.
Andy
March 6, 2003 at 8:26 am
cwitucki, why not perform REPAIR_FAST or REPAIR_REBUILD before use REPAIR_ALLOW_DATA_LOSS?
These repairs can be done without risk of data loss.
Just curious...
Franco
Franco
March 6, 2003 at 8:56 am
Franco,
I think you should definitely want to try the options to not lose data first. In our case, they did not fix the DB so we could reattch. So, we had to use of repair_allow_data_loss option.
You make a good point though. Try the least intrusive options first. They just did not work for us. I should have posted it saying that...
March 6, 2003 at 1:30 pm
Great documentation on your recovery process.
One thing you might try (although we hope there is no next time)... after setting the status to emergency mode and BEFORE running DBCC.
execute DBCC REBUILD_LOG ( 'DB_Recover', '<path to new log file>' ).
I would only recommend this because the original error was
The physical file name 'd:\mssql7\data\tlw.ldf' may be incorrect.
Anyway - thanks for the information.
Guarddata-
March 7, 2003 at 5:07 am
Thanks for the suggestion. I am really tempted to go try it just to see the results (on a non-production server of course). To anyone unfamiliar with the DBCC REBUILD_LOG, make sure you note guarddata's comment:
I would only recommend this because the original error was
The physical file name 'd:\mssql7\data\tlw.ldf' may be incorrect.
Also, I have copies of the .mdf and .ldf files to fall back to in case of really disasterous results.
June 28, 2005 at 7:58 am
Thanks for the step of instructions.
However, when I try to run the DBCC Checkdb statements when the database is in 'Emergency' mode. I get an error stating that the
Could not run BEGIN TRANSACTION in database 'Training_Testing' because the database is in
bypass recovery mode.
The statement has been terminated.
I need to run the DBCC to fix torn pages(if possible). How did you run your DBCC statements when you had the db in emergency mode?
Thanks
June 28, 2005 at 8:30 am
As far as I know emergency mode opens database without transaction log which implies that you cannot make any changes to the data.
So you can run dbcc checkdb only without repair clauses.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply