CHECKSUM and VERIFYONLY

Alexandr Omelchenko Glossary

A database backup process is an integral part of a recovery system. All backups must be operable so that they can be restored at any time. It is recommended to make regular database backups and the majority of SQL Server users actually follow this recommendation. But how can you be sure that you will be able to restore the database from the backups? Sometimes backups can be corrupted. This is why it is useful to test database backups for their integrity. Of course, the best check for any backup is a restore to a server, but there are some other, less radical ways of doing it. Let’s explore CHECKSUM and VERIFYONLY options.

CHECKSUM

The checksum is a value calculated from the data page bytes. Equal data pages have equal checksums. To make the checksum recorded on each page of the backup file the “WITH CHECKSUM” option needs to be added to the “BACKUP DATABASE” command.

Using a CHECKSUM option makes the database backup process longer, but if the database is really large it is better to use the CHECKSUM option and be confident that all pages are written to the disk and ready for the restore process. To check the checksum value use the following SQL Server option:

VERIFYONLY

The VERIFYONLY  option does not restore the backup, it only verifies it. The VERIFYONLY performs several checks, such as:

  • Checksum
  • Availability of free space in the destination place
  • Some header fields of database pages
  • The readable state of all volumes and the state of the backup set

It is recommended to check the backup file before the restore process with the help of the VERIFYONLY option. If the file is in a proper state the SQLServer will display the following message:

The backup set on file 1 is valid.

If even a single byte is damaged the SQLServer will report the following:

Msg 3241, Level 16, State 0, Line 5
The media family on device 'd:\checksum.bak' is incorrectly formed. SQL Server cannot processthis media family.
Msg 3013, Level 16, State 1, Line 5
VERIFY DATABASE is terminating abnormally.

How to Use CHECKSUM And VERIFYONLY Options

Here are some ways how to add CHECKSUM to the database backups and how to check them with the help of the VERIFYONLY option:

T-SQL

Use T-SQL Command to work with CHECKSUM and VERIFYONLY options:

To make a database backup simply add the “WITH CHECKSUM” clause:

BACKUP DATABASE your_database TO DISK = 'backup_with_checksum.bak' WITH CHECKSUM

To check the database with the VERIFYONLY option use the following syntax:

RESTORE VERIFYONLY FROM DISK = 'backup_with_checksum.bak'

SQL Server Management Studio

Another way of using CHECKSUM and VERIFYONLY options is to apply them in SQL Server Management Studio (SSMS):

For that, you need to right-click on the database you want to backup, select “Tasks”, then “Back up…”. In the window that opened select the “Media Options” page and check “Verify backup when finished” and “Perform checksum before writing to media”. Look at the picture below:Checksum

SQLBackupAndFTP

If you prefer third-party utilities you can also use SQLBackupAndFTP to set up CHECKSUM and VERIFYONLY options:

Just scroll down to the “Backup options” section and check off the “Enable CheckSum” and “Verify After Backup” options.