Validate a SQL Server Backup can be Restored

By:   |   Comments   |   Related: > Backup


We need to programmatically validate our SQL Server database backups are not corrupted and that the backup can be successfully restored. We have limited storage and time so we are unable to actually restore the database. Are there any options to validate the SQL Server backup and restore process with T-SQL code?


I chose a T-SQL solution that combines the WITH CHECKSUM option in the SQL Server backup statement along with the RESTORE VERIFYONLY option in order to ensure that not only the backup is not corrupted, but also that the backup can be restored.

The procedure will get the backup file name and full path as parameter and will create a restore VERIFYONLY T-SQL statement that will do these two checks:

  • The statement will include a WITH CHECKSUM option that will verify for each page, the checksum value, by checking that it is present on the backup media and that is equal to the checksum value found in the restore statement and by that it will validate the backup checksum values.
  • The RESTORE VERIFYONLY statement, combined with the WITH CHECKSUM clause, will also add a check that the backup set is complete and the header fields of the database pages are restorable and also that there is sufficient space in the restore path for the restored database.

Note that database backups are stored in a special format called Microsoft Tape Format (MTF). Using only the RESTORE VERIFYONLY clause in the RESTORE statement will perform only simple checks on the MTF blocks and not on the actual data blocks, so adding the WITH CHECKSUM option for the backup command will yield a safer check.

Sample SQL Server Stored Procedure for Restore VerifyOnly

CREATE PROCEDURE dbo.usp_CheckBackupFileRestoreStatus (@backupFilePath varchar(200))
  DECLARE @tsql varchar(400)
  SET @tsql = 'RESTORE VERIFYONLY FROM DISK = ' + '''' +  @backupFilePath + '''' + ' WITH CHECKSUM'

  EXEC (@tsql)

SQL Server Backup with Checksum and Restore VerifyOnly Example

-- Check the backup file in the c:\mssql\data\northwind.bak directory after a full database backup,

TO DISK = 'c:\mssql\data\northwind.bak' WITH CHECKSUM
USE Northwind
EXEC dbo.usp_CheckBackupFileRestoreStatus 'c:\mssql\data\northwind.bak'

In the messages tab the result is:

The backup set on file 1 is valid.
Next Steps
  • The procedure was tested with SQL Server 2012 and 2014 Developer Editions.
  • The initial backup database statement should include the WITH CHECKSUM clause, otherwise the procedure will fail with the following error message: Msg 3187, Level 16, State 1, Line 1 RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.
  • Check out all of the SQL Server Backup and Restore resources on

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms