Summary: In this article, we will discuss about the ‘SQL Backup Detected Corruption in the Database Log’ error. It will also describe the reason behind the error and manual workarounds to resolve it. The article also explains an alternative solution that can be used to restore the database and its transaction log backup – when the manual solutions fail.
When performing transaction log backup for a SQL database, to restore the database after network maintenance or in the event of a crash, you may find the backup job failed with the following error:
Backup failed for Server xxx (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: BACKUP detected corruption in the database log. Check the errorlog for more information. (Microsoft.SqlServer.Smo)
The error message clearly indicates that the transaction log is damaged (corrupted). Checking the SQL errorlog for more details on the error shows:
2020-11-01 13:30:40.570 spid62 Backup detected log corruption in database TestDB. Context is Bad Middle Sector. LogFile: 2 ‘D:DataTestDB_log.ldf’ VLF SeqNo: x280d VLFBase: x10df10000 LogBlockOffset: x10efa1000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x280d LogBlock.StartLsn.
2020-11-01 13:30:40.650 Backup Error: 3041, Severity: 16, State: 1.
2020-11-01 13:30:40.650 Backup BACKUP failed to complete the command BACKUP DATABASE TestDB. Check the backup application log for detailed messages
However, the FULL database backup completed successfully and even running DBCC CHECKDB integrity check didn’t find any errors.
What Could Have Caused the SQL Transaction Log Backup to Fail?
A transaction log (T-log) backup allows restoring a database to a certain point-in-time, before the failure occurred. It does so by taking a backup of all the transaction logs created since the last log backup, including the corrupt portion of the T-log. This causes the backup to fail.
However, a FULL database backup only has to back up the beginning of the last active part of the T-log – at the time the backup is taken. Also, DBCC CHECKDB requires the same amount of log as the FULL database backup – at the time of the db snapshot was generated. This is why the full backup executed successfully and no errors were reported by DBCC CHECKDB.
Manual Workarounds to Backup Detected Log Corruption in SQL Database
Following are the manual workarounds you can apply to resolve the SQL backup log corruption issue:
Workaround 1: Change the SQL Recovery Model from FULL to SIMPLE
To fix the ‘SQL Server backup detected corruption in the database log’ issue, try switching the database to the SIMPLE recovery model.
Switching to SIMPLE recovery model will ignore the corrupted portion of the T-log. Subsequently, change the recovery model back to FULL and execute the backups again.
Here’s the steps you need to perform to change the recovery model:
Step 1: Make sure there are no active users by stopping all user activity in the db.
Step 2: Change the db from FULL to a SIMPLE recovery model. To do so, follow these steps:
- Open SQL Server Management Studio (SSMS) and connect to an instance of the SQL Server database engine.
- From Object Explorer, expand the server tree by clicking the server name.
- Next, depending on the db you are using, select a ‘user database’ or choose a ‘system database’ by expanding System Databases.
- Right-click the selected db, and then select Properties.
- In the Database Properties dialog box, click Options under ‘Select a page’.
- Choose the Simple recovery model from the ‘Recovery model’ list box, and then click OK
Step 3: Now set the db back to the FULL recovery model by following the same steps from 1 till 5 above. Then, select Full as your recovery model from the list box.
Step 4: Perform a FULL database backup again.
Step 5: Take log backups again.
Hopefully, performing these steps will help you perform the transaction log backup without any issue.
Note: This solution won’t be feasible if you’re using database mirroring for the database for which you have encountered the ‘backup detected log corruption’ error. That’s because, in order to switch to the SIMPLE recovery model you will need to break the mirror and then reconfigure the db which can take significant amount of time and effort. In this case, try the next workaround.
Workaround 2: Create Transaction Log Backup using Continue on Error Option
To complete executing the backup of T-log without any error, try running log backup of SQL database with the CONTINUE AFTER ERROR option. You can either choose to run the option directly from SSMS or by executing a T-SQL script.
Steps to run the ‘Continue on Error’ option from SSMS are as follows:
Step 1: Run SSMS as an administrator.
Step 2: From ‘Back Up Database’ window, click Options under ‘Select a page’ on the left panel. Then, select the ‘Continue on error’ checkbox under the Reliabilitysection.
Step 3: Click OK.
Now, run the log backup to check if starts without the backup detecting an error in SQL database.
Ending Note
The above-discussed manual solutions won’t work if the transaction log is missing or damaged, putting the database in suspect mode. In that case, you can try restoring the database from backups or run Emergency-mode repair to recover the db from suspect mode.
However, none of the above solutions might work in case of severe database corruption in SQL Server. Also, implementing the ‘Emergency-mode repair’ method involves data loss risk. But, using a specialized SQL database repair software such as Stellar Repair for MS SQL can help you repair a severely corrupted database and restore it back to its original state in just a few steps. The software helps in repairing both SQL database MDF and NDF files. Once the MDF file is repaired, you can create a transaction log file of the database and back it up without any encountering any error.
