SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

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.

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 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.

CONTINUE_AFTER_ERROR Option to Backup Database in SQL Server

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

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips

Manning