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

e2

Backup using SSMS

e3


Last Update: 3/17/2009




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.

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 (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