Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 3, 2003

Streamlining the Database Server Recovery Process on SQL Server

By Gregory A. 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 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:

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

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


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.

Code for usp_build_restore_script

Code for usp_build_list_of_files

» See All Articles by Columnist Gregory A. Larsen

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.