Recover database without LDF file

  • Article

I recently came across a missing LDF file case that I would like to share some interesting findings.

In my case, the customer didn't have any database backup (they did have maintenance plan created, however, the job had been being failing since the day it was implemented. Another sad story telling us how important to have a good backup)

So this situation is to recovery a database with no LDF file which had been detached from SQL Server 2012.

We will start reproduce this case by creating this testing database DB1:

Without committing the transaction, we shutdown SQL Server and detach DB1, which will leave an open transaction with it:

Once DB1 is detached, restart SQL Server:

Once the stage is set, let's start with creating DB1 with attach option:

As you can see in the screenshot, creating database failed due to open transactions. I did another test using SP_ATTACH_SINGLE_FILE_DB stored procedure:

Same error message raised for the same reason.

Since these 2 didn't work. The only option left is to fool SQL Server. Before trying anything, let's rename our DB1.mdf to DB1_orig.mdf.

We will start with creating a new database with same name DB1 and take it offline so we can do our tricks:

This will create 2 new files – MDF & LDF in the data folder:

At this point, there are a couple options can be explored. I will start with replacing db1.mdf and leaving DB1_log.ldf as it is.

Test 1 – Replacing DB1.mdf with DB1_orig.mdf and leaving DB1_log as it is

Steps are performed in the following order:

  1. Delete DB1.mdf
  2. Copy / Paste DB1_orig.mdf and rename it to DB1.mdf (keep this DB1_orig around as we will need it for other tests)
  3. Bring DB1 back online using

The error message may suggest this is not working but just hold on for 1 second before we claim this is a dead end – the database DB1 is showing as Recovery Pending:

And if I try to access to the database, I would get an error message:

This is where a lot people might think we need to go into emergency mode but we don't as DB1 is already in suspect mode. We can use ALTER DATABASE

This raises an error because db1_log.ldf already exists. At this point, we would either delete the existing db1_log.ldf file or I could rebuild the log using a new name

A lengthy message appears but the database is back in Restricted User mode:

We can set DB1 back to multi user to bring it back to normal

Now we are back to business!

Now, let's take a step back and explore other options. In case 1, we replaced MDF file but left LDF file as it is. Let's see what happens if we also delete LDF file:

Test 2:

Here are the steps:

  1. Delete the fixed db1 (if you don't have db1_orig any more, repeat the creation steps before continue)
  2. Create DB1 and bring it offline
  3. Delete db1.mdf and db1_log.ldf
  4. Copy / Paste DB1_orig.mdf and rename it to DB1.mdf
  5. Bring DB1 back online

After step 5, a different error appeared:

And as you might have suspected, DB1 is in "Recovery Pending" mode and we could just follow previous steps to bring it back:

These 2 tests are slightly different and both are effective. There are a couple more twists can also achieve the same goal. For example, after bringing DB1 offline, instead of replacing DB1.mdf file, we could use ALTER DATABASE DB1 MODIFY FILE to point the MDF file and/or LDF file to db1_orig.mdf. For LDF file, we could point it to a non-existing file. These will bring DB1 to the same Recovery Pending mode after trying to bring it online. Or, instead of using ALTER DATABASE DB1 REBUILD LOG, DBCC CHECKDB would also be able to repair LDF file.

More Test:

Now, I would like to take this little experiment a little further.

If we search online about this topic - recovering database, a lot folks will tell us to create a database with exact same name. This time, we are going to question this assumption – is it necessary to have same database name to recovery from a MDF file? Let experiment start!

If you don't have the previous DB1_orig.mdf file, you can look at previous blog for more details.

We are going to start with creating a new database with a different name: DB12

Using the same technique, we create the database and take it offline:

Then, we go to the data folder and delete both DB12.mdf and DB12_log.ldf. And we take a copy of DB1_orig.mdf and rename it to DB12.mdf.

After these changes, we will try to bring DB12 online:

By looking at the error message, it's not much different from what we saw in previous blog. Now what if we try to repair it, will it work?

Same message again, log file has been rebuilt. Then our simple test shows the recovery is successfully:

This test shows that same database name might not be required.

Now, let's run a DBCC CHECKDB and see everything is OK:

And DBCC says everything is OK J

Keep in mind that I have not done a thorough test for permissions and other database objects and cannot be certain if different database name might be problematic. As a good practice, it's always recommended to be cautious and use the same database when it's applicable.

Comments

  • Anonymous
    November 19, 2016
    i am using that tool http://www.sqlserverlogexplorer.com/how-to-attach-mdf-file-without-ldf-file/
  • Anonymous
    January 10, 2017
    Hi LouisThis is an awesome solution , thanks I tried the above step by step , in my case I can see both the records in the database, does the open transaction not commit while the SQL server is shutting down.
  • Anonymous
    October 05, 2017
    HiI am not able to see data after corrupted files recovered.Can you Please suggest for the same?i followed first scenario which you explained step by step able to do perfect all steps with out issues but finally couldn't see data.
  • Anonymous
    April 13, 2018
    Excellent article. In my case, a Project Server database was accidentally deleted (with no backups). But we had a VMware database server snapshot that was restored to a time prior to the database deletion. That is how I recovered a viable copy of the database. However, the transaction log was simply not useful and the database would not mount because the database was regarded as "read only" by SQL Server. Going through this process enabled me to get the desired .MDF file mounted to SQL Server. But once I got the database into 'Recovery Pending', I had to switch the database to 'Emergency' mode run DBCC CHECKDB (DatabaseName,REPAIR_ALLOW_DATA_LOSS) to finish the recovery operation. At the end of the process, the database tables were accessible and the only data loss was that which was missing as of the last committed transaction log. Another DBCC CHECKDB with ALL_EERORMSGS showed no corruption or structural issues!