Attaching SQL Databases Without Log Files

In a perfect world, you’d never lose a database log file. But when the unexpected happens and you urgently need the data from a database, knowing how to attach a SQL Server database without its log (.ldf) file can be a lifesaver.

This guide walks you through the process, covering three main parts:
1. Creating the Scenario:
Setting up a test database to simulate the issue.
2. Restoring the database without the log file:
Methods to reattach a database without its .ldf file.
3. Handling errors during the attach process:
Common errors and their solutions.

1. Creating the Scenario

To simulate attaching a database without its log file, follow these steps:

1. Create a test database:
Set up a test database in SQL Server.

2. Disconnect active sessions:
Close SQL Server Management Studio (SSMS) or disconnect any active query sessions to the database.

3. Stop the SQL Server service:
Temporarily shut down the SQL Server service to access database files directly.

4. Locate the database files:
Navigate to the directory containing the database’s .mdf (data) and .ldf (log) files.

5. Copy the .mdf file:
Save a copy of the .mdf file (data file) to a safe location.

6. Start the SQL Server service:
Restart the service to restore normal operations.

7. Drop the test database:
Remove the database from SQL Server, simulating the “missing log file” scenario.

8. Delete the original files:
Confirm the .mdf and .ldf files are no longer present in their original directory.

9. Restore the .mdf file:
Copy the .mdf file back into the original directory, preparing it for reattachment.

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

2. Restoring the database without the log file

SQL Server provides two ways to attach a database without its log file:

Option 1: Using FOR ATTACH (Recommended)

This approach allows you to attach a database using only the .mdf file. SQL Server automatically attempts to rebuild the missing log file.

This uses the FOR ATTACH option when creating the database:

-- attached database without log file
CREATE DATABASE [YourDatabaseName]
ON (FILENAME = 'C:\Path\To\YourFile.mdf')
FOR ATTACH;

Option 2: Using sp_attach_single_file_db (Not Recommended)

The older sp_attach_single_file_db system stored procedure can also be used but is deprecated and not advised for production systems. It is better to use the modern FOR ATTACH method.

3. Handling errors during the attach process

While attaching a database without its .ldf file, you might encounter errors like this:

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.

Common Causes and Fixes

> Open transactions/users during shutdown:
Ensure there are no active connections to the database before stopping the SQL Server service. Close all SSMS query windows or applications using the database.

> Missing checkpoints:
If the database was not properly shut down, SQL Server may struggle to rebuild the log file. In such cases, consider restoring from a recent backup if available.