Read time 4 minutes
An SQL administrator must perform database backups for various reasons, including the requirement for immediate backups, server relocation, and network maintenance, among others.
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.
When performing a complete database backup, it captures data starting from the last active transaction recorded at the backup’s initiation. However, with transaction log backups using their default settings, they include all transaction logs since the last log backup, potentially encompassing any corrupted logs. Thus, it is reasonable to attribute any errors to the presence of a corrupted log.
This issue arises due to corrupted logs within the SQL database, and it often goes unnoticed during both the full backup of the SQL database and the integrity check, such as DBCC CHECKDB. We’d like to provide some free manual solutions that any SQL administrator can attempt independently.
It has come to our attention that the transaction log backup process has encountered an interruption due to an error signifying a corrupted transaction log. To address this issue, we can attempt to resume the backup process from the point of failure, effectively excluding the problematic portion from the backup. Let’s delve into the steps required for this:
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.
Users have an alternative manual method at their disposal to successfully perform a transaction log backup without encountering any errors. This can be achieved by executing the SQL log backup with the “CONTINUE AFTER ERROR” option. This can be done by running a T-SQL script or by utilizing the option directly within the Microsoft SQL Server Management Studio.
To begin, launch Microsoft SQL Server Management Studio with administrative privileges. Within the “Backup Database” window, navigate to the “Media options” or “Options” located on the left-hand panel. Under the “Reliability” section, ensure that the “Continue on error” checkbox is selected, and then confirm by clicking “OK.” Subsequently, initiate the backup process, which will proceed without encountering the same error.
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.
Unexpected occurrences such as log errors can cause frustration for SQL users at any moment. This blog addresses manual remedies for such issues while also presenting a durable and trustworthy solution for advanced SQL database backup recovery.