MIssing Transaction Log

[JefB]

We have a very large table that, unbeknownst to us, created a transaction log that was 26GB (yes, GB) in size.

Due to circumstances beyond my control, someone went in and deleted the log file. The only backup we have is from a month ago (don't yell at me, please). We cannot access the data.

Is there anyway to retrieve the data from the .mdf file when there is no .ldf file?

JefB

 

[tlbroadbent]

You can attach an MDF wihout an LDF by using sp_attach_single_file_db in SQL Server Query Analyzer. SQL Server will create an empty LDF file. The data should be intact. This system SP is documented in SQL Server Books online.

Make sure a backup and restore plan is developed and implemented immediately. See faq183-1784 "How Do I Develop a Backup and Restore Strategy?"

You or the DBA's may be want to read the following for future reference. "Shrinking Databases and Logs - SQL 7 and Higher" - faq183-1534. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[JefB]

Terry,

I entered the following in the Query Analyzer:

EXEC sp_attach_single_file_db @dbname 'LostDBXX', @physname = 'F:\mssql7\data\lostdbXX_data.mdf'

("lostdbXX_data.mdf" being the database with the missing log file)

I get the following error:
"Device activation error. The physical file name 'F:\mssql7\data\lostdb_LOG.LDF may be incorrect'

(Note that there is a "lostdb_LOG.LDF" file, but is the log file for a different database!)

If I had the "lostdbXX_LOG.LDF" file, I wouldn't be in this mess! Why is it looking at an unrelated log file? I thought that this procedure is supposed to rebuild a missing log file?

What am I missing?

JefB

 

[tlbroadbent]

The error occurs because the MDF file contains the name of the original MDF and LDF files. When you execute sp_attach_single_file_db, SQL attempts to create a file with the same name as the name stored in the MDF. If that file already exists, the process errors.

Use Enterprise Manager to attach the database. Right click on databases, open All Tasks | Attach database. You can change the file names for the MDF and LDF in the dialog. Enterprise Manager may display a warning and/or error message but should attach the file and create the new log file.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[JefB]

I tried attaching (in Ent Mgr), changing the names of the log file to match the current name of the database.

The "Go"(?) button is grey-ed out until you hit the "Verify" button, which changes the name back to the original!

I have tried changing file names, attaching, unattaching, etc. I don't know what I have missed.

At this point I have 360MB of data that I cannot access because a log file was deleted and the log file from the backup will not work with the .mdf file!

I am open to suggestions here! Either way, Terry, I appreciate your help.

Jef Block

 

[tlbroadbent]

Which version of SQL Server are you running? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[JefB]

SQL2000