How to Recover SQL Server Database After Backup Corruption

Backups are the most important asset of any organization that uses software. The backups are essential to recover the critical business data that has been corrupted or deleted due to hardware failures, accidental deletions, or catastrophic events. However, sometimes backup files themselves become corrupted due to many reasons. It hinders the entire database restore strategy and might cause a lot of financial issues to any organization.
This article explains what backup corruption is, why it happens, and provides a step-by-step guide to recover SQL database after backup corruption. We will also learn the potential reason which we might encounter while restoring the backup.

Understanding Backup Corruption in SQL Server

In simple words, a corrupt backup means a corrupted *.bak file which we are unable to restore. The backup can be corrupted due to many reasons.

Hardware issues – The backup might be corrupted due to the hardware issues like the storage sub systems where the backups are being stored. The hardware can fail due to many factors which can lead to the database backup corruption.
Software problems – Sometimes the backup process get interrupted due to SQL Server instance crashed while backup is in process.
Incomplete backups – The backup can get corrupted when the backups process is not completed due to the network latency or insufficient space in backup storage.
Malware or viruses – The malwares or viruses can also corrupt the backup files. Sometimes due to weak security, the hackers might be able to access the backup files and use certain tools which corrupt the backups.

The backups are very important because in case of database corruption or failure, we will be able to recover the data from backups only. If the backups are corrupted and you do not have any alternate solution to recover the data, there will be an irreversible data loss.

Prerequisites for SQL Database Recovery

Before attempting to recover the database, we must check the reasons for database backup corruption, find the valid backup to begin the restoration process.

Identify the corruption source

We can use any of the methods to identify the corruption source.

The details of the failed or interrupted backups are available in windows event viewer or the SQL Server error logs. If you are using SQL Scripts to take the backup, you can incorporate the logging mechanism in script.
You can also schedule a SQL Server agent job or windows task scheduler job to verify the integrity of the backup file. You can check the integrity of the backup by using RESTORE HEADERONLY or RESTORE VERIFYONLY statement. Here is the syntax of both commands.

RESTORE HEADERONLY FROM DISK = N'location_of_backup_file';
GO

You can read more about RESTORE HEADERONLY command here and RESTORE VERIFYONLY here.

The next step is to prepare the environment to begin the database restoration.

Preparing for the environment

Once the backup files have been identified, we should start preparing the database server. Along with provisioning the database server, we should take care of following items:

The server on which you are restoring the database should have adequate space. Also, the storage which server is using must not have any disk-level corruption.
The correct version of SQL Server must be installed. Also, verify that the resources allocated to the server are adequate, so the restoration process is not interrupted due to lack of CPU and memory.
If the database is partially accessible, place the database in a safe state for recovery.

Here is the step-by-step guide to recover the database of backup corruption.

Step-by-Step Guide to Recover SQL Database After Backup Corruption

Before restoring the backup, we must validate whether the backup is valid or corrupt. For example, we want to restore the AdventureWorks2012 database. Before we begin the restoration process, verify the backup copy available with us is valid. We have full, differential and transaction log backups. To check that, execute following query.

DECLARE @BackupFile_FullBackup NVARCHAR(255) = 'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_full_backup.bak';
DECLARE @BackupFile_DiffBackup NVARCHAR(255) = 'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_differential_backup.bak';
DECLARE @BackupFile_LogBackup NVARCHAR(255) = 'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_log_backup.bak';

-- Step 1: Check the backup header (metadata about backup set)
RESTORE HEADERONLY FROM DISK = @BackupFile_FullBackup;
RESTORE HEADERONLY FROM DISK = @BackupFile_DiffBackup;
RESTORE HEADERONLY FROM DISK = @BackupFile_LogBackup;

-- Step 2: Check the backup file list (files included in the backup)
RESTORE FILELISTONLY FROM DISK = @BackupFile_FullBackup;
RESTORE FILELISTONLY FROM DISK = @BackupFile_DiffBackup;
RESTORE FILELISTONLY FROM DISK = @BackupFile_LogBackup;

