Recovering from a deleted log file on SQL Server

Introduction

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:

1. You're log file got too big so you decided to shutdown SQL Server, then delete the log file.
2. Salvage data from a damaged SQL data (.MDF) file. ( Someone emailed about this ).

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.

Instructions

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: knowledge@spaceprogram.com.

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

1. Backup the data (.mdf) file! Just in case. We take no responsibility for anything that happens following this procedure.
2. EXEC sp_detach_db 'dbname' -- this will detach the database from the server
3. Restart SQL Server
   The database may still be seen in enterprise manager, but just ignore it.
4. Create a new database with the same name or a different name. You will have to use a different physical file name, which is fine.
5. Stop SQL Server.
6. Rename the new data file that was created to something else (ex: add.bak to the end)
7. Rename the old data file that you want to restore to the name of the newly created file (the same name as the file you changed in the step above)
8. Start SQL Server
   Now the db will still be suspect but you now have a log file.
9. Switch to emergency mode on the database. You do this by doing the following:
   1. Right click on the database root node in Enterprise manager and bring up the properties.
   2. Under the Server Settings tab, check of "Allow modifications to be made directly to the system catalogs".
   3. click ok
   4. Now go to the master database and open the sysdatabases table.
   5. Find the suspected database in here and modify the status column, setting it to: 32768. This will put it into emergency mode.
   6. stop then start sql server
10. Now here's the tricky part and I'm not sure how this will work on a single install, i was lucky enough to have SQL Server 2000 installed. But anyways, open up the Import and Export Data (DTS) program from the start menu. And you want to copy data from the old database to a brand new one. Just copy tables and views.

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.

Read the emails here