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