MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter























Trending SQL Server Tips







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 

Read Comments


Introduction

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.

Error Message

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.

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.

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

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




Learn More...





Last Updated On: Feb 12, 2015



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: