Attach DB with corrupt .ldf?

Question

Attach DB with corrupt .ldf?

Natalya Faden

SSC Veteran

Points: 287
More actions
September 13, 2004 at 12:43 pm

#109008

Hey gang,
I've been trying to restore a DB from it's MDF and LDF all morning; the catch? The LDF appears to be corrupt.
When I first started the SQL Server, the db in question was marked as "Suspect'. I did some research on this and it has caused me to attempt detaching, backing up, deleting the LDF, using ATTACH DB and even sp_attach_single_file_db, and sp_add_data_file_recover_suspect_db.
Nothing works.
So, with an MDF and no LDF ... is it possible to recreate this database somehow? Can I attach the MDF to another (empty) database to retrieve it's contents? (Tried it, couldn't get it to work), can I extract the contents of the MDF - even if it's just the objects and not the data itself - in some capacity?
I've read in various places about attaching an MDF with no LDF and the system will recreate the LDF as needed, but that doesn't work either.
Ideas are most, most, most welcome.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Natalya Faden SSC Veteran Points: 287 More actions · Answer 1

Sorry guys, my tenacity wouldn't let me sit on this one and I dug up the solution:

LISTING 1: Undocumented DBCC Command REBUILD_LOGEXEC sp_configure 'allow updates', 1RECONFIGURE WITH OVERRIDEGOBEGIN TRANUPDATE master..sysdatabasesSET status = status | 32768WHERE name = 'MyDatabase'IF @@ROWCOUNT = 1BEGIN   COMMIT TRAN   RAISERROR('emergency mode set', 0, 1)ENDELSEBEGIN   ROLLBACK   RAISERROR('unable to set emergency mode', 16, 1)ENDGOEXEC sp_configure 'allow updates', 0RECONFIGURE WITH OVERRIDEGO-- Restart SQL Server at this point.DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')/*Perform physical and logical integrity checks at this point.Bcp data out if your integrity checks demonstrate that problems exist.*/ALTER DATABASE MyDatabase SET MULTI_USERGO-- Set database options and recovery model as desired.GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Detach the database, delete or move the ldf and then from ent manager attach database. The dialog will note that the ldf is missing. Click OK and db will be attached and new ldf will be created.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bledu SSChampion Points: 12335 More actions · Answer 3

I had the same problem at one time.I deleted the log file and did what Gilamonster said you should do and it worked fine.

Everything you can imagine is real.

Natalya Faden SSC Veteran Points: 287 More actions · Answer 4

In this instance, moving/renaming the ldf DID NOT work and I've read in several other forums that this method does not seem to be 100% reliable; not sure why.

In the end, using the undocumented instructions given above forced the recreation of the log file.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

What error did you get? Did SQL refuse to attach the db, attach but mark as suspect?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Natalya Faden SSC Veteran Points: 287 More actions · Answer 6

It did not attach it, no, nor mark anything as suspect. The error - off the top of my head - was something along the lines of "There is a problem with 'C:\SQL FIles\database.ldf' the file is missing or incorrect. Disconnected."

Or something like that. Essentially it knew that the ldf was missing ... but did nothing about it. I even started to research to see if there was a parameter that I didn't know about to force the .ldf creation using attachDB.

Incidentally, I tried the attach_Single_file_suspect_db methods as well, but they gave the same error.

tcraig SSC Veteran Points: 253 More actions · Answer 7

Try attaching the mdf to another instance of SQL Server. I've had this happen where the original instance expects the ldf file but a new instance will create a new empty one. Must be something in the master db?

hildevan Say Hey Kid Points: 691 More actions · Answer 8

Hi

Try using sp_attach_single_file_db.

Here is a example from Books Online.

EXEC sp_detach_db @dbname = 'pubs'EXEC sp_attach_single_file_db @dbname = 'pubs',    @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

Hildevan

Hildevan O Bezerra

Natalya Faden SSC Veteran Points: 287 More actions · Answer 9

Tried " sp_attach_single_file_db" (please read Post#1) and that gave the same error.