How do I re-attach a database without an LDF file?

Hi everybody,

I have a rather difficult situation that I have to deal with and have hit the limits of my sql/google/bing abilities.

I was contacted by one of our offices requesting me to restore a database - fine thinks I - give me the file, and away we go.

I was presented with a single MDF file!

I have tried the solution that is deemed "normal" in this situation:


This provides the following error:

 File activation failure. The physical file name "XXXXXXXX" may be incorrect.
 The log cannot be rebuilt because there were open transactions/users when the
 database was shutdown, no checkpoint occurred to the database, or the database 
 was read-only. This error could occur if the transaction log file was manually 
 deleted or lost due to a hardware or environment failure.

After reading this message and some further digging I have found out that:

  • the database system was stopped mid-operation (hence the open transactions)

  • only the mdf file was copied off the server

  • the backups were locally stored on the server

  • the server was decommissioned and disks were destroyed

I have also tried creating a dummy DB, stealing the LDF file and starting up the database with the dummy LDF - no dice.

 One or more files do not match the primary file of the database. If you are 
 attempting to attach a database, retry the operation with the correct files.
 If this is an existing database, the file may be corrupted and should be 
 restored from a backup.

I am now at the point of seeing this as a lost cause - does anyone have anything else to say that could help me along at all?

Many thanks in advance for any help offered towards solving this big pile of FAIL.

more ▼

asked May 24, 2012 at 02:04 PM in Default

avatar image

26.2k 18 38 48

P.S. I should mention that the office is more than happy to have some data loss - If it is possible to access the data in the MDF in any way, they would be very happy.

P.P.S - I hate writing this post - it is a cringe-inducing situation, especially as I had warned them of the importance of maintaining restorable backups when the system was commissioned.

May 24, 2012 at 02:09 PM WilliamD

I would also try and make sure that I was restoring onto the same version/edition/SP if poss

May 24, 2012 at 02:16 PM Kev Riley ♦♦

Thanks Kev, I'll give that a try - not hopeful though and tend to agree with Grant - they are without data.

May 24, 2012 at 06:41 PM WilliamD
May 25, 2012 at 10:36 AM sp_lock
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If you have open transactions that were not completely committed to the .mdf file... I don't think you've got any hope of restoring this without having a backup available. I wish I could say something else, but there it is.

more ▼

answered May 24, 2012 at 02:07 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

sad times it is then.... I'm gonna give a couple more things a try, then they'll have to try data recovery of the HDD if they really want/need the data. Thanks for the support of my assumption anyway.

May 24, 2012 at 06:39 PM WilliamD

Sorry. That's not a pretty spot to be in.

May 25, 2012 at 10:28 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 24, 2012 at 02:04 PM

Seen: 11536 times

Last Updated: May 25, 2012 at 10:36 AM

Copyright 2018 Redgate Software. Privacy Policy