Recovering from a deleted log file on SQL Server

Emails Received in Response to this Article

Back to Article

Introduction

The following are emails that were received since this article was posted. Names modified to protect the innocent.

Emails

Date: 9/10/02 2:21:35 AM
From: Martin
Subject: Thanks

Your info on recovering data from a sql server databse with only a
suspect mdf file and no logs proved invaluable yesterday when one of our
clients turned out to be an idiot.
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log
.html

Thanks ;-)

Martin

Date: 9/10/02 3:20:07 AM
From: Herbert
Subject: Recovering from a deleted log file on SQL Server

HI!

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
Do a Attach DB from Enterprise Manager. It is attaching the database and is automatically rebuilding the transaction log. Afterwards the database is in normal mode again.

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!

Best regards

Herbert

Date: 9/23/02 5:50:19 AM
From: arun
Subject: thanks

Dear Travis,

Last week we had a terrible problem.

our server got crashed with all the hard disk in it. Luckily we
managed to pull the mdf file of our 1 GB database.

But could nt retrive the log file.

We tried so many things to retrieve the data from it.

But couldnt.

Then tried in the net and found your tip for how to retrieve the
data if log file is got deleted.

We followed ur instruction.

We made a new database and changed the mdf file with the crashed
mdf file.

then made a situation that the db status was showing as suspect.

we opened sysdatabase table, changed the number to 32768 and
then made a new db and through the Import option we transfered
the whole data to the new db.

We retreived the whole data without missing a single record.

I wanted to thank u from the bottom of my heart.

THANKS A LOT

BYE

ARUN ( India )

Date: 10/8/02 12:21:05 PM
From: Colin
Subject: sqlserver recover from deleted log

Thanks for the info. Your instructions simple and easy to follow and worked.
We had a problem with a backed up the log file that didn't restore correctly.

Colin

Date: 11/11/02 7:29:29 AM
From: Bruce
Subject: I owe you a pot of gold

Travis,

I have no idea how a friend of mine found your article on "Recovering
from a deleted log file on SQL Server ... but it saved my bacon.

I had a database that I was running some older scripts that required
a lot of Updates - thus the log file grew and grew until it filled up
the drive.

I have never filled up the drive before but I have frequently have
trimmed down the log file when it has gotten too large and the reason
is me massaging data for a special export of the data. Anyway the
process has been 1) detach the database 2) delete the log file and 3)
do a sp_single_file_attach_db command and off to the races.

Thing time it did not attach but gave me the following error:

> >> Server: Msg 1813, Level 16, State 2, Line 1
> >> Could not open new database 'SFIP_Main'. CREATE DATABASE
> >> is aborted.
> >> Device activation error. The physical file
> >> name 'E:\sfip_main\SFIP_Main_log.LDF' may be incorrect.

Yikes!!!!!!!!!!!!

Your experience has saved me much grief as I could not duplicate the
processing that happened over the last two days (no backup of
course). This was a long weekend not knowing what to do and knowing
that the database was required.

Thanks again for taking the time to publish your experience.

I owe you.
Bruce

From: Wilkens
Sent: 12/15/02 2:51 AM
Subject: Thanking you for Help !!

Dear mr travis,

I'm writing this email, regards a huge help that you gave me.
I had a suspect database, yestarday, and the company, get lost the Backup, after I found a document writed by you, i figured out how to fix that, and we could recovery the data base.

Thankyou very much for your help, and if you need something from Brazil, Amazonas, don't hesitate to e-mail me at <removed>

Best Regards

Wilkens

Date: 1/23/03 4:34:08 PM
From: Fobico
Subject: About "Recovering from a deleted log file on SQL Server"

I LOVE YOUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU

Date: 2/6/03 2:23:29 PM
From: <removed>
Subject: About your SQL recovery Page

Just to let you know, you can skip to step 9 in your
instructions at
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html

I tried it today and it worked.

Thanks
Jeff

Date: 2/19/03 8:54:30 AM
From: Gustav
Subject: Thank you, thank you, thank you....

...for the tutorial on the net on how to restore a deleted SQL2000
transaction log file. It worked like a charm. You have saved my ass!

Gustav

Date: 3/26/03 2:27:25 AM
From: Guido
Subject: Recovering from a deleted log file on SQL Server

Hi Travis

In http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log
.html you mention that
<QUOTE>
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.
</QUOTE>

I did exactely the same and it worked, you might have a few probs with
views though, but on the tables itself it worked a treat and that for me
was already 99% of what I needed.

You will still have to copy/rebuild ALL functions and triggers

Cheers for the tip

Guido

