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

WilliamD gravatar image

25.9k 17 19 41

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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 8728 times

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