How to Identify Corrupted SQL Backup File

Maintaining regular backup of SQL databases is a good practice. However, you may find yourself in a situation where the database cannot be restored from a backup. This happens when the backup file becomes corrupt. To avoid such a situation, you must regularly test your backups for corruption. 

How Do I Verify a SQL Server Backup File for Corruption?

You can check if your backup file is useable and not corrupted by restoring it and then running DBCC CHECKDB on the newly restored database. However, restoring the db and performing consistency checks for each database restore process is not feasible. That’s because the process takes additional processing time and hard disk space to complete.

Is there a better alternative?

Yes. Using the ‘RESTORE VERIFYONLY’command can help you verify that your backup file is not corrupted and can be restored without any problem. The command does not restore a database. Instead, it reads through the backup file to ensure that it is useable after the backup process is complete.

Following are two different methods that can be used to validate SQL Server backup using the ‘RESTORE VERIFYONLY’ command:

Method 1 – Using T-SQL Statements

Execute the following command to check whether the backup file on disk is valid or not:

RESTORE VERIFYONLY FROM DISK = C:\YourDatabase.BAK GO

Usually, there are multiple database backups in a file. The above command will only check the first file. To check the second file in the backup, run the following command:

RESTORE VERIFYONLY FROM DISK = C:\YourDatabase.BAK WITH FILE = 2 GO

Method 2 – Using SSMS

When taking a backup of your SQL database using a maintenance plan or SSMS, the ‘RESTORE VERIFYONLY’ (i.e., Verify backup when finished) option is used to validate whether the backup is useable.

However, the VERIFYONLY option is not enough to guarantee that your backup file can be restored without any issue. That’s because SQL Server produces backup in Microsoft Tape Format (MTF), and the ‘verify backup when finished’ option only performs a few checks on the MTF blocks containing the backup. Using only the VERIFYONLY option may report that the backup file is useable, even if it cannot be restored.

Thus, it’s recommended that the ‘Verify backup when finished’ option must be combined with the ‘Perform checksum before writing to the media’ option to identify whether the SQL backup file is corrupted or not.

Note: On selecting the ‘Perform checksum before writing to media’ option, SQL Server writes CHECKSUM value for each page when the backup is taken. The Server recalculates the CHECKSUM value for each page against the CHECKSUM generated during the backup process to validate whether the backup file is useable or not.

Both these options are available under ‘Reliability’ in SSMS, as you can see in the image below:

What Next?

If your SQL database backup file is corrupted, you can try repairing your database using the DBCC CHECKDB command with the ‘REPAIR_ALLOW_DATA_LOSS’ repair option. If you’re lucky enough, you may restore your database with minimal data loss.

A better alternative is to use Stellar Toolkit for MS SQL software to restore your SQL database with all its data intact. The software comprises tools to repair a corrupt SQL database and extract db from corrupted backup. It also offers a tool to reset passwords of SQL Server database files.

Conclusion

As DBAs, you are already regularly backing up your SQL databases. However, you must also ensure that SQL Server can read the backup if your database needs to be restored. But, you cannot use the backup file to restore the database if it is corrupt. Hence, it is important to identify corrupted SQL backup file before performing the database restore process.

Also read: How to Recover SQL Server Database without Backup?

How to Fix DBCC CHECKDB Consistency Errors?