First published on MSDN on Jan 12, 2016
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.
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:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.