Verifying Backups

  • Article

Although not required, verifying a backup is a useful practice. Verifying a backup checks that the backup is intact physically, to ensure that all the files in the backup are readable and can be restored, and that you can restore your backup in the event you need to use it. It is important to understand that verifying a backup does not verify the structure of the data on the backup. However, if the backup was created using WITH CHECKSUMS, verifying the backup using WITH CHECKSUMS can provide a good indication of the reliability of the data on the backup.

In Microsoft SQL Server 2005, RESTORE VERIFYONLY has been enhanced to do additional checking on the data to increase the probability of detecting errors. The goal is to be as close to an actual restore operation as practical.

RESTORE VERIFYONLY Checks

Checks performed by RESTORE VERIFYONLY include:

  • That the backup set is complete and all volumes are readable.
  • Page ID (as if it were about to write the data)
  • Checksum (if present on the media)
  • Checking for sufficient space on destination devices

Restoring a database, does not guarantee that it can be recovered. Furthermore, a database recovered from a verified backup could have a problem with its data. This is because verifying a backup does not verify whether the structure of the data contained within the backup set is correct. For example, although the backup set may have been written correctly, a database integrity problem could exist within the database files that would comprise the backup set. However, if a backup was created with backup checksums, a backup that verifies successfully has a good chance of being reliable.

Note

Transact-SQL has a set of DBCC statements used to verify the integrity of a database; for more information, see DBCC (Transact-SQL).

To verify the backup set

See Also

Concepts

Viewing Information About Backups

Other Resources

RESTORE VERIFYONLY (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance