How to Deal with Media Errors while Performing a Backup in SQL Server

In this article, we look at backup checksums, how they help and how to deal with media errors that arise when performing backups.

Apart from simply performing backup operations to safeguard your data, SQL Server also allows you to ensure that your backup data’s integrity is maintained. To make sure that the data you are backing up is free of errors, you can opt for creating a backup checksum. And depending on the kind of checksum used, you can identify problems with your backups if there are any.  A backup checksum can be created using backup operations in SQL Server along with a restore operation for the purpose of validation.

Learn about Backup Checksums

Backup checksums in SQL Server can determine if the backup is healthy or not, you can determine what a backup checksum does to your data, meaning whether it stops when it comes across bad data, or does it continue running the backup. For checking for errors in the backup data, you can make use of RESTORE and RESTORE VERIFYONLY options. However, simply checking for problems is not enough, you should also know how to deal with those problems, and even worse, how to deal with problems that might arise when you are making use of backup checksums.

Create Backups using Backup Checksum

When you are making use of backup checksum for performing backups, the following operations will occur

1. Page level information will be verified by the backup operation before it gets written to backup media. If there is no error in the page, it will not be verified, all unverified pages will be added as it is. If an error is encountered during the verification of the page, the backup will fail.
2. Irrespective of whether there are any page checksums or not, Backup will generate a different backup checksum to be used in backup streams. The Restore operations will be able to optionally make use of the backup checksum to ensure that there is no corruption in the backup. This backup checksum will be stored on backup media and not on database page; it will have the scope of being used optionally during restore.
3. This set of backup will be flagged as one containing backup checksums

Dealing with Errors when performing backup in SQL Server

If you are making use of a backup checksum, it will stop the Backup or Restore process on encountering an error, say a corrupted sql server file page, and will only continue with the RESTORE VERIFYONLY operation. However, what happens after the given operation has failed can be controlled by you.

If the backup continues even after an error is detected, the following actions would occur.

1. The backup that has been placed on the backup media will be flagged as one containing errors, and will begin tracking the page in suspect_pages tables of the database.
2. The error will be logged in SQL Server error log.
3.  The backup will be masked as the one containing error.
4. A message will be issued stating that the process of generating a backup was successful but it consists of page errors.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com