Thursday, March 29, 2012

Backup and Restore Test

            There is a good saying that every DBA should live by: 

“A database is not backed up until it is restored”
Recoverability is the bread and butter of a Database Administrator.  You are pushing out all flavors of backups (full, differential, transaction log), but your backups are completely useless unless they can restore to a database with integrity.

I have written a Stored Procedure that automates the process of taking a backup file and restoring the database from it and doing a DBCC CHECKDB to check the logical and physical integrity of all database objects.

Before we dive into the Stored Procedure, there are a few things to know of the current version of this code as this blog post is written:
1.       Currently only full database backups are able to be tested (differentials and transaction log backups to come shortly)
2.       The Stored Procedure resembles more of a Stored Novel, but I decided that as a shipping object from me to you, it made sense to keep the database footprint as small as possible (whether you decide to create this Stored Procedure in master, your DBA database, or wherever you so choose).  Because of this, I did not separate differing logic into multiple Stored Procedures, but feel free to slice, dice, and have fun with the code
3.       This code was written and tested with SQL Server 2008 R2.  Even if you are on this version of SQL Server, test in a testing environment to ensure it works the way you want/expect (and, as always email me at with issues, or use the Issues portion of GitHub to report bugs to me)

Now onto the fun part:  Because the code is so extensive, you may not have 30 minutes to hash through it all and my explanations.  So, here is the abbreviated pseudo code:
1.       Get the parameters from the backup file
2.       Restore a temporary database from the backup file
3.       Run a DBCC CHECKDB against the temporary database
4.       Report the results to the end-user
5.       Drop the temporary database

Now, for the more extensive explanation.  Take a deep breath; we’re diving into it…

This SP takes 3 parameters (the 3rd is optional):  the first parameter, @backup_filename, is the full path and filename of the backup file to be handled.  @restore_temp_path is going to be a full directory path of where the user will want to have temporary database files stored (by temporary, I mean temporary.  A RESTORE DATABASE shortly followed by a DROP DATABASE.  But I felt it best to give you the full control of everything this code touches).  @backup_set_file_number is the backup set number on the device.  This will default to 1, as typically you’d just have a backup per device and this would be 1.  Flexibility reigns, so you can choose what you like as a user.
create procedure dbo.sp_backup_restore_test
      @backup_filename nvarchar(1000),
      @restore_temp_path nvarchar(1000),
      @backup_set_file_number int = 1

Construct the dynamic SQL commands to gather the necessary information from the backup device.
            @exec_cmd_filelistonly = 'restore filelistonly from disk = ''' +
                  @backup_filename + ''' with file = ' +
                  cast(@backup_set_file_number as nvarchar),
            @exec_cmd_headeronly = 'restore headeronly from disk = ''' +
                  @backup_filename + ''' with file = ' +
                  cast(@backup_set_file_number as nvarchar)

Create a temporary table to house list of data and log files contained in the backup set.
      create table #restore_filelistonly_result
            LogicalName nvarchar(128) null,
            PhysicalName nvarchar(260) null,
            -- ... abbreviated for simplicity

      insert into #restore_filelistonly_result
            -- ... abbreviated for simplicity

Create another temp table to house the header information for the backup set.
      create table #restore_headeronly_result
            BackupName nvarchar(128) null,
            BackupDescription nvarchar(255) null,
            -- ... abbreviated for simplicity
      insert into #restore_headeronly_result
            -- ... abbreviated for simplicity

Retrieve the database name from the temp table that stores the result of RESTORE HEADERONLY.
      -- get the database name
      select @database_name = DatabaseName
      from #restore_headeronly_result

Append our handy pre-defined suffix in case we are doing the restore test on the same instance as the original database (a workaround for database name conflicts).
      -- set the database name as the same with a suffix
      select @database_name += @database_name_suffix

Aggregate to total hard disk space that the restore will take; this is in preparation to make sure that there is enough space on the drive to do the quick RESTORE/DROP.
      -- get the drive space required to do a restore
      select @drive_space_required_mb = sum(SizeMB)
      from #restore_filelistonly_result

Now we want to retrieve and store the free space per drive by calling the sys.xp_fixeddrives Extended Stored Procedure.
      create table #fixed_drives
            drive_name char(3) not null,
            free_space_mb int not null

      insert into #fixed_drives
      exec master.sys.xp_fixeddrives

Retrieve the available space for the drive that was specified as the root drive for the temp directory parameter.  Also, do a check to make sure that the specified parameter is actually a legitimate and available drive.  The last check is to make sure there is sufficient space on the temporary directory’s drive to house the restore (with a little space buffer in there just for good measure:  Defaults to 1 GB).
            @drive_space_available_mb = free_space_mb
      from #fixed_drives
      where drive_name = left(@restore_temp_path, 1)
      -- check to make sure the temp path drive exists
      if @drive_space_available_mb is null
                  raiserror('Unrecognized temp path drive', 16, 1)
                  return -1
      -- make sure there is enough space on the drive with a 1 GB buffer
            @drive_space_available_mb <  
            @drive_space_required_mb + @drive_space_tempbuffer_mb
                  raiserror('Insufficient temp drive space', 16, 1)
                  return -1

The following code does a few things:  First off, it creates the base of the RESTORE DATABASE command.  Then it uses a cursor to loop through all of the database files.  After all, we can’t assume there’ll only be one data file and one log file.
      select @restore_cmd =
            'restore database ' + @database_name +
            ' from disk = ''' + @backup_filename +
            ''' with file = ' +
            cast(@backup_set_file_number as nvarchar) + ', '
      -- use a cursor to iterate over the database files
      --    in order to handle an unknown amount of database files
      --    for the tested backed up database  
      declare file_iterate cursor for
            'move ''' + LogicalName +
            ''' to ''' + @restore_temp_path + NewFileName + ''','
      from #restore_filelistonly_result
      open file_iterate
      fetch next
      from file_iterate
      into @move_file
      while @@fetch_status = 0
            select @restore_cmd += @move_file
            fetch next
            from file_iterate
            into @move_file
      close file_iterate
      deallocate file_iterate
      -- remove the trailing comma
      select @restore_cmd = left(@restore_cmd, len(@restore_cmd) - 1)

As the comment states so well, call the RESTORE DATABASE dynamical SQL and run DBCC CHECKDB on the restored database.
      -- restore the database and run DBCC CHECKDB
      dbcc checkdb(@database_name)
      with no_infomsgs

What the following code does is utilize the sys.xp_readerrorlog extended stored procedure to retrieve the result of the DBCC CHECKDB command.
      -- read the error log and show the DBCC CHECKDB result
            @dbcc_checkdb_logsearch = 'dbcc checkdb (' + @database_name + ')'
      create table #errorlog
            LogDate datetime null,
            ProcessInfo nvarchar(128) null,
            Text nvarchar(2000) null
      insert into #errorlog
      exec master.sys.xp_readerrorlog 0, 1, @dbcc_checkdb_logsearch

Display the DBCC CHECKDB result to the end-user, and then call a DROP DATABASE on our restored database.
      select top 1 Text
      from #errorlog
      order by LogDate desc
      -- drop the test restored database
      exec('drop database ' + @database_name)

That is the bulk of the code and the logic that was used when writing the T-SQL.  As for Usage, here are a few examples:

-- test the backupset that is at position 4
exec dbo.sp_backup_restore_test
      @backup_filename = 'C:\DatabaseFiles\BackupAndRestoreTest.bak',
      @restore_temp_path = 'C:\DatabaseFiles',
      @backup_set_file_number = 4

-- test the backupset at the first position
exec dbo.sp_backup_restore_test
      @backup_filename = 'C:\DatabaseFiles\BackupAndRestoreTest.bak',
      @restore_temp_path = 'C:\DatabaseFiles\'

                The code for this blog post can be found on my GitHub repository at the following location:

                Test your backups!  If there are any questions, comments, or issues please feel free to leave a comment below or email me at