Media errors during backup or restore operations are quite stressful because usually, these tasks should run smoothly.

But just in case you happen to have one such error, then it would be ready if you would be prepared or have a bit of knowledge about these errors before you see them.

SQL Server 2012 allows you to continue to recover a database even in the case there are errors.

It has a new mechanism which is an important step forward in the field of error detection which gives you the option of creating a backup and doing a checksum operation during backup and during restore.

You have the ability to control if the operation will look for errors and if it encounters any errors, you also can specify if it should continue or stop.

If such a backup does contain such a checksum you can use the RESTORE and RESTORE VERIFYONLY options to look for errors that are caused by the media.

Backup checksums

Backup checksums are a feature of SQL Server since the 2005 edition, and up until the 2012 generation, there are multiple types of checksum operations available for the user.

Checksums are important because they help you detect media errors during backup or restore operations. Without such verification, you might find out one day that all of your backups on a disk are not correct and consistent files because the disk is corrupted.

SQL Server has support for up to three types of checksum operations: a checksum operation for pages, a checksum for log blocks, and checksums for backups.

When you are doing a backup operation, the BACKUP command will verify that the data that has been read from the source keeps its consistency with any checksum or torn-page indicator that the database has.

The BACKUP command can also compute a checksum for the backup on the backup stream. When doing a backup checksum the operation does not put any additional information on the pages (checksums are not stored on pages), but the pages are backed up just as they are in the database. Thus, the pages are unmodified by the backup process.

But, an extra operation during the backup process will obviously have an impact on the time required to finish the backup. Doing checksums will have an influence on the time it takes to do your backups, but usually, it is not a significant increase.

Therefore, doing a checksum operation during your backup is optional, but recommended, if you care very much for your data.

Another thing that you should monitor before doing a backup operation is the usage of the CPU. Backup checksums will generate a CPU overhead, so your system is not affected to a great degree.

What happens during backup checksum

If checksums are enabled to occur during backup operations, the following steps will occur:

1. The first step of a backup operation follows these rules:
   • Before a page is written to the media, the backup operation will check the page-level information.
   • This means that it will do a checksum for the page, it will perform a torn-page detection or it either exists.
   • If neither exists, the backup cannot verify the page, which will determine the page to be included as it is and the contents of the page are added to the overall backup checksum value.
   • If an error happens at this level, the backup operation will fail.
2. The second step in the operation is composed of:
   • Even if the page has checksums, the BACKUP operation will generate a separate checksum for the streams.
   • Restore operations will optionally make use of the backup checksum in order to verify that the backup has not been corrupted.
   • This backup checksum is stored on the media, not on the pages of the database and it can be optionally used during the restore process.
3. The third and final step assumes these steps:
   • The backup set is now flagged as having a checksum present.
   • This is stored in the has_backup_checksum column of msdb..backupset
   • During the process of restoring the backup if the checksums are present on the media then by default both of the RESTORE and RESTORE VERIFYONLY statements, which we mentioned before will check to make the verification on the checksum and page checksums.
   • In case there is no backup checksum, the operation of restoring will continue without further verifications.
   • This is because without a checksum the restore process cannot verify in a reliable manner page checksums.

What happens during an error

If media errors during backup or restore operations occur, then by default, after encountering such a checksum error, the RESTORE VERIFYIONLY operation will continue.

Although, you have the possibility of controlling what happens in case of such an error, if the process continues to give the best possible outcome or if it should stop.

If you choose to continue the operation after an error is encountered, the next steps will occur:

• The BACKUP operation will flag the backup set on the media as having errors and will track the page in the suspect_pages table which is part of the msdb database.
• The error will be logged in the SQL Server error log.
• The backup set will be marked as having an error,  by populating the is_damaged column from msdb.backupset table.
• A message will be issued if the backup has been successfully created, but the page has errors.