Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


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

Last Update: 3/17/2009

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


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

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

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

Learn more about SQL Server tools