A corrupted or missing transaction log file is one of the most stressful situations a DBA can face. The first instinct is to restore from the last full backup — but depending on the circumstances, SQL Server offers faster recovery paths that minimize both downtime and data loss. This article walks through the two main scenarios and the procedure for each.

Warning: these procedures involve bypassing normal recovery mechanisms. Always validate application-level data consistency after completing the recovery, and take a full backup as soon as the database is accessible.

Possible Starting Conditions

• Corrupted log file (hardware failure, storage error)
• Corrupted log file during a long-running transaction
• Log file volume lost or unmounted while transactions were in progress

Scenario 1: No Transactions Were Running at Crash Time

If the database was idle when the log file was lost or corrupted, SQL Server can automatically rebuild the log file during startup. This is the cleanest recovery path.

1. Detach the database: sp_detach_db
2. Rename the corrupted log file to *.OLD (don’t delete it yet)
3. Attach using FOR ATTACH_REBUILD_LOG

-- SQL Server will rebuild the log file at the original path
CREATE DATABASE [MYDATABASE] ON
    (FILENAME = N'D:SQLDataDatabase.mdf')
FOR ATTACH_REBUILD_LOG
GO

If the rebuilt log file needs to be in a different location, SQL Server will attempt to create it in the original path specified in the MDF’s internal metadata. You may need to ensure that path is accessible.

Scenario 2: Transactions Were Running at Crash Time

ATTACH_REBUILD_LOG is not allowed when SQL Server detects open transactions or pending rollback operations. Attempting it will return:

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. […] Msg 1813, Level 16, State 2: Could not open new database. CREATE DATABASE is aborted.

In this case, use the following procedure to force a log rebuild:

1. Detach the database
2. Rename both the MDF and LDF to *.OLD
3. Create a new empty database with the same name, same datafile path, and same log file path
4. Set the new database offline: ALTER DATABASE MyDatabase SET OFFLINE
5. Copy the original MDF (*.OLD) back to its original path, overwriting the new empty one
6. Bring the database online: ALTER DATABASE MyDatabase SET ONLINE — this will fail, but that’s expected
7. Rebuild the log file:

ALTER DATABASE [MyDatabase]
REBUILD LOG ON (
    NAME     = 'MyDatabaseLog',
    FILENAME = 'D:SQLDataMyDatabase_log.ldf'
)

8. Open the database to all users:

ALTER DATABASE [MyDatabase] SET MULTI_USER

Post-Recovery Checklist

• Run DBCC CHECKDB immediately to verify physical and logical consistency.
• Take a full backup as soon as possible — the restore chain is broken and the previous backup chain is no longer valid.
• Validate application data with the application team. Transactions that were in-flight at crash time were not committed — check for partial operations that may have left business data in an inconsistent state.
• If application-level consistency cannot be restored, a full restore from the last valid backup is the only safe option.