Streamlining the Database Server Recovery Process on SQL Server

April 3, 2003

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 while trying to perform a restore of
all databases on a server.

Most DBAs develop procedures
on how to recover 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 takes
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 were going to practice 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 latest
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 subsections. 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 the 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 they 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 that is processed. 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. In addition, 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 SP's execution
basically only produces a report. The reports generated need to be directed
to physical files. This is accomplished by executing these SPs using the "osql"
command. Here are the commands I use for redirecting the output from each of
these SPs to a file:

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

Conclusion

I hope that 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 the
one I have suggested, will not only save you time, but will reduce your anxiety
level, during an already stressful disaster recovery situation.