Recover from corrupt SQL LDF transaction log file

Recover from corrupt SQL LDF transaction log file

Another favourite this month. A fault on one of our client's servers caused it to restart once every 10 minutes for 2 hours - the result was a corrupt LDF transaction log file for the main application database.

It is surprisingly simple to recover from this situation:

1. Stop the SQL Server service
2. Copy the affected database (both LDF and MDF files) out of the main data folder.
3. Restart the SQL Server service
4. Create a new database of the same name and location as the database affected in step 2 - it is critical the filenames and paths are identical.
5. Stop the SQL Server service.
6. Copy the original MDF file (copied in step 2) in to replace the new MDF file created in step 4
7. Start the SQL Server service - the database will show as being suspect.
8. Now you need to recover the database, working from Query Analyser or SQL Management Studio:

Use master
go

sp_configure 'allow updates', 1
reconfigure with override
go

select status from sysdatabases where name = '{{DBName}}'
go
-- Make a note of the status - in case you need to restore the value.

update sysdatabases set status = 32768 where name = '{{DBName}}'
go

9. Restart SQL Server - the database should now show up in emergency mode.
10. Delete the LDF file for the database.
11. Rebuild the transaction log:

DBCC REBUILD_LOG ('{{DBName}}', '{{Full path to the LDF file}}')
go
-- SQL Server will confirm: Warning: The log for database '' has been rebuilt.

12. If all appears to be OK:

USE {{DBName}}
GO
ALTER DATABASE {{DBName}} SET SINGLE_USER
go
DBCC CHECKDB ('{{DBName}}', REPAIR_ALLOW_DATA_LOSS)
go

13. If all still appears to be OK:

sp_dboption '{{DBName}}', 'single_user', 'false'
go

Use master
go

sp_configure 'allow updates', 0
go