question

WilliamD avatar image
WilliamD asked

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: CREATE DATABASE ...... FOR ATTACH_REBUILD_LOG 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.
sql-server-2005restoredisaster-recovery
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
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.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Have you read this: ?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
I would also try and make sure that I was restoring onto the same version/edition/SP if poss
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
Thanks Kev, I'll give that a try - not hopeful though and tend to agree with Grant - they are without data.
0 Likes 0 ·
sp_lock avatar image sp_lock commented ·
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
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.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Sorry. That's not a pretty spot to be in.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.