Read time 4 minutes
An SQL administrator has to take database backup owing to different reasons. Some examples are – need of urgent database backup, relocation of server, network maintenance etc.
Here, our focus in this blog is the error ‘SQL Backup Detected Corruption in the Database Log.’ While taking the backup of SQL database, there is no error, but while taking a transaction log backup, the process is halted in between and an error message is popped up like this.
BACKUP LOG <database name> TO DISK = N’I:\BACKUPS\
The error indicates backup detects a corruption in the database log. Now, in this condition, even if you run the integrity check DBCC CHECKDB, no issues will be found.
Actual reason for this error
Whenever a full database backup is taken, the backup will be from the start of the last active transaction at the time of the backup, but in the case of the transaction log backup (in its default setting), it backups all the transactions logs from the last log backup which might include the corrupted log as well (if any). So, we can assume that the error is due to the corrupted log.
Manual fixes
This error occurs as a result of corrupted logs in the SQL database, which is not detected during the full backup of the SQL database or the integrity check, i.e. DBCC CHECKDB. We would like to share some free manual fixes which any SQL administrator can try on their own.
Fix No. 1 – Switch to simple recovery model
As we know, transaction log backup has been halted or broken with an error indicating a corrupted transaction log. We can try resuming it from that point where the issue has occurred to leave that portion from the backup process and then continue with the backup. Let us understand the process to be performed.
a. Disconnect all other connected users to the SQL database.
b. Set the SQL database to the Simple Recovery Mode.
c. Use manual checkpoint to move point-in-time on the database log file for detecting and ignoring the corrupted log file.
d. Once that sector of the log file is ignored, users can switch the database backup from Simple Recovery Model to the Full Recovery Model.
e. Perform the Full database backup process.
f. Next, start the SQL log files backup as well and wait for its completion.
If everything goes right, it means the corrupted SQL log has been successfully ignored while performing the backup. This method definitely requires technical knowledge about the SQL database backup processes, its models, etc.
However, this switching of the recovery model is not applicable if you are running database mirroring for the SQL data backup and received this error “Backup detected corruption in the database log”.
For such a situation where Database Mirroring is going and the error “Backup detected corruption in the database log” is received, we have another fix available for you.
Fix No. 2 – Use the continue after error option for transaction log backup
Users can also go for another manual trick to complete their transaction log backup without any error. They can run the SQL log backup with the CONTINUE AFTER ERROR option either by running a T-SQL script or using the option directly from the Microsoft SQL Server Management Studio.
Run Microsoft SQL Server Management Studio as an administrator. On your Backup Database window, go to the Media options or Options on the left panel. Under Reliability section, select the checkbox saying Continue on error and click on OK. Then run the backup. It would start the backup without throwing the error again.
If you do not get the desired results from the above two manual fixes or find them complex to perform, then we suggest you to try the automated solution, Kernel SQL Backup Recovery tool, which performs complete recovery of the SQL data even when it is corrupted. First it repairs the corrupt files efficiently, retrieves the data and then saves the recovered data to either Live SQL Server or the Batch File. The advanced tool performs effective backup, recovery and supports all SQL Server versions.
Final words
Unfortunate incidents like log errors can irritate SQL users anytime. For that some manual fixes are discussed in this blog. Other than that a permanent and reliable solution is also mentioned for advanced SQL database backup recovery.
I was getting SQL Backup log error while backing up. I tried this Kernel SQL Backup Recovery and it worked well. Being a DB administrator I would like to recommended for those who are tired of manual solutions.