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

 

Validate a SQL Server Backup can be Restored


By:   |   Last Updated: 2016-09-21   |   Comments   |   Related Tips: More > Backup

Problem

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?

Solution

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))
AS
 BEGIN
  DECLARE @tsql varchar(400)
  
  SET NOCOUNT ON
  SET @tsql = 'RESTORE VERIFYONLY FROM DISK = ' + '''' +  @backupFilePath + '''' + ' WITH CHECKSUM'

  EXEC (@tsql)
  
  SET NOCOUNT OFF
 END
GO

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,

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

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 MSSQLTips.com:


Last Updated: 2016-09-21


get scripts

next tip button



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.

View all my tips
Related Resources




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.



    



Learn more about SQL Server tools