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 failing for last 2 months. Another sad story reminding us how important to have a good backup.)

Long story short, we are in a situation where we will need to recovery the database with no LDF file and it has also been detached from SQL Server 2012.

A friendly reminder - What we are about to do should be ONLY used as last resort.

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:

Now 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. Let's try another way by calling SP_ATTACH_SINGLE_FILE_DB stored procedure:

Same error message raised for the same reason.

Since these two attempts fail to attach the database. 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 leave 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, following error message showed:

This is where some people might think we need to go into emergency mode but we don't as DB1 is already in suspect mode. Instead, we would just run ALTER DATABASE REBUILD LOG command:

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:

By looking at the database status, 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 two tests are slightly different and both are effective. There are a couple more twists can also achieve the same result. 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 Tests:

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

When we rebuilt the database, I used same database name. and assumed the database name is magical. What if the database name is irrelevant in this case? Can we prove it? Let's answer this question with experiments :)

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 having a different database name could be problematic. As a good practice, it's always recommended to be cautious and use the same database when it's applicable.