Recovering A Database With Only The MDF File: These Are Your Options

Hello out there and welcome back to SQL Something!

This is one of a few blog posts I had time to complete and post (busy times). Without further ado, let's get started.

This post was inspired by a recent event where we lost the drive that our log files were on (not gonna go into the gory details about that).
Thankfully, prior to this we knew hardware weirdness was going on with the server so we moved all our production DBs to our secondary server. Whew.
Thus, losing the drive on what was formerly the production server wasn't that bad. And it then gave me some MDF files to attempt to restore at my leisure, just for trying sake.

Situation:

No log files available, only MDF file.

Solutions:

There are a number of solutions I have read and tried concerning this, and I'll try to rank them in order of what you should try first, all the way to what is your absolute last resort.

Solutions 1 and 2 will only work if the database was cleanly shut down prior to losing the log file. An example of this would be if the database was detached or taken offline before the log file was lost.

Solution 1: Using sp_attach_single_file_db

Detailed info on sp_attach_single_file_db can be found here.

Use the following command:
USE [master]
GO
EXEC sp_attach_single_file_db @dbname='YourDB', @physname=N'YourPath\YourDB.mdf';
GO

When this code is run, Management Studio will indicate that a new log file was created.

Solution 2: CREATE DATABASE... FOR ATTACH_REBUILD_LOG

From MSDN: "If a read/write database has a single log file that is currently unavailable, and if the database was shut down with no users or open transactions before the attach operation, FOR ATTACH automatically rebuilds the log file and updates the primary file."

USE [master]
GO
CREATE DATABASE YourDB ON
(FILENAME = N'YourPath\YourDB.mdf')
FOR ATTACH_REBUILD_LOG;
GO

Same as before, Management Studio should indicate that a new log file was created.


Alternate Solution:
You can achieve the same results as Solutions 1 and 2 above via Management Studio.
Right click the Databases folder in Management Studio and click "Attach".
Select your lone MDF file.
Select the LDF file as in Fig. 1 and select "Remove".
Click "Ok".
SQL Server will attempt to recreate a log file.

Fig. 1: Removing the default log file.

If the above two do not work, chances are you're getting an error like this:

File activation failure. The physical file name "YourPath\YourDB.ldf" 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.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'YourDB'. CREATE DATABASE is aborted.

Sigh.

A couple notes here:

1. Pay attention to the line "The physical file name "YourPath\YourDB.ldf" may be incorrect". Depending on if you're trying to restore the MDF on a different machine, this path may not exist. I'm honestly not sure as yet how one will go about changing this (I will be investigating further).
2. "...there were open transactions/users when the database was shutdown...". This could mean that even if you manage to restore the DB, chances are you may lose data. Any transactions not commited at time of failure will be lost. :-(

Alright, let's hit that last resort.

Solution 3: SQL Trickery 

This last solution is messy. Follow these steps but choose one of two where indicated.

• Create a new database with the same name as the one you are attempting to restore (a new MDF and LDF file will be created).
• Choose ONE of the following:
• Take the DB offline (either right click the DB, go to Tasks then Take Offline OR run ALTER DATABASE YourDB SET OFFLINE).
• Stop the SQL Server Service (do this through the Configuration Manager).
• Navigate to the folder of the newly created MDF file and replace that new MDF with the problem MDF (Trickery!).
• Choose ONE of the following:
• If you took the DB offline, attempt to bring it back online (either right click the DB, go to Tasks then Bring Online OR run ALTER DATABASE YourDB SET ONLINE).
• If you stopped the SQL Server Service, restart it (do this through the Configuration Manager).
• The DB should be in "Suspect" mode (you'll know because the ability to expand it to view tables etc will be disabled).
• Run: "ALTER DATABASE YourDB SET EMERGENCY;" (allows you to operate on it).
• Run: "ALTER DATABASE YourDB SET SINGLE_USER;" (allows you to run DBCC repairs on it; you'll get an error stating "Repair statement not processed. Database needs to be in single user mode." otherwise).
• Run: "DBCC CHECKDB (YourDB, REPAIR_ALLOW_DATA_LOSS);" (You WILL lose uncomitted transactions; your DB may also become structurally unsound).
• Run: "ALTER DATABASE YourDB SET MULTI_USER;".
• Run: "ALTER DATABASE Test SET ONLINE;".

Your DB should be back up. You can also run a DBCC check at the end to ensure no errors. You can also run some manual queries on your data.

If at the end of all that you get something like this... then I think that's it for trying unfortunately:
Msg 5173, Level 16, State 1, Line 1
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.
Log file 'YourPath\YourDB.ldf' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 21(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'YourPath\YourDB.ldf'.
Msg 5024, Level 16, State 2, Line 1
No entry found for the primary log file in sysfiles1.  Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 1
The system could not activate enough of the database to rebuild the log.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.

If I do find a solution to the above, I will edit this post. I really do hope this will be of use otherwise. This is a terrible scenario to find oneself in. ALWAYS make your backups.

 DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)