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 would 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 into each page of the backup file the “WITH CHECKSUM” option needs to be added to “BACKUP DATABASE” command.

Using a CHECKSUM option makes database backup process longer, but if the database is really large it is better to use 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 destination place
  • Some header fields of database pages
  • Readable state of all volumes and the state of backup set

It is recommended to check the backup file before the restore process with the help of  VERIFYONLY option. If the file in 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 of how to add CHECKSUM to the database backups and how to check them with the help of VERIFYONLY option:

T-SQL

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

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

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

To check the database with 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 “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 “Enable CheckSum” and “Verify After Backup” options.