Verifying SQL database backups automatically

Backups are the starting point for any serious disaster recovery strategy. Creating SQL database backups on a regular basis is just the first step. Equally important is to make sure they are also reliable and restorable. This is the only way to avoid unpleasant surprises in case of a disaster

The following SQL database backup verification methods can be used:

  1. RESTORE VERIFYONLY command – checks whether a SQL database backup can be read and restored

    To test a SQL database backup, execute the following

    FROM DISK = 'E:\Test\AdventureWorks2012_Full.bak'

    If the backup is valid, the SQL Server Database Engine returns:

    The backup set on file 1 is valid

    Note, however, that data structure and reliability cannot be verified this way

  2. To verify data structure and reliability in a SQL Server backup, the backup must be created using WITH CHECKSUMS (validates page checksums and generates a backup one). When added to RESTORE VERIFYONLY, the statement checks data integrity in a backup

    If you execute

    FROM DISK = 'E:\Test\AdventureWorks2012_Full.bak'

    against a database backup not created using WITH CHECKSUM, you’ll get 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.
    Msg 3013, Level 16, State 1, Line 1
    VERIFY DATABASE is terminating abnormally.

    If the database backup is created using WITH CHECKSUM and the data integrity is verified, the following message is shown:

    The backup set on file 1 is valid

    Keep in mind, however, that WITH CHECKSUM adds overhead, makes backups bigger and increases the backup creation time (significant for large databases)

    Since RESTORE VERIFYONLY doesn’t check all header information in a backup, there is a chance that the backup is invalid and impossible to restore, even if the statement is executed successfully

  3. A foolproof way to verify a database backup is to restore it. You can also run DBCC CHECKDB immediately against the restored database, to confirm everything is OK. If both actions are successful, the backup is fine. Unlike the RESTORE VERIFYONLY backup verification (where only backups created using WITH CHECKSUM are verified), this method can be used on backups created without WITH CHECKSUM

Use SQL Server Management Studio to restore backups

To automatically create a SQL Server database backup, restore and verify it, use T-SQL and schedule a SQL job:

  1. To create a database backup, right-click the database in Object Explorer and select Tasks | Back Up

    object explorer

  2. Specify the backup type and location


  3. Open the Script drop-down menu and select Script Action to Job


  4. A new job is automatically created (create database backup task is the first step)
  5. Make sure the On success action option for the step is Go to the next step, and click OK

    /Job Step Properties

  6. Click New to add a new step that restores the SQL database backup created in step 1. The command must contain MOVE, as the database with the same path and file names already exists on the SQL Server instance

    RESTORE DATABASE AdventureWorks2012_Test
       FROM DISK = 'E:\Test\AdventureWorks2012_Full.bak'
    MOVE 'AdventureWorks2012_Data' TO 'E:\test\AdventureWorks2012_Data.mdf', 
    MOVE 'AdventureWorks2012_Log' TO 'E:\test\AdventureWorks2012_Log.ldf'

    step properties restore

  7. Click OK
  8. Add the third step to check logical and physical integrity of all objects in the restored database. The step type is Transact-SQL and the command:

    DBCC CHECKDB (AdventureWorks2012_Test)


  9. In the Job dialog go to the Schedules tab
  10. Specify the job name, execution time and frequency

    New Job Schedule

  11. Click OK
  12. To verify that the SQL job is created successfully, right-click it in Object Explorer and select Start Job at Step

    SQL Server Agent

  13. Select the first step. If all steps are executed successfully, the job status will be ‘Success’

    Start Jobs FUJITSU

  14. The restored database will be listed among other databases in Object Explorer

    Fujitsu SQL

The main disadvantage of this method is that considerable time and space are required when restoring a large database

Use ApexSQL Restore to restore backups

ApexSQL Restore is a SQL Server restore tool that attaches native and natively-compressed SQL database backups (full and differential), and transaction log backups as live databases. Once attached, the backups will be accessible via SQL Server Management Studio, Visual Studio or any third party tool. ApexSQL Restore Command Line Interface enables backup restoration scheduling via SQL jobs

  1. Create a batch file (e.g. named RestoreBatch.bat) using the statement in this format:

    “<ApexSQL Restore installation path>\” /server:<server name> /user:<user name> /password:<SQL Server password> /database:<database name> /backup:<backup file path and name> /restorepath: <path for restored database files> / database: <restored database name> /attach

    For example, to restore the AdventureWorks2012_Full.bak backup stored in the E:\Test folder to the Fujitsu\SQL2012 instance as the database named AdventureWorks2012_Restored, and to store the new MDF and LDF files in the E:\Test folder, save the following

    “C:\Program Files (x86)\ApexSQL\ApexSQLRestore2012\” /server:Fujitsu\SQL2012 /user:sa /password:sqladmin /backup: E:\Test\AdventureWorks2012_Full.bak /restorepath: E:\Test /database:AdventureWorks2012_Restored /attach

  2. In SQL Server Management Studio, create a job for SQL database backup creation, using the steps 1 to 5 given in the Use SQL Server Management Studio to restore backups section
  3. In the Steps tab of the Job dialog, click New
  4. Specify the step name and select Operating system (CmdExec) as a type
  5. Click Open, then navigate to the RestoreBatch.bat file

    Database Restore

  6. Create a new step to check the restored database’s integrity using

    DBCC CHECKDB (AdventureWorks2012_Restored)

    New Job

  7. If you want to drop the database after the verification, add a new step that will execute:

    DROP DATABASE AdventureWorks2012_Restored

    New Job Step 2

  8. Once all 4 steps are created, schedule the job execution, as described in step 10 in the Use SQL Server Management Studio to restore backups section

    ST Name Type

As shown, ApexSQL Restore also leverages SQL Server jobs to automate database verification. The advantages of ApexSQL Restore backup verification are quicker restoration and less hard disk space usage. The space saved is up to 99%

Once you set automatic SQL database backups verification, you’re one step closer to a successful disaster recovery

August 2, 2013