An SQL administrator has to take database backup to provide an essential safeguard for protecting critical data stored in your SQL Server databases. Here are some examples – emergency data storage, server relocation, network maintenance, etc.
Here in this blog, we will focus on the error ‘SQL Backup Detected Corruption in the Database Log.’ When we take a backup of SQL database, it will not shows any error, but when we backing up the transaction log, it stopped the ongoing process and an error message appears like –
BACKUP LOG <database name> TO DISK = N’I:\BACKUPS\ _Data…” failed with the following error: BACKUP detected corruption in the database log. Check the error log for more information. BACKUP LOG is terminating abnormally.
This error shows that backup detects a corruption in the database log. Now, in this condition, even if you perform a DBCC CHECKDB integrity check, no problems will be found.
Actual reason for this error:
When we take a full backup of database, the backup will be from the start of the last active transaction from the moment the backup is created. The default setting for transaction log backup allows you to create a backup copy of all transaction log entries made since the last transaction log backup. So, when your backup is done, the corrupted parts of the log files might be include in it as well. So, we can assume that the error is due to the corrupted log.
The main reason for this error is corruption in the log file, which is not detected while taking the full backup or integrity check of SQL Database. To avoid this error, you can regularly check the integrity of SQL database and its transaction logs, i.e. DBCC CHECKDB. Here, we want to share with you some free manual fixes that any SQL administrator can try on their own.
Fix No. 1 – Switch to Simple Recovery Model
As we know, the backup copy of the transaction log is suspended or broken by an error indicating a damaged transaction log. We can try to update it from the moment the problem occurred by leaving part of the backup process and then continuing to create a backup copy. Let us understand the process to be performed.
- First of all disconnect all users connected to the SQL database
- Now, you are required to set SQL database to Simple Recovery Mode
- Run a checkpoint to move point-in-time on the database log file for detecting and ignore the corrupted log file
- Once that sector of log file is ignored, users can switch to the Full Recovery Model
- Now, execute full database backup process
- Next, start backing up your SQL log files and wait for it to finish
If everything is running properly, it means that the damaged SQL log has been successfully ignored while performing the backup. This method actually requires technical knowledge of SQL database backup processes, its model, etc.
However, switching of the recovery model is not valid if you are using database mirroring for SQL data backup and get this error “Backup detected corruption in the database log”.
For such a situation, when the database Mirroring is enabled and the error “Backup detected in the database log” occurs, we have another solution for you.
Fix No. 2 – Use the CONTINUE AFTER ERROR option for Transaction Log Backup
Users can also try other manual tricks to back up their transaction log files without any mistakes. Users can easily run a SQL log backup with CONTINUE AFTER ERROR option either by running a T-SQL script or directly using Microsoft SQL Server Management Studio.
- Run Microsoft SQL Server Management Studio as an administrator.
- In the Backup Database window, Visit the Media options or Options on the left panel.
- Under Reliability section, click on checkbox saying Continue on error and click OK.
- Then run the backup process. This will start creating a backup without throwing the error again.
If you failed to get the desired results from the two manuals tricks we talked above, or find it difficult to follow, then we recommend you try the automated solution, named as SQL Backup Recovery tool, which performs a full recovery of SQL data even when they are damaged. First it effectively scans the corrupted or damaged files, repairs/recovers data and then saves the recovered data to Live SQL Server with full accuracy without any errors. This professional tool efficiently repairs/recovers corrupt .bkf file and supports all versions of MS SQL Server. Try the free edition of this SQL Backup Recovery tool that helps you in evaluating the performance & capability of the utility before investing on it.
The blog covers how to fix SQL Backup Detected Corruption in the Database Log’ Error. You will learn some manual fixes discussed in this blog. We have also mentioned an advance SQL database backup recovery to fix these issues.