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 sqlsalt@gmail.comwith 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_numberis 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
as
Construct the dynamic SQL commands to gather the necessary information from the backup device.
      select
            @exec_cmd_filelistonly = ‘restore filelistonly from disk = ”’ +
                  @backup_filename + ”’ with file = ‘ +
                  cast(@backup_set_file_number asnvarchar),
            @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
      (
            LogicalName,
            PhysicalName,
            — … abbreviated for simplicity
      )
      exec(@exec_cmd_filelistonly)
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
      (
            BackupName,
            BackupDescription,
            — … abbreviated for simplicity
      )
      exec(@exec_cmd_headeronly)
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_fixeddrivesExtended Stored Procedure.
      create table#fixed_drives
      (
            drive_name char(3) not null,
            free_space_mb int not null
      )
      insert into#fixed_drives
      execmaster.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).
      select
            @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
            begin
                  raiserror(‘Unrecognized temp path drive’, 16, 1)
                  return 1
            end
     
      — make sure there is enough space on the drive with a 1 GB buffer
      if
            @drive_space_available_mb < 
            @drive_space_required_mb +@drive_space_tempbuffer_mb
            begin
                  raiserror(‘Insufficient temp drive space’, 16,1)
                  return 1
            end
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 asnvarchar) + ‘, ‘
     
      — 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
      select
            ‘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
      begin
            select @restore_cmd += @move_file
           
            fetch next
            from file_iterate
            into @move_file
      end
     
      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
      exec(@restore_cmd)     
      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
      select
            @dbcc_checkdb_logsearch =‘dbcc checkdb (‘ +@database_name + ‘)’
           
      create table#errorlog
      (
            LogDate datetime null,
            ProcessInfo nvarchar(128) null,
            Text nvarchar(2000) null
      )
     
      insert into#errorlog
      (
            LogDate,
            ProcessInfo,
            Text
      )
      exec master.sys.xp_readerrorlog 0, 1, @dbcc_checkdb_logsearch
Display the DBCC CHECKDBresult to the end-user, and then call a DROP DATABASE on our restored database.
      select top1 Text
      from #errorlog
      order byLogDate 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
go
— test the backupset at the first position
exec dbo.sp_backup_restore_test
      @backup_filename = ‘C:\DatabaseFiles\BackupAndRestoreTest.bak’,
      @restore_temp_path = ‘C:\DatabaseFiles\’
go
Code
                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 sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s