Date: 3/27/03 8:12:17 AM
From: Matt
Subject: recovering from a deleted log file on sql server

thank you so much

this document saved my life...well possibly not life but job!

Matt

Date: 3/31/03 8:12:59 PM
From: Darwynn
Subject: Thank You incredibly

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 2 months!

I, too, tried rebuild_log and some other things that didn't work whatsoever so I decided to just check out what I could find. I saw your article and decided to give it a shot.

I created a brand new database, stopped SQL, replaced the new mdf file with the mdf file from the database that would not attach and started SQL. This brought the database back but it was in Suspect mode. I used your instructions and put the database in Emergency mode and was able to get the data out. I did have to use BCP because DTS errored out on me but it worked like a charm!

If I were a rich man, I'd give you cash but I wanted to make sure that I at least thanked you and let you know that it helped out.

Thank you,
Darwynn

Date: 4/4/03 4:21:23 PM
From: Mark
Subject: SQL Server Recover from deleted log...

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!

Thanks

-md

Date: 4/9/03 12:32:05 AM
From: Lance
Subject: Great Job

Travis;

Your online instructions on recovering a suspect database
saved my bacon.

Thanx for taking the time to put it online.

Great Job!!!

Lance

Date: 4/9/03 11:16:53 AM
From: AUjang
Subject: Just wanna say thanks

Hi Travis,

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.

-Azmizar

Date: 4/29/03 12:37:35 PM
From: bolan
Subject: Thank you

Hi,

Thank you for the article "Recovering from a deleted log file on SQL Server". It's just perfect.
Just one question. In the step 10 of your article, you said just copy tables and views. I also tried "Copy objectes and data between SQL Server databases". It works fine.
For the first option, it works. But the index is not copied. If you choose "Copy objectes and data between SQL Server databases", the index is also copied.
I want to know if you recommend "Copy objectes and data between SQL Server databases". If you don't, why?
Thanks again and again!
Best Regards,

Bolan

Date: 5/13/03 12:51:29 AM
From: Michael
Subject: Recovering from log file deletion

Travis,

I just recovered from a log file deletion using STEP 9 and 10. Worked like a charm, many thanks.

Regards,

Date: 6/9/03 12:44:26 PM
From: Vaidas
Subject: SQL problems and yours advice

Hello,

I had problem with my sql - after power down it swithed into suspend
mode... I read your article at
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html
It helped me very much and I would like to thank you. By the way, firstly
I tried only 9-10 steps and failed. After doing all steps i succeded. So
do not get rid of them.

Regards,
Vaidas
Lithuania.

Date: 7/10/03 3:46:33 PM
From: Keith
Subject: Recovering from a deleted log file on SQL Server

Hi friend-
This thing is brilliant, it saved us much grief.
Our reason: (which you could add as #2 :-)
You keep your log files and data files on separate disks, and the disk with
your log files fails.

I, too, started from step 9, and it worked great. FYI I also omitted step
9.6 (stopping and starting mssqlserver service) and it worked fine.

Thanks again.

-Keith, Asst DBA

Date: 8/25/2003 12:28 PM
From: Sean
Subject: Thanks, and an update to add to your instructions.

Thank your for your instructions on "Recovering from deleted log file on SQL Server.

http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html

You're instructions also work to allow one to salvage data from a damaged SQL data (.MDF) file.

We had a customer who's SQL Server database became damaged.
They had no recent backup. (A year ago was the most recent.)

I had one of our service technicians bring me their 1.8 GB MDF file so I could see what I could do.
(Not knowing if anything was even possible, but I was willing to research it.)

I had started following these instructions:
http://groups.yahoo.com/group/sql_server7_forum/message/4017

But then realized this was a dangerous route because of:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9011

Having done the above up until the DBCC REBUILD_LOG, (an apparent NO-NO), I was left hanging, but then found your instructions which finished things off easily using the DTS.

I was able to use DTS to copy data from the damaged (in Emergency Mode) database) to a new database. All the tables except one copied. And I was able to get the first 10201 rows of that table by exporting it to a CSV. That one table was damaged with invalid cross-linking.

Anyhow, I thought you should know that your instructions were good for something else.
And it does seem to be the only documented set of instructions to help with these situations.
Everyone else assumes that valid backup files exist.

Thanks again,

Sean

Date: 17/12/2003 4:16 AM
From: Johan
Subject: sqlserver_recover_from_deleted_log

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.

Best regards,

Johan

Date: 08/01/2004 9:17 AM
From: Pat
Subject: Instructions for recovering SQL Server without log

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.

Thanks,
Pat