Fix SQL Server Backup Detected Corruption in the Database Log Instantly
Corruption issues are nothing new for SQL Server users. Once in a while, some type of issues keep popping up in the database. In this write-up, we will see what happens when SQL Server Backup detects corruption in database log. In order to discuss this, we would go through different error messages that get generated during this error.
SQL Server Backup Detected Corruption in the Database Log
User Scenario 1: “Even after multiple attempts, I failed to ship my log file. I only keep getting an error message saying: “SQL Server BACKUP detected corruption in the database log.” Even after checking my database with DBCC CHECKDB command, I did not find any error or fault in that database. Wondering why this is happening time and again! Can anyone help me in this regard?”
Explanation:
If you check the history of backup jobs, you will find out that the log file corruption is causing this error. It is also seen that even after performing the full backup without any complications, this error message shows up. Since transaction log backup contains everything including the last good backup, corruptions all get included in it. Due to this corruption, the log file backup or shipping fails. Therefore, DBCC CHECKDB fails to identify any fault from the last good backup, causing more confusion to the users.
Solution A :
The solution to this problem is to exclude the corruption while taking the backup of SQL Server Log files. For that, users can perform these steps sequentially:
Step 1: Make sure that all other users are disconnected from the SQL database.
Step 2: Move to SIMPLE recovery mode.
Step 3: Include a new file to a drive.
Step 4: Now, switch to FULL recovery mode.
Step 5: Perform FULL database backup of your SQL Server
Step 6: Initiate SQL Log backup. You should be able to perform it without any error message.
Solution B:
Also, users can try the option that will allow the backup process to continue even after encountering the error due to corruption. The option is called “CONTINUE_AFTER_ERROR” option. It can be implemented through T-SQL command or with the help of SQL Server Management Studio (SSMS). This command will support the process of Log file backup despite the presence of corruption.
User Scenario 2:“Our organization has multiple databases for SQL Server. Yesterday, we attempted to backup Log file of one database. During the backup process, it stopped and we got an error: SQL Server Backup detected corruption in database log. Context is bad middle sector.” The database was in FULL recovery mode during the backup process. Please tell me why this error has appeared as our database backup was successful.”
Explanation:
Once you get such an error while backing up, you have to check the error log of your Server. Investigating the log will give you an idea that the corruption is caused by replication in the SQL Server database. Users may be surprised by this error log as they do not consider replication as the cause of this error. If the metadata related to replication is not terminated from the database, it can cause this error to pop-up in SQL Server.
Solution
Since the copied data of database is responsible for this particular error, complete and permanent removal of replication is its only solution. To do that, SQL users have to use this command:
sp_removedbreplication
'OfficeData'
Now the backup can be done seamlessly as there will be no old replica transactions running.
Conclusion
Due to the nature of this error, most of the users do not understand why they are getting SQL Server Backup detected corruption in the database log” error. To make them understand, we have discussed two different scenarios where this error message can appear. By following the solutions cited in this post, users can resolve this SQL Server error with no trouble whatsoever.