Recovering from a deleted log file on SQL Server
This document will give instructions on how to recover from a deleted database log file on Microsoft SQL Server. The database this was done on was SQL Server 7 with SQL Server 2000.
You will get a suspect beside the name of the database when this happens.
You may be interested in the document if one of the following happened:
That's the only reason I can think of right now and that is the reason I have come to do figuring this out, so here goes.
If you have a recent backup of the database, USE IT! Forget about this article and do a normal restore procedure. Otherwise read on.
First of all, you are SOL if you want a full recovery. You just can't get all the data back because the log file itself contained a lot of transactions that may never have made it to the data file.
So anyways, I read just about everything possible on this topic and nothing worked, I mean nothing. I tried going into emergency mode, running stored procedures that did squat, using sp_detach_db, then sp_attach_db, etc, even trying db_rebuild_log() (the rebuild_log thing seems to be an undocumented feature that someone must have figured out... easter egg?? not really since there are some serious warnings against using it, but hey, when you're in dire straits, you'll try anything once, right?).
This restore procedure doesn't seem to be formally documented anywhere. I guess you're just not supposed to be this stupid, but everybody makes mistakes don't they? And hey, I'm no DBA or anything! I just use the damn things. ;-)
Anyways, on to the guts of the article. I am trying to make this as simple as possible and pulling this from memory so if there is something I am missing, please don't hesitate to e-mail us at: firstname.lastname@example.org.
There's one thing to note here and if someone could verify it, that would be great. First thing I'd like to verify is if you can just skip to step 9 right off the bat? So as soon as you get a suspect database, can you just start at step 9? Can someone please try that and let me know. SEE UPDATE: JAN. 6, 2003 BELOW
And voila, this should work smoothly. Let me emphasize should.
UPDATE: Jan. 6, 2003
I just received an email from someone who tried starting at step 9 and he said it worked. If I can get someone else to confirm that, then I'll get rid of 1-8.
UPDATE: June 18, 2003
It seems that some people can just do 9-10 and some have to do all the steps.
Emails Received in Response to this Article
There have been many emails we have received about this article both sharing their experience and praising the instructions.
|Products | Services | Blog | Open Source | About | Contact Us|
|Copyright © 2001-2002 - Space Program Corp.|