-- Step 3: Verify that the backup is not corrupted and can be restored
RESTORE VERIFYONLY FROM DISK = @BackupFile_FullBackup;
RESTORE VERIFYONLY FROM DISK = @BackupFile_DiffBackup;
RESTORE VERIFYONLY FROM DISK = @BackupFile_LogBackup;

Once the backups are verified, we can start the restoration process. First, we must restore the full backup, then secondly, we must restore the differential backup and finally if the database is in full recovery model, we must restore all transaction log backups.

Here is the command to restore the full backup.

RESTORE DATABASE [Stackoverflow2010_Restore] FROM  DISK = N'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_full_backup.bak' WITH  FILE = 1,  MOVE N' Stackoverflow2010' TO N'D:\MS_SQL\DATA\Stackoverflow2010_Restore.mdf',  MOVE N' Stackoverflow2010_log' TO N'D:\MS_SQL\LOG\Stackoverflow2010_Restore_1.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5

Here is the command to restore the differential backup.

RESTORE DATABASE [Stackoverflow2010_Restore] FROM  DISK = N'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_differential_backup.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

Here is the command to restore the transaction log file.

RESTORE LOG [Stackoverflow2010_Restore] FROM  DISK = N'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_log_backup.trn' WITH RECOVERY  NOUNLOAD,  STATS = 10

Note that the transaction log file will be restored WITH RECOVERY option.
You can refer to this article which explains the restoration process in detail.

Addressing Common Errors During Recovery

While restoring the database, we might encounter certain errors. Here are the common errors and their root cause and potential fix. Note that these errors are generic. For detailed description of errors, refer to the Microsoft documentation.
Error: The backup set is corrupted.
Reason:

This error occurs when one or multiple backup files are corrupted.

Potential fix and Precautions

This is very critical error, and which directly indicates that backup set which we are trying to restore is not usable. Now, all we can do is to find a good working backup and restore it. In most cases, you will encounter a major data loss.
Always test your backups. Keep the backups on redundant storage. In simple words, keep the backup of backups.

Error: The log or differential backup is missing:
Reason: The error indicates that while restoring the sequence of backups, there is a differential or log backup is missing. The error indicates a recovery chain has been broken due to a full backup is taken between a full backup and differential backup. Read this article to learn more about it.
Potential fix and precautions:

This is very critical error and the only way to fix it to restore the last full backup and subsequent working differential and log backup.
If you want to take an ad hoc backup, always use the COPY_ONLY option. It will ensure that recovery chain does not get broken and restore completes without error.
Error: Database is in use
Reason: This error occurs when you are trying to overwrite the database with backup and the database which you are restoring is ONLINE and MULTIUSER state.
*Potential Fix: *
This is not a critical error. The issue can be resolved by changing the state of database from MULTI_USER to SINGLE_USER. Execute following command to do that.

ALTER DATABASE <Database_Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Once the database is in SINGLE_USER mode, you can rerun the database restoration process.

Leveraging Tools for Efficient SQL Database Recovery

When the manual recovery method that I have explained fails, you can use the third-party tools which can help to recover the data. I personally use Stellar Repair for MS SQL which is professional solution that can help to repair and restore the corrupt database. Here are some key benefits

It can repair MDF and LDF database files.
It can recover the data and the database objects like tables, procedures and triggers.
It can also help to restore the data from the corrupt backups.

Conclusion

Backup corruption is one of the most challenging obstacles in database recovery. To avoid data loss, we must always verify backup integrity and restorability. Always maintain a clear recovery plan, always test the existing backups, and always store backups in multiple secure locations. In cases where standard restore methods don’t work, Stellar Repair for MS SQL provides a powerful way to recover SQL database from corruption and ensure business continuity.