SQL Server Backup and Restore Media Errors

SQL Server application has an important mechanism which is capable to detect errors. The backup checksum is optional which can be created by the backup operation and is validated using the restore operation. It completely depends on you to stop or continue the operation once it finds errors. For this the backup is supposed to contain backup checksum, RESTORE VERIFYONLY and RESTORE statements. We will discuss about SQL Server backup and restore media errors in this section.

SQL Checksums:

SQL Server is integrated with different checksums;

• Checksum on pages
• Checksum in log blocks
• Backup Checksum

Backup Checksum:

Preface: Backup checksum is operation which basically checks if the SQL database whose backup is being created is having a consistency or not. If any inconsistency occurs or any torn-page is notified in the database then BACKUP verifies it using the checksum mechanism.

As mentioned above checksums are also provided on pages, thus if there is any page-checksum or torn-page info present in that particular page which is being backed up, BACKUP will verify this checksum also along with its status and page IDs. However when backup checksum is being created it will not add any checksums to the pages, pages will be backed up as it is in the database. No modification in them will occur in back up mechanism.

Downside: As this mechanism is advance and requires too much of verification in order to create backup checksums. Its usage might drop the performance of SQL Server. It not only slows down the backup procedure, but also affects normal workflow on Server.

Note: BACKUP will never do any modification on the original database on disk or contents of the database.

Working:

• Backup operation verifies the page-level information prior the process of page writing on backup media. This includes torn page or page checksum existence.
• If any page error is encountered by operation during verification, the backup fails.
• Even if the page checksums are present, BACKUP will create backup checksums separately for backup streams.
• Backup checksum is located on the backup media and not on the database pages, no modifications are done on the original source pages. This checksum can be used when you restore the backup and is optional in order to verify backup.

Page Checksum Errors:

Generally BACKUP or RESTORE operation can fail if it encounters a page checksum error, but RESTORE VERIFYONLY operation continues. SQL Server backup and restore media errors can't stop you from the operation continuity. It completely depends on you whether operation should stop or continue.

If BACKUP application continues after error recognition it follows up following steps;

• The backup set with Page Checksum error on the backup media will be flagged with errors and the page will be tracked in suspect_pages table in the MSDB database.
• The error will be logged in SQL Server error log.
• Will prompt a message that backup was successfully created but has page errors.
• The backup with such kind of Page Checksum error will be marked as it contains this error in the is_damaged column of msdb.backupset.

Many times user continues the backup and this backup set might be difficult to get restored to fetch back the database. SQL Server backup and restore media errors are flashed while restoring such kind of SQL backups and in that situation it is recommended to use professional tool like SQL Server BAK file repair tool which can provide you error-free recovery of your SQL backup file.

Remember that you can enable or disable these backup checksums at your own as per your needs during Backup or Restore operation.