How to Fix “BACKUP detected corruption in the database log” Error in SQL Server

Due to an unplanned reboot of SQL Server one of the transaction log file of a database become corrupt. DBA noticed this issue once regular transaction log backup job started failing with “BACKUP detected corruption in the database log” error message. Similar error message was also written to SQL Server Error Log file.

This article outlines the steps to be followed to fix “BACKUP detected corruption in the database log” error in SQL Server.

In the history of transaction log backup job below mentioned error was found.

Error Message

BACKUP LOG MYTECHMANTRA TO DISK = N'I:\BACKUPS\MYTECHMANTRA_Data..." failed with the following error: BACKUP detected corruption in the database log. Check the errorlog for more information. BACKUP LOG is terminating abnormally. 

However, at the same time the full database backup will complete successfully without any issues and DBCC CHECKDB didn’t discover any such errors.

Lets understand behind the scene what happens:

Transaction Log: A transaction log will backup all the transaction log which is generated since the last time the transaction log backup was successfully completed. This means it will also try to backup the corrupt portion of the TLOG even though it is corrupt and will fail in the end.

Full Database Backup: In case of Full database backup it will backup transactions which will allow the database to be restored to a consistent state after the completion of restore operation.

This article outlines two options to recovery from this situation as long as the data files are intact.

Approach One: Switching Recovery Model from FULL to SIMPLE and then Switch it back to FULL

One approach to solve this corruption issue will be to switch the database recovery model from Full or Bulk-Logged to SIMPLE recovery model. This will ignore the corrupted portion of the Transaction Log file. Finally, switch the recovery model of the database back to FULL or Bulk-Logged and initiate the Full Database Backup followed by Transaction Log Backup to resolve the issue.

Approach Two: When Database Mirroring is Enabled – Take Transaction Log Backup with CONTINUE AFTER ERROR option

When Database Mirroring is enabled for a database then you should take Transaction Log Backup with CONTINUE_AFTER_ERROR option as in this case the transaction log backup will continue after encountering a PAGE CHECKSUM error.

Using SSMS create Transaction Log Backup with CONTINUE_AFTER_ERROR option in SQL Server

You could also perform the transaction log backup with CONTINUE_AFTER_ERROR option in SQL Server Using SQL Server Management Studio.

In this case within the Back Up Database window choose CONTINUE ON ERROR option as highlighted in the below snippet and Click OK to perform the transaction log backup.

TSQL script to create Transactional Log Backup with CONTINUE_AFTER_ERROR option in SQL Server

BACKUP LOG [MyTechMantra] 
TO  DISK = N'C:\DBBackups\MyTechMantra_Continue_After_Error.trn' 
WITH CONTINUE_AFTER_ERROR, COMPRESSION, STATS = 10
GO

Once the transaction log backup is completed then you must manually perform the normal TLOG backup. On successful completion of the normal Transaction Log backup your scheduled transaction log backup job to continue performing transaction log backups at regular intervals.

Trending SQL Server Disaster Recovery Articles and Tips

• How to Repair Suspect Database in SQL Server
• SQL Server: How to Start SQL Server with Minimal Configuration
• How to Start SQL Server in Single User Mode?
• How to Identify the Location of Resource Database in SQL Server
• What are Virtual Log Files in SQL Server Transaction Log File?
• How to Backup and Restore Resource Database in SQL Server
• Steps to Connect to SQL Server When all System Administrators are Locked Out