SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Streamlining the Database Server Recovery Process

By Gregory Larsen,

Are you tired of manually restoring each database on a new server when the original server has a melt down? Does the manual process seem slow, and prone to keystoke and mouse click errors? Would you like to have those restore scripts automatically built, so you only have to fire them off? Well this article will show you one possible method for speeding up and reducing errors will trying to perform a restore of all databases on a server.

Most DBAs develop procedures on how to recovery a server, should it fail. Some organizations are lucky enough to have the necessary hardware/software to support a standby server, and perform log shipping to minimize downtime. Not all organizations can financially support the standby server model. Our shop happens to be one of those, fund starved, organizations. Just like any other organization our goal is to recover any failed server as quick as possible, given the tools and hardware we have to work with. Therefore we have developed two stored procedures to help speed up our recovery time. One stored procedure (SP) identifies the database backup files that need to be restored from tape, while the other the SP builds a restore script. The goal of these two SPs is to minimize the time and errors associated with rebuilding our database servers.

Overview of Stored Procedures

The two SPs we have built to help with disaster recovery are usp_build_restore_script, and usp_what_files_to_restore. The "usp_build_restore_script" SP builds a restore script, which contains RESTORE DATABASE and RESTORE LOG commands for each database on a server, where as the "usp_what_files_to_restore" SP produces a list of backup files that need to be restored from tape, which are required by the restore script.

Each of these SPs take into account which backup files are associated with the current backup set. Meaning the set of backup files needed to restore each database from the last full backup. The restore script produced will recover all databases to the point of the last backup.

Why We Built These Stored Procedures

Like most shops we decided we where going to practices recovering one of our production servers, so we scheduled a disaster recovery test. During our first disaster recovery exercise we identified two things that could be improved. One was reducing the time it took restoring database backups from tape, and the other was reducing the time and manual effort associated with restoring all databases.  

Our first disaster recovery exercise restored the entire backup directory that contained two weeks worth of database backup files. Normally our database recovery would only require the most recent set of backups (one full, one differential, and/or a few transaction log backups). Every extra file that was unnecessarily restored from tape increased the total time it took to copy the database backups from tape, and therefore increased the downtime. Our goal was to minimize down time. To optimize the tape restore time, we developed the "usp_what_files_to_restore" SP to identify a specific set of database backup files associated with the lastest backup cycle (full, diff, and transaction logs) that needed to be recovered.

Secondly we noticed that restoring the databases one at a time manually using Enterprise Manager was a slow, and error prone process. This manual restore process could easily be automated to reduce the duration and errors associated with performing manual database restores. This lead us to develop "usp_build_restore_script" SP to produce a database restore script that would restore every database on a server. The generated database restore script would streamline the restore process by replacing the manual database restores, done via Enterprise Manager.

Details of the SPs

The code for each of these SPs can be found at the bottom of this article. I’m not going to review the code in detail, but will generally describe where the code gets the backup information, and how that backup information is used.

These SPs can be broken up into sections and subsection. For each section or subsection mentioned below refer to the SPs, at the end of this article, to find the code associated with referenced sections.    

There are two sections in each SP. Section 1 collects database backup file information, and section 2 uses the backup information to produce the desired database restore script or list of files to restore from tape.

"Section 1" is basically the same for both SPs. This section uses the information stored in the backupset, backupmediaset, and backupmediafamily system tables, in the "msdb" database, to gather the physical file names and backup types (full, differential, and log) for each database backup. This section does this in three different subsections, 1A, 1B, and 1C, where each subsection is processed in order for each database. "Subsection 1A" is responsible for determining physical file name for the last full backup. "Subsection 1B" finds the name of the last differential backup, and "Subsection 1C" determines the physical names of all the transaction log backups taken since the last differential backup. Each of these subsections place the physical backup names in a temporary table named ##backupnames. "Section 2" is responsible to produce that actual restore script or the report of physical backup files to restore from tape.

In "usp_build_restore_script" SP, section 2 processes through the temporary table ##backupnames one database at a time, in the order in which were taken (full, diff, then transaction logs). For each database a series of "PRINT" TSQL commands are executed to output the actual database "RESTORE" commands, with the full database restore being first, then the differential, and then any transaction logs being last. If a given database requires multiple "RESTORE" commands, then the "NORECOVERY" option is specified on all the "RESTORE’ commands except the very last one. This allows multiple backups to be restored, and to have recovery performed once after the last backup for each database is precessed.     Since the "master" database needs to be restored in single user mode, the restore script produced contains the commands to restore the "master" database, but they are commented out. This is so the restore script can be run, as is, to restore all databases, except the "master" database. Also this allows you to highlight and run the restore commands for the "master" database.  

Section 2 for "usp_what_files_to_restore" SP also processes through temporary table ##backupnames. For each physical database backup this SP produces a TSQL "PRINT" statement that outputs just the physical database backup name.

How these SPs are executed

Each SPs basically only produces a report. The reports generated needs be directed to physical files. This is accomplished by executing these SP using the "osql" command. Here are the commands I use for redirect the output from each of these SPs to a file:

osql -E -Smyserver -Q"dba.dbo.usp_build_restore_script" -o c:\mssql\restore\restore_all.sql -w200
 
osql -E -Smyserver -Q"dba.dbo.usp_what_files_to_restore" -o c:\mssql\restore\files_to_restore.txt -w200

I have added two steps, that execute these commands, in our SQL Server agent job that creates our database backups. In addition I have added a step to the same backup job to copy the restore script and restore file list to another server on a network. This makes sure we have a copy of the script and list of files to restore on another server, should our SQL Server box crash.

Here is a sample of the TSQL commands produced by "usp_build_restore_script" on a server that contained only a single user database.

Restore Script

-- Restore All databases
restore database DBA
 from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021005_2000_full.bak'
 with replace, norecovery
go
 
restore database DBA
 from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021006_2000_diff.bak'
 with replace, norecovery
go
 
restore log DBA
 from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021007_1000_tran.bak'
 with replace, norecovery
go
 
restore log DBA
 from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021007_1200_tran.bak'
 with replace
go
 
/*
restore database master
 from disk = 'C:\mssql\MSSQL\BACKUP\master_20021006_2000_full.bak'
 with replace
*/
go
 
restore database model
 from disk = 'C:\mssql\MSSQL\BACKUP\model_20021006_2000_full.bak'
 with replace
go
 
restore database msdb
 from disk = 'C:\mssql\MSSQL\BACKUP\msdb_20021006_2000_full.bak'
 with replace
go
 
restore database Northwind
 from disk = 'C:\mssql\MSSQL\BACKUP\Northwind_20021006_2000_full.bak'
 with replace
go
 
restore database pubs
 from disk = 'C:\mssql\MSSQL\BACKUP\pub_20021006_2000_full.bak'
 with replace
go

Here is also a copy of the files to restore from tape created by "usp_what_files to restore" from the same server.

Files to Restore

C:\mssql\MSSQL\BACKUP\DBA_20021005_2000_full.bak
C:\mssql\MSSQL\BACKUP\DBA_20021006_2000_diff.bak
C:\mssql\MSSQL\BACKUP\DBA_20021007_1000_tran.bak
C:\mssql\MSSQL\BACKUP\DBA_20021007_1200_tran.bak
C:\mssql\MSSQL\BACKUP\master_20021006_2000_full.bak
C:\mssql\MSSQL\BACKUP\model_20021006_2000_full.bak
C:\mssql\MSSQL\BACKUP\msdb_20021006_2000_full.bak
C:\mssql\MSSQL\BACKUP\Northwind_20021006_2000_full.bak
C:\mssql\MSSQL\BACKUP\pubs_20021006_2000_full.bak

Conclusion

Hopefully this article gave you an idea on how I streamlined our recovery process. If you are currently restoring your databases manually, then you should consider implementing procedures that will speed up and reduce possible errors during a database server recovery. Implementing an automated script building approach like I have suggested, will not only save you time, but will reduce your anxiety level, during an already stressful disaster recovery situation.

Code for usp_build_restore_script

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
 
 
create procedure usp_build_restore_script
as
--
-- This stored procedure was written by Greg Larsen for Washington State Department of Health.
-- Date: 12/16/2001
--
-- Description:
--  This stored procedure generates TSQL script that will restore all the databases
--  on the current SQL Server.  This stored procedure takes into account when the last
--  full and differential backups where taken, and how many transaction log backups
--  have been taken since the last database backup, based on the information in
--  the msdb database.
--
-- Modified:
--
--
-- Declare variables used in SP
declare @cmd nvarchar (1000)
declare @cmd1 nvarchar (1000)
declare @db nvarchar(128)
declare @filename nvarchar(128)
declare @cnt int
declare @num_processed int
declare @name nvarchar(128)
declare @physical_device_name nvarchar(128)
declare @backup_start_date datetime
declare @type char(1)
-- Turn off the row number message
set nocount on
 
-- SECTION 1 ----------------------------------------------
-- Define cursor to hold all the different databases for the restore script will be built
declare db cursor for
select name from master..sysdatabases
where name not in ('tempdb')
 
-- Create a global temporary table that will hold the name of the backup, the database name, and the type of database backup.
create table ##backupnames (
name nvarchar(100),
database_name nvarchar(100),
type char(1) )
 
-- Open cursor containing list of database names.
open db
fetch next from db into @db
 
-- Process until no more databases are left
WHILE @@FETCH_STATUS = 0
BEGIN
-- Subsection 1A --------------------------------------------
-- initialize the physical device name
 set @physical_device_name = ''
-- get the name of the last full database backup
 select @physical_device_name = physical_device_name , @backup_start_date = backup_start_date
 from  msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id
      join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
       where type='d' and backup_start_date =
        (select top 1 backup_start_date from msdb..backupset
             where @db = database_name and type = 'd'
              order by backup_start_date desc) 
-- Did a full database backup name get found
if @physical_device_name <> ''
begin
-- Build command to place a record in table that holds backup names
  select @cmd = 'insert into ##backupnames values (' + char(39) +
              @physical_device_name + char(39) + ',' + char(39) + @db + char(39) + ',' +
              char(39) + 'd' + char(39)+ ')'    
-- Execute command to place a record in table that holds backup names     
  exec sp_executesql @cmd
end
-- Subsection 1B --------------------------------------------
-- Reset the physical device name
set @physical_device_name = ''
-- Find the last differential database backup
 select @physical_device_name = physical_device_name, @backup_start_date = backup_start_date
 from  msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id
      join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
       where type='i' and backup_start_date =
        (select top 1 backup_start_date from msdb..backupset
             where @db = database_name and type = 'I' and backup_start_date > @backup_start_date
              order by backup_start_date desc)
-- Did a differential backup name get found
if @physical_device_name <> ''
begin
 
-- Build command to place a record in table that holds backup names
  select @cmd = 'insert into ##backupnames values (' + char(39) +
              @physical_device_name + char(39) + ',' + char(39) + @db + char(39) + ',' +
              char(39) + 'i' + char(39)+ ')'    
-- Execute command to place a record in table that holds backup names       
  exec sp_executesql @cmd
end
-- Subsection 1C --------------------------------------------
-- Build command to place records in table to hold backup names for all
-- transaction log backups from the last database backup
set @CMD = 'insert into ##backupnames select physical_device_name,' + char(39) + @db + char(39) +
 ',' + char(39) + 'l' + char(39) +  
 'from  msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id join ' +
 'msdb..backupmediafamily c on a.media_set_id = c.media_set_id ' + 
       'where type=' + char(39) + 'l' + char(39) + 'and backup_start_date >  @backup_start_dat and' +
 char(39) + @db + char(39) + ' = database_name'
-- Execute command to place records in table to hold backup names
--  for all transaction log backups from the last database backup
exec sp_executesql @cmd,@params=N'@backup_start_dat datetime', @backup_start_dat = @backup_start_date
-- get next database to process
fetch next from db into @db
end
-- close
close db
-- Section B ----------------------------------------------
open db
-- Get first recod from database list cursor
fetch next from db into @db
-- Generate Heading in Restore script
print '-- Restore All databases'
 
-- Process all databases
WHILE @@FETCH_STATUS = 0
BEGIN
-- define cursor for all database and log backups for specific database being processed
  declare backup_name cursor for
     select name,type from ##backupnames where database_name = @DB
-- Open cursor containing list of database backups for specific database being processed 
  open backup_name
-- Determine the number of different backups available for specific database being processed
  select @CNT = count(*) from ##backupnames where database_name = @DB
-- Get first database backup for specific database being processed
  fetch next from backup_name into @physical_device_name, @type
-- Set counter to track the number of backups processed
  set @NUM_PROCESSED = 0
-- Process until no more database backups exist for specific database being processed
  WHILE @@FETCH_STATUS = 0
  BEGIN
-- Increment the counter to track the number of backups processed
  set @NUM_PROCESSED = @NUM_PROCESSED + 1
-- Is the number of database backup processed the same as the number of different backups
-- available for specific database being processed?
  if @CNT = @NUM_PROCESSED
-- If so, is the type of backup currently being processed a transaction log backup?
    if @TYPE = 'l'
-- build restore command to restore the last transaction log
      select @cmd = 'restore log ' + rtrim(@db) + char(13) +
              ' from disk = ' + char(39) + 
               rtrim(substring(@physical_device_name,1,len(@physical_device_name))) +
                 char(39) + char(13) + ' with replace'
    else
-- Last backup was not a transaction log backup
-- Build restore command to restore the last database backup
      select @cmd = 'restore database ' + rtrim(@db) + char(13) +
            ' from disk = ' + char(39) + 
             rtrim(substring(@physical_device_name,1,len(@physical_device_name))) +
               char(39) + char(13) + ' with replace'
  else
-- Current backup is not the last backup
-- Is the current backup being processed a transaction log backup?
    if @TYPE = 'l'
-- Build restore command to restore the current transaction backup, with no recovery
      select @cmd = 'restore log ' + rtrim(@db) + char(13) +
              ' from disk = ' + char(39) + 
               rtrim(substring(@physical_device_name,1,len(@physical_device_name))) +
                 char(39) + char(13) + ' with replace, norecovery'
    else
-- Current backup being processed is not a transaction log backup
-- Build restore command to restore the currrent database backup, with no recovery
      select @cmd = 'restore database ' + rtrim(@db) + char(13) +
           ' from disk = ' + char(39) + 
            rtrim(substring(@physical_device_name,1,len(@physical_device_name))) +
              char(39) + char(13) + ' with replace, norecovery'
 
-- if it is master comment line out
   if @db = 'master'
      set @cmd = '/* ' + char(13) + @cmd + char(13) + '*/'
-- Generate the restore command and other commands for restore script
   print @cmd
   print 'go'
   print ' '
   
-- Get next database backup to process
  fetch next from backup_name into @physical_device_name, @type
end
-- Close and deallocate database backup name cursor for current database being processed
close backup_name
deallocate backup_name
-- Get next database to process
  fetch next from db into @db
end
-- Close and deallocate cursor containing list of databases to process
close db
deallocate db
-- Drop global temporary table
drop table ##backupnames
 
 
 
 
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Code for usp_build_list_of_files

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
 
CREATE   procedure usp_what_files_to_restore
as
--
--
-- This stored procedure was written by Greg Larsen for Washington State Department of Health.
-- Date: 02/28/2002
--
-- Description:
--  This stored procedure generates a list of files that need to be restored
--  should the server need to be rebuild.  The list only contains a list of database
--  backup files.
--
-- Declare variables used in SP
declare @cmd nvarchar (1000)
declare @cmd1 nvarchar (1000)
declare @db nvarchar(128)
declare @filename nvarchar(128)
declare @cnt int
declare @num_processed int
declare @name nvarchar(128)
declare @physical_device_name nvarchar(128)
declare @backup_start_date datetime
declare @type char(1)
-- Section A --------------------------------------------
-- Turn off the row number message
set nocount on
-- Define cursor to hold all the different databases for the restore script will be built
declare db cursor for
select name from master..sysdatabases
where name not in ('tempdb')
-- Create a global temporary table that will hold the name of the backup, the database name,
-- and the type of database backup.
create table ##backupnames (
name nvarchar(100),
database_name nvarchar(100),
type char(1) )
-- Open cursor containing list of database names.
open db
fetch next from db into @db
-- Process until no more databases are left
WHILE @@FETCH_STATUS = 0
BEGIN
-- Subsection 1A ----------------------------------------------
-- initialize the physical device name
 set @physical_device_name = ""
-- get the name of the last full database backup
 select @physical_device_name = physical_device_name , @backup_start_date = backup_start_date
 from  msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id
      join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
       where type='d' and backup_start_date =
        (select top 1 backup_start_date from msdb..backupset
             where @db = database_name and type = 'd'
              order by backup_start_date desc) 
-- Did a full database backup name get found
if @physical_device_name <> ""
begin
-- Build command to place a record in table that holds backup names
  select @cmd = 'insert into ##backupnames values (' + char(39) +
              @physical_device_name + char(39) + ',' + char(39) + @db + char(39) + "," +
              char(39) + 'd' + char(39)+ ')'    
-- Execute command to place a record in table that holds backup names     
  exec sp_executesql @cmd
end
-- Subsection 1B ----------------------------------------------
-- Reset the physical device name
set @physical_device_name = ""
-- Find the last differential database backup
 select @physical_device_name = physical_device_name, @backup_start_date = backup_start_date
 from  msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id
      join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
       where type='i' and backup_start_date =
        (select top 1 backup_start_date from msdb..backupset
             where @db = database_name and type = 'I' and backup_start_date > @backup_start_date
              order by backup_start_date desc)
-- Did a differential backup name get found
if @physical_device_name <> ""
begin
-- Build command to place a record in table that holds backup names
  select @cmd = 'insert into ##backupnames values (' + char(39) +
              @physical_device_name + char(39) + ',' + char(39) + @db + char(39) + "," +
              char(39) + 'i' + char(39)+ ')'    
-- Execute command to place a record in table that holds backup names       
  exec sp_executesql @cmd
end
-- Subsection 1B ----------------------------------------------
-- Build command to place records in table to hold backup names for all
-- transaction log backups from the last database backup
set @CMD = "insert into ##backupnames select physical_device_name," + char(39) + @db + char(39) +
           "," + char(39) + "l" + char(39) +  
 "from  msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id " +
 "join msdb..backupmediafamily c on a.media_set_id = c.media_set_id " + 
       "where type=" + char(39) + "l" + char(39) + "and backup_start_date >  @backup_start_dat and" +
char(39) + @db + char(39) + " = database_name"
-- Execute command to place records in table to hold backup names
-- for all transaction log backups from the last database backup
exec sp_executesql @cmd,@params=N'@backup_start_dat datetime', @backup_start_dat = @backup_start_date
-- get next database to process
fetch next from db into @db
end
-- close and deallocate database list cursor
close db
deallocate db
-- Section B -------------------------------------------------------
-- define cursor for all database and log backups
declare backup_name cursor for
   select name,type from ##backupnames
-- Open cursor containing list of database backups for specific database being processed 
open backup_name
-- Get first database backup for specific database being processed
fetch next from backup_name into @physical_device_name, @type
-- Set counter to track the number of backups processed
set @NUM_PROCESSED = 0
-- Process until no more database backups exist for specific database being processed
WHILE @@FETCH_STATUS = 0
BEGIN
-- print file name to restore
   print @physical_device_name
-- Get next database backup to process
   fetch next from backup_name into @physical_device_name, @type
end
-- Close and deallocate database backup name cursor for current database being processed
close backup_name
deallocate backup_name
 
-- Drop global temporary table
drop table ##backupnames
 
 
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
Total article views: 8966 | Views in the last 30 days: 1
 
Related Articles
SCRIPT

Backup & Restoration Script

This script provides very useful information about database backup and restoration.

FORUM

Backup/restore

Backup/restore

FORUM

Restore with no backup

Database dropped, no backup. Need to restore

ARTICLE

Webinar: Turn your backups into live databases with SQL Virtual Restore

Find out in this webinar how easy it is with Red Gate's SQL Virtual Restore to mount live, fully fun...

FORUM

Backup / restore issue

Backup / restore issue

 
Contribute