Resolving Log Corruption Detected During Database Backup in SQL Server

This error usually happens when you are doing a backup of the transaction log. The error is like this one:

Msg 26019, Level 16, State 1, Line 1 BACKUP detected corruption in the database log. Check the errorlog for more information. BACKUP LOG is terminating abnormally.

In this article, we will explain why this error happens and how we can solve this problem.

What Does the Database Log Error Corruption Mean?

A level 16 error is not so critical. It is in the category of miscellaneous user error. The database will work. If you do a full backup, it will work. If you run the DBCC CHECKDB, it will not detect an error.

However, the transaction log file is damaged. Line 1 is the line of code that is failing.

Why Does This Error Happen?

To find out the reason for this error, check your SQL Error Log.

You can find your Error Log in the SQL Server Management Studio (SSMS). 

In the Object Explorer, go to Magagement>SQL Server Logs. You have the current log and older logs. Double-click the logs, and you can see the events and errors. 

You can also check the Event Viewer and go to Windows Logs>Application and look for MSSQLServer errors.

The most common problems that can generate log corruption are problems in hardware. Also, the software can damage the database.

For example, a power failure can shut down the server while doing a transaction, and then the log can be corrupted. Another common problem is that the disk fails. It happens if the disk is old, or there is a power outage, or there is an electricity problem. If the server temperature is high, a hardware problem can occur.

If we talk about software, the software can corrupt the log. For example, viruses and malware can damage the log files. 

How To Resolve Log Corruption Detected During Database Backup in SQL Server

If we do a full backup of a corrupted database, the backup will run, but we will back up the database with a corrupt log file.

If we try to back up the log file only, we will get the error mentioned before.

A solution for that problem is to back up with the Continue on error option.

To do that, open the SSMS. 

In the Object Explorer, right-click the Database and select Tasks>Back Up 

Select the Transaction Log option.

In the Media Option, select the Continue on error option.

This option will continue doing the backup even when the transaction log is corrupt.

Another way to solve this problem is to set the database to simple recovery mode.

In SSMS, go to the Object Explorer.

Click the Database, and right-click the database, and select Properties.

Go to the Options page and select the Simple Recovery model.

Run a checkpoint using T-SQL.

CHECKPOINT

Do a full backup of your database.

Now, you will be able to do a backup of the log file without errors.

How To Resolve Log Corruption Detected During Database Backup in SQL Server Using Stellar Repair for MS SQL

Another way to solve this problem is to use Stellar Repair for MS SQL. This software can repair the database using the SQL Server Data file, or it can use a damaged SQL Server backup to recover all the information. Once you have the database back, you can back up your log file without errors.

To do that, you need to download your software from this link.

We will need to take the database online first. To do that, run the following command:

ALTER DATABASE stellardb

SET OFFLINE;

You will need to find the data file. The data file is a file with .mdf extension. This file contains the database information.

Optionally, you can Browse and select the mdf file if you know where it is and press the Repair button.

Once repaired, you can Save your data in a New Database. The Live database is to replace the current one. When you select other formats, you can export your table and view data in Excel, CSV, or HTML files.

If you select the New Database or Live Database, you will be able to back up the log file without errors because the repaired database will not be corrupted.

Conclusion 

In this article, we learned what error occurs when the log is corrupted. Also, we learned why this error occurs. In addition, we learned to back up using the Continue on Error option. Finally, we learned how to repair the database using Stellar Repair for MS SQL.