Streamlining the Database Server Recovery Process

By Gregory Larsen, 2002/11/05

Streamlining the Recovery Process

Streamlining the Database Server Recovery Process

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 SP’s is to minimize the time and errors associated with rebuilding our database servers.

Overview of Stored Procedures

The two SP’s 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 SP’s 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 SP’s

The code for each of these SP’s 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 SP’s can be broken up into sections and subsection. For each section or subsection mentioned below refer to the SP’s, 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 SP’s. 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 SP’s are executed

Each SP’s 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 SP’s 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

restore database DBA

from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021006_2000_diff.bak'

with replace, norecovery

restore log DBA

from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021007_1000_tran.bak'

with replace, norecovery

restore log DBA

from disk = 'C:\mssql\MSSQL\BACKUP\DBA_20021007_1200_tran.bak'

with replace

restore database master

from disk = 'C:\mssql\MSSQL\BACKUP\master_20021006_2000_full.bak'

with replace

restore database model

from disk = 'C:\mssql\MSSQL\BACKUP\model_20021006_2000_full.bak'

with replace

restore database msdb

from disk = 'C:\mssql\MSSQL\BACKUP\msdb_20021006_2000_full.bak'

with replace

restore database Northwind

from disk = 'C:\mssql\MSSQL\BACKUP\Northwind_20021006_2000_full.bak'

with replace

restore database pubs

from disk = 'C:\mssql\MSSQL\BACKUP\pub_20021006_2000_full.bak'

with replace

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

SET ANSI_NULLS ON

create procedure usp_build_restore_script

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

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

Code for usp_build_list_of_files

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

CREATE procedure usp_what_files_to_restore

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

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

Total article views: 8946 | Views in the last 30 days: 4

SCRIPT

Streamlining the Database Server Recovery Process

Streamlining the Database Server Recovery Process

Overview of Stored Procedures

Why We Built These Stored Procedures

Details of the SP’s

How these SP’s are executed

Restore Script

Files to Restore

Conclusion

Code for usp_build_restore_script

Code for usp_build_list_of_files

Backup & Restoration Script

Backup/restore

Restore with no backup

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

Backup / restore issue

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

Streamlining the Database Server Recovery Process

Streamlining the Database Server Recovery Process

Overview of Stored Procedures

Why We Built These Stored Procedures

Details of the SP’s

How these SP’s are executed

Restore Script

Files to Restore

Conclusion

Code for usp_build_restore_script

Code for usp_build_list_of_files

Backup & Restoration Script

Backup/restore

Restore with no backup

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

Backup / restore issue

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

Already a member? Jump in: