Checking to make sure a SQL Server backup is useable



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.


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.


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.


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.


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


Comments For This Article

Wednesday, March 21, 2018 - 12:43:45 PM - Greg Robidoux Back To Top (75494)

Hi Jon,

You could use single_user or restricted_user access to limit access to the database.


Wednesday, March 21, 2018 - 12:11:13 PM - jon Back To Top (75492)

Thank you for this tip. Once the database is ready for use, it would be good to spot check it before releasing to the general community. Other than sending out an alert, how would I keep the the one or two users who are chomping at the bit ready to get on the database when I have not released it yet? Is not single-user-mode one option? That can be tricky if the database does not know you are the appropriate single user that is allowed?


Thank you.


Jon Lellelid

get free sql tips
agree to terms