By: Greg Robidoux
Overview
The RESTORE VERIFYONLY command checks the backup to ensure it is complete and the entire backup is readable. The does not do an actual restore, but reads through the file to ensure that SQL Server can read it in the event that a restore using this backup needs to occur.
Explanation
The RESTORE VERIFYONLY option is a good choice to check each backup after the backup has completed. Unfortunately this takes additional processing time for this to complete, but it is a good practice to put in place. Following are ways you can do this with T-SQL and SSMS.
T-SQL
Check a backup file on disk
The following command will check the backup file and return a message of whether the file is valid or not. If it is not valid, this means the file is not going to be useable for a restore and a new backup should be taken. One thing to note is that if there are multiple backups in a file, this only checks the first file.
RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK GO
Check a backup file on disk for a particular backup
This command will check the second backup in this backup file. To check the contents in a backup you can use RESTORE HEADERONLY and use the Position column to specify the FILE number.
RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK WITH FILE = 2 GO
SQL Server Management Studio
When creating a backups either using a maintenance plan or through SSMS you have the option to turn on the RESTORE VERIFYONLY option as shown below. This can be done for all backup types.
Maintenance Plan
Backup using SSMS
