Why SQL Server Log backups fails and Full backup succeeds? Learn 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 transactional log file of a database become corrupt. DBA noticed this issue once regular transactional 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 transactional log backup job will show you the below mentioned error.
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 even DBCC CHECKDB will not discover any such errors.
Lets understand behind the scene what happens:
Transactional Log: A transactional log will backup all the transactional log which is generated since the last time the transactional 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.
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 Transactional Log file. Finally, switch the recovery model of the database back to FULL or Bulk-Logged and initiate the Full backup followed by Transactional log backup to resolve the issue.
Note: If you have configured database mirroring for the database for which you have received "BACKUP detected corruption in the database log" error message. Then, the above option wouldn't work as it will break Database Mirroring once you switch the database recovery model from FULL to SIMPLE. Hence follow the next approach.
Approach Two: When Database Mirroring is Enabled - Take Transactional Log Backup with CONTINUE AFTER ERROR option
When database mirroring is enabled for a database then you should take transactional log backup with CONTINUE_AFTER_ERROR option as in this case the transactional log backup will continue after encountering a PAGE CHECKSUM error.
Using SSMS create Transactional Log Backup with CONTINUE_AFTER_ERROR option in SQL Server
You could also perform the transactional log backup with CONTINUE_AFTER_ERROR option in SQL Server Using SQL Server Management Studio.
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 transactional log backup is completed then you must manually perform the normal TLOG backup. On successful completion of the normal TLOG backup your scheduled transactional log backup job to continue performing transactional log backups at regular intervals.
- Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Built your Career
- Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server
- SQL Server Database Backup Tutorial for DBAs and Developers
- How to Enable BACKUP CHECKSUM in SQL Server
- Different States of SQL Server Database
- Repair Suspect Database in SQL Server
- When Last Time DBCC CHECKDB ran on SQL Server Databases
- How to Limit SQL Server Error Log File Growing too Big
Last Updated On: Feb 12, 2015
Please leave your Valuable Comment or Let us know how this article helped you: