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.com 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 3^rd 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

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 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

      (

            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_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).

      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 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

      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 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

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:

BackupRestore_TestBackupDatabaseFromBackupFile.sql

                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.