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.

When you back up a SQL Server database, multiple backups can be stored in a single file. The WITH FILE = X option allows you to choose a specific backup version from a single backup file.

Here’s what happens:
– WITH FILE = 1: Restores the first backup stored in the file.
– WITH FILE = 2: Restores the second backup, and so on.

This can be useful when you run consecutive backups without changing the file path and want to restore a specific version later.

In the demo below, I’ll walk you through how the WITH FILE = X option functions when backing up or restoring a SQL Server database. This technique is particularly useful when dealing with multiple backups stored in a single media file. I’ll also inspect the backup file using RESTORE HEADERONLY to show how that works too.

Demo: Using WITH FILE to Restore Specific Backups

1. Create a Test Database and Insert Data

2. Perform the First Backup

-- backup database to file
BACKUP DATABASE TestDB TO DISK = 'C:\sql_Backups\backup_test.bak' WITH COMPRESSION, STATS;

3. Insert More Data and Backup Again

INSERT INTO TestTable VALUES ('2', '2', '2');
BACKUP DATABASE backupOverwrite TO DISK = 'C:\sql_Backups\backup_test.bak' WITH COMPRESSION, STATS;

4. Inspect the Backup File
To see the backup versions inside the file, use RESTORE HEADERONLY:

Look for the Position column. This shows 1 for the first backup and 2 for the second:

5. Restore the First Backup (WITH FILE = 1)

I’m doing this restore using an additional option, REPLACE, which means I don’t need to drop this test database before restoring.

-- restore test database, first file in backup
RESTORE DATABASE backupOverwrite FROM DISK = 'C:\sql_backups\backup_test.bak' WITH FILE = 1, REPLACE, STATS;

Verify the data:

6. Restore the Second Backup (WITH FILE = 2)

Again, doing with replace to overwrite the last restored db.

Verify the data again:

Conclusion

Using WITH FILE = X helps you target specific backups from a single file, providing greater flexibility during database restoration. The RESTORE HEADERONLY command is invaluable for identifying backup positions. Remember to use WITH REPLACE when restoring over an existing database without dropping it first.