Recovering from a deleted log file on SQL Server
Emails Received in Response to this Article
The following are emails that were received since this article was posted. Names modified to protect the innocent.
Date: 9/10/02 2:21:35 AM
Your info on recovering data from a sql server databse with only a
Date: 9/10/02 3:20:07 AM
Found your article in spaceprogram.com:
If you want to set the database in emergency mode, you don’t have to do points 2 to 8 because you don’t need a transaction log file for this mode. If you are doing 2-8 you have a transaction log file which is not used, because it is not the original log file. That’s the reason, why your database is still in suspect mode. You can find information about it on support.microsoft.com. BTW in emergency mode you can always do SELECT and BCP. Only Transactions are not allowed.
Another quick and dirty way you can do:
Do sp_detach_db ‘dbname’ from Query Analyzer, it is not working
with suspect databases from Enterprise Manager
This procedure is only working, if there originally only one logfile existed. If you had more than one, you have a problem. But there is no reason for having more than one, normally, because it is written sequentially and two are used as one.
Also with this method you can lose data because the original logfile is lost and you have to check the database afterwards.
Hope that helps. If you have any suggestions, please send me an email!
Date: 9/23/02 5:50:19 AM
Last week we had a terrible problem.
our server got crashed with all the hard disk in it. Luckily we
But could nt retrive the log file.
We tried so many things to retrieve the data from it.
Then tried in the net and found your tip for how to retrieve the
We followed ur instruction.
We made a new database and changed the mdf file with the crashed
then made a situation that the db status was showing as suspect.
we opened sysdatabase table, changed the number to 32768 and
We retreived the whole data without missing a single record.
I wanted to thank u from the bottom of my heart.
THANKS A LOT
ARUN ( India )
Date: 10/8/02 12:21:05 PM
Thanks for the info. Your instructions simple and easy to follow and
Date: 11/11/02 7:29:29 AM
I have no idea how a friend of mine found your article on "Recovering
I had a database that I was running some older scripts that required
I have never filled up the drive before but I have frequently have
Thing time it did not attach but gave me the following error:
> >> Server: Msg 1813, Level 16, State 2, Line 1
Your experience has saved me much grief as I could not duplicate the
Thanks again for taking the time to publish your experience.
I owe you.
Dear mr travis,
Date: 1/23/03 4:34:08 PM
I LOVE YOUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
Date: 2/6/03 2:23:29 PM
Just to let you know, you can skip to step 9 in your
I tried it today and it worked.
Date: 2/19/03 8:54:30 AM
...for the tutorial on the net on how to restore a deleted SQL2000
Date: 3/26/03 2:27:25 AM
I did exactely the same and it worked, you might have a few probs with
You will still have to copy/rebuild ALL functions and triggers
Cheers for the tip
Date: 3/27/03 8:12:17 AM
thank you so much
this document saved my life...well possibly not life but job!
Date: 3/31/03 8:12:59 PM
Long story short, your article at http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html
saved a few asses. One of the people that I work with was working with
a client of ours and tried getting rid of some log space by detaching
a database, deleting the log file and attaching it again. Normally this
works fine but for some reason the database would not attach. Unfortunately,
this person didn't bother to see that there were no backups in the last
Date: 4/4/03 4:21:23 PM
Please don't delete steps 1-8. I had an MDF file and a new server, and
this was all I could do. It would have taken me a while to figure out
1-8 if they hadn't been there. I managed to recover the MDF file from
a failed server and that was it so this really saved me!
Date: 4/9/03 12:32:05 AM
Date: 4/9/03 11:16:53 AM
Just wanna tell you that your webpage: http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html helped me a lot today. It is nice to know people still wanna share their experiences (even the bad ones). Thanks again.
Ooo .. I followed the instruction 1 to 9.
Date: 4/29/03 12:37:35 PM
Thank you for the article "Recovering from a deleted log file on
SQL Server". It's just perfect.
Date: 5/13/03 12:51:29 AM
I just recovered from a log file deletion using STEP 9 and 10. Worked like a charm, many thanks.
Date: 6/9/03 12:44:26 PM
I had problem with my sql - after power down it swithed into suspend
Date: 7/10/03 3:46:33 PM
I, too, started from step 9, and it worked great. FYI I also omitted
-Keith, Asst DBA
Date: 8/25/2003 12:28 PM
Thank your for your instructions on "Recovering from deleted log
file on SQL Server.
Date: 17/12/2003 4:16 AM
Many thanks for the instructions on your site. It helped us in Germany last Monday and you saved my ash.
Many thanks and happy hollidays.
Date: 08/01/2004 9:17 AM
Hello, We had a customer who's transaction log was somehow deleted and we followed your instructions to get them back up and running. However, this database contained several filegroups (15) which added a bit of complexity. After several hours working with Microsoft Database Corruption experts, we were able to get the database back up and running. The trick to the whole thing was making sure the Dummy Database that was created contained the filegroups in the EXACT order of the original production database. Our problem was the order (select * from sysfiles) was not what it needed to be, so SQL Server would generate an error ("this file is not a primary file") when the service was restarted. The database would then be marked as "SUSPECT/EMERGENCY". In this case, we could only query tables that were in the PRIMARY mdf file, not any other filegroups. Once the order and filegroup-to-filename mappings were correct, we ran the DBCC REBUILD_LOG statement and then DBCC CHECKDB to verify integrity. These steps recovered the database successfully.
|Products | Services | Blog | Open Source | About | Contact Us|
|Copyright © 2001-2002 - Space Program Corp.|