SQL Server error BACKUP detected corruption in the database log

By:   |   Comments (4)   |   Related: > Backup


Network maintenance was performed in one of our data centers, causing SQL Server to reboot several times. Directly after the maintenance window, SQL Server was checked and no issues were found. The day after the maintenance, we found that the transaction log backups failed due to log file corruption errors in one of the databases. No user complaints were received, but the transaction log continued to grow since we could not perform a successful log backup.


The history of the transaction log backup job shows the below error, indicating that the log file is corrupted:

BACKUP LOG [MSSQLTipsDB] TO DISK = N'M:\SQLBackup_Dat..." failed with the following error: "BACKUP detected corruption in the database log. Check the errorlog for more information. BACKUP LOG is terminating abnormally

What is weird is that the full backup completed successfully without any error and when we ran a DBCC CHECKDB no errors were found. Investigating deeper, we found that the transaction log backup will back up all transactions generated since the last log backup, including the corrupted portions, which caused the backup to fail. On the other hand, the full backup only backs up to the beginning of the last active transaction at the time the backup is taken, which is enough for the full backup to be restored and recovered to a consistent point. Typically, the DBCC CHECKDB operation requires the same amount of log as the full backup, at the time the database snapshot is generated.

To solve this corruption problem, you could switch the database to the SIMPLE recovery model and ignore the corrupted portion of the log, then switch the recovery model back to FULL and perform your backups again. This will help in most cases, but in our case it would not work since we are using database mirroring between two datacenters and in order to change the recovery model to SIMPLE we would need to destroy the mirror and then reconfigure which would be a big effort to recreate due to bandwidth between the two datacenters and the size of the database.

SQL Server Database Backup with Continue After Error Option

Another solution is to take a transaction log backup with the CONTINUE_AFTER_ERROR option. Using CONTINUE_AFTER_ERROR, the backup operation continues after encountering a page checksum error.

The backup can be performed using a T-SQL command like the following:

 TO DISK = N'M:\SQLBackup_Data\MSSQLTipsDB\ MSSQLTipsDB.trn' 

This same option also can be selected using SQL Server Management Studio by checking the option "Continue on error" from the Media Options page on the Back Up Database window as shown below:

sql server backup continue on error

After we used this option to do a manual transaction log backup, the transaction log backup ran successfully and then our scheduled Transaction Log backups continued normally resolving the corruption issue we encountered.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, April 18, 2023 - 6:56:05 AM - TELNET Back To Top (91117)
it's 2023 and this post was a lifesaver

Friday, September 20, 2019 - 5:11:07 PM - ChrisN Back To Top (82526)

Worked great thanks! (fyi - Ola's does have this as an input param)

Thursday, June 22, 2017 - 10:32:07 AM - KevinP Back To Top (57932)

Our org has a mirror that I did not feel comfortable pausing but this worked for our group as well to clear the issue and allow the automated backups to proceed with the error cleared.  Thanks



Monday, August 29, 2016 - 6:35:58 PM - Jaydeep Back To Top (43212)

Solution provided to take log backup when corrupted worked well and here is the query I used to take log backup,


TO DISK='F:\MSSQL\DBName_Tran.bak'



Thanks a lot!!


get free sql tips
agree to terms