Checksum on Backup and Restores May 28th, 2014

Vinod Kumar

I am a firm believer of using new features that enhance productivity and life of a DBA. In that category is the feature of CHECKSUM. I have written a number of article on them in the past and are worth a mention:

  1. Torn Page Vs CHECKSUM
  2. CHECKSUM and SQL Server
  3. CHECKSUM and TempDB
  4. CHECKSUM Vs FileStream
  5. SQL Server 2014: Checksum with backups

SQL Server BACKUP and RESTORE statements provide the CHECKSUM option to include checksum protection on the backup stream and trigger the matching validation operations during restore. To achieve a checksum-enabled backup, the BACKUP command must include the CHECKSUM option. Read more syntax of CHECKSUMS from MSDN.

Backup and restore operations that use checksum capabilities increase data integrity protection and also increase CPU usage requirements at the point of backup. A backup or restore with the checksum option requires that each byte be checked as it is streamed, thereby increasing CPU usage. The checksum that is used for backup and restore uses the same algorithm to calculate the checksum value for the backup media as is used for data pages and log blocks.

The following rules apply to the BACKUP and RESTORE command CHECKSUM operations:

  1. By default, SQL Server BACKUP and RESTORE operations maintain backward compatibility (NO_CHECKSUM is the default). This needs to be specified explicitly.
  2. The database’s PAGE_VERIFY setting has no effect on backup and restore operations; only the CHECKSUM setting on the backup or restore command is relevant.
  3. The backup and restore checksum is a single value representing the checksum of the complete stream; it does not represent individual pages or log blocks located in the backup stream. The value is calculated during the backup and stored with the backup. The value is recalculated during the restore and checked against the stored value.
  4. Backup with the CHECKSUM option will not change the pages as it saves them to the backup media; a page’s protection state (NONE, CHECKSUM, or TORN) is maintained as read from the database file. If a checksum was already stored on the data page, it is verified before the page is written to the backup stream.
  5. Restore and Verify commands can be used to validate the CHECKSUM if the backup was created by using the CHECKSUM option. Trying to restore with the CHECKSUM option on a backup without a checksum returns an error as shown below.

Msg 3187, Level 16, State 1, Line 4
RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.

From an third-party utilities perspective and CHECKSUM, please read the KB-2656988.

Now that you got some of the fineprints with CHECKSUM and backups, I hope you will use the same in your environments in future atleast if you are not using.

Tags: , , , , , , , , ,

This entry was posted on Wednesday, May 28th, 2014 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply