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



By:

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


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.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options

-Greg


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