User Query 1: “Hi all, I have only a little knowledge about the vast topic like SQL Server. I was in an urgent need for database backup, so I began the process a few days ago. This is when I faced a problem. The database backup could not be completed and instead, I saw a message that says, “SQL BACKUP detected corruption in the database log.” It also asked to check the error log but I could not find anything there. Moreover, I run DBCC CHECKDB command, but found no corruption. Can anyone tell me how to get rid of error SQL Server BACKUP detected corruption in the database log? I will be ever grateful.”
User Query 2: “When I attempted to back up my entire SQL database (SQL 2008 R2) I had no problem during full database backup. But when transaction log backup started, after 30% progress a message showed up: SQL BACKUP detected corruption in the database log. I have tried changing the database from Full recovery to Simple recovery mode but it did not work. Please suggest me any method that can fix/ remove this error.”
Among many relational database management systems, Microsoft SQL Server is one of the most popular ones. Companies, as well as enterprises. Are the main users of SQL Server. We often hear about the SQL error messages these users keep facing. In this post, we will discuss one such SQL Server error, “BACKUP detected corruption in the database log.” This error can stop the backup process and create a lot of other troubles. Let us learn the reasons behind this error and its remedies.
What Causes “SQL Backup Detected Corruption in the Database Log” Error
The main reason behind this error is corruption in log file. The default setting for transaction log backup allows to backup the all the transaction log entries that have been made since the last backup of transaction log. This rule goes even for the corrupt parts of transaction log. So, when the backup happens, the damaged parts of log files are not spared. It is the time when users get SQL backup detected corruption in the database log error. To prevent the occurrence of this error, users can check up the integrity of SQL database and its transaction logs on a regular basis.
How to Fix Error SQL Server Backup Detected Corruption in the Database Log
There are various methods to get rid of this error. Here we will discuss two methods:
1. Move to Simple Recovery Model
To fix this issue, users can try switching from FULL to SIMPLE recovery model. Thus, you can easily skip the corrupt sector of transaction log and move back to the FULL recovery model for conducting backup. This method is a useful one unless you are using database mirroring method.
2. Backup using Continue_ After_Error Option
If users face “Backup Detected Corruption in the Database Log” error, they can also utilize CONTINUE_AFTER_ERROR option. With its help, they can continue the backup process even after bumping into this page checksum error. This option can be enabled using T SQL or SQL Server Management Studio.
- Use These T-SQL Commands to Continue Backup Even After the Error:
- Avoid Backup Error by Using SQL Server Management Studio (SSMS)
To use CONTINUE_AFTER_ERROR option in SSMS, open Back Up Database window and go to Media Options page. Check “Continue on error” option there. Once this has been utilized, the transaction log backup will run without interruption by resolving all the corruptions in SQL Log.
Note- If your SQL Server .bak file found to be corrupted then for quick solution you can use SysTools SQL bak file repair to fix your corrupted SQL backup file.
Conclusion
Here, we have compiled the basic information about error SQL Server BACKUP detected corruption in the database log.” If your SQL Server has encountered this problem, you can follow the techniques mentioned here to fix the situation. If your Server has not faced the error yet, do not forget to apply the preventive measures to remain safe from this error.
IgnacioSalom.com 