If you’ve lost a database log file and you’re in critical need of the data, well… that would never happen ( ͡° ͜ʖ ͡°)

Nevertheless, this is might be a handy random fact to know, that you can attach a data (.mdf) file without the associated log (.ldf) file in SQL Server.

This post will come in 3 parts.
1. Create scenario (get a test database, stop SQL, copy database, drop database). 
2. Restoring without a log file.
3. Errors when attaching (if you get one).

Create Scenario

1. Create a test database.

2. Close down SSMS, or disconnect the query session.

3. Stop the SQL Server Service.

4. Locate the Data and Log files.

5. Copy funkdata into a different location.

6. Start the SQL Service.

7. Drop the database.

8. Yep, the database files are gone.

9. Copy the data file (funkydata) back into this directory.

Now, we’re ready to try attach this back into SQL.

Attach Database without the Log File

There’s 2 ways of going about it.

1. Using the FOR ATTACH

2. Using sp_attach_single_file_db (not recommended).

Click here for the code used above.

Errors when Attaching

While testing the above, I first receive an error upon attaching the data file;

File activation failure. The physical file name “c:\sql\funklog.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 3
Could not open new database ‘funky_db’. CREATE DATABASE is aborted.

This was due to having a SSMS query window open in the background when stopping the services (Step 2 during prep).

It’s possible to get it attached I’m sure, but is it really worth it?…

Nah.