Verifying Backups
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.
RESTORE VERIFYONLY Checks
In SQL Server 2005 and later versions, the 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