NOTE: See an updated version of this functionality using Powershell and supporting some additional functionality here. 

It's quite common to see automated/custom procedures for backing up a database/log - nearly everywhere I go companies have custom backup procedures and processes to handle backups, logging of backups, naming standards, locations, etc. However, it's rare that I find processes/procedures in place to automate the restore of database/log backups - so rare in fact that I can recall only a single time in the last 3 years that I've seen it. Kind of odd considering you typically want to ensure the recovery process goes quickly when needed - nothing underlines this more than an actual disaster, and when you're in the middle of one, the last thing you want to have to worry about is writing lines of code to build the restore statements for your recovery path, which could be made up of hundreds or thousands of backup sets.

Enter the sp_backup_restoredb procedure - I wrote this procedure to automate the most common restore paths in a variety of scenarios. With it you can automate the restore of a database using msdb backup history information for the given database - don't have the history information, or are restoring to another server? No worries, just point it at the directory location (or locations) that hold the backups using an optional pattern filter and let it rip. Need to restore just a single file, file group, or page(s)? No problem either. Want to rename the database during the restore? Ok, no problem. Need to move the locations of the log/data files for the database during the restore? Just tell it where you want them to go and it will do the rest (and you don't even have to know what files exist in the database, or where they previously existed, nor do you have to know even how many files there are within the database - it will simply round-robin the files among the locations you specify). Want to use LiteSpeed? Sure, we can do that.  Do you use a single mediaset for each backup, or do you use a single mediaset for a group of backups, or do you use a single mediaset forever and always? No problem, handle them all. Need to recovery to a specific point in time? Just specify the value. Want to recover the database? Leave it in recovery? Use a checksum (or not)? Silently ignore restore errors? Suppress execution and output just the restore statements? Check, check, check, check, and check.

There are lots of interesting uses for this type of procedure beyond just disaster recovery situations - can be leveraged to test recovery processes, to restore varying types of backups to a single reporting server, to validate what you have for backups, or for migration scenarios (and there are probably other cases as well).

Here are some samples executions:

-- Restore the testDb database, suppressing actual execution, using data from msdb, not performing

-- recovery, native restore, and the most efficient path

exec dbo.sp_backup_restoredb @dbname = 'testDb', @opts = 1;

-- Same thing, only instead of using data in MSDB, use the 2 specified locations for any .bak file

-- starting with 'testDb'

exec dbo.sp_backup_restoredb @dbname = 'testDb', @restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',

@filePattern = 'testDb*.bak', @opts = 1;

-- Same thing, only use LiteSpeed syntax...

exec dbo.sp_backup_restoredb @dbname = 'testDb', @restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;', @opts = 5;

-- How about changing the name on restore?

exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',

@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',

@opts = 5;

-- What about moving the log/data files around? Here we will place data files for the database in the

-- 4 specified locations (semi-colon delimited) - if there are less than 4 data files, they will simply

-- be placed in the locations in the order specified up to the number of data files there are (so, if

-- there were 2 data files, 1 would go to M:\SqlData and 1 to N:\SqlData). If there are more than 4

-- data files, they will continue to round-robin among the specified locations in order specified

-- until there are no more files (so, with 7 data files, you'd end up with 2 in M,N,O and 1 in P)

exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',

@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',

@moveLogsTo = 'l:\SqlLogs\',

@moveDataTo = 'm:\SqlData\;n:\SqlData\;o:\SqlData\;p:\SqlData',

@opts = 5;

-- Want to stop at a particular point?

exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',

@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',

@moveLogsTo = 'l:\SqlLogs\',

@moveDataTo = 'm:\SqlData\;n:\SqlData\;o:\SqlData\;p:\SqlData',

@stopAt = '2008-07-29 15:52:20.310',

@opts = 5;

-- Same thing, only ignore the use of an DIFFERENTIAL backups

exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',

@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',

@moveLogsTo = 'l:\SqlLogs\',

@moveDataTo = 'm:\SqlData\;n:\SqlData\;o:\SqlData\;p:\SqlData',

@stopAt = '2008-07-29 15:52:20.310',

@opts = 21;

-- Perform recovery at the end of the restore process...

exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',

@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',

@moveLogsTo = 'l:\SqlLogs\',

@moveDataTo = 'm:\SqlData\;n:\SqlData\;o:\SqlData\;p:\SqlData',

@stopAt = '2008-07-29 15:52:20.310',

@opts = 23;

-- Force existing users out of the new database prior to restoring...

exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',

@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',

@moveLogsTo = 'l:\SqlLogs\',

@moveDataTo = 'm:\SqlData\;n:\SqlData\;o:\SqlData\;p:\SqlData',

@stopAt = '2008-07-29 15:52:20.310',

@opts = 31;

-- Perform a PAGE level restore, getting the pages to be restored from the msdb

-- suspectpages database table...

exec dbo.sp_backup_restoredb @dbname = 'testDb', @opts = 65; 

The usage output looks like follows:

USAGE:

exec dbo.sp_backup_restoredb @dbname, @restorepaths, @moveLogsTo, @moveDataTo, @fileFilGroupPageString, @newDbName, @filePattern, @stopAt, @opts

PARAMETERS:

@dbname

DB to be restored

@restorepaths

Path(s) to the files containing backups to be restored from, semi-colon delimited...if not passed, we try to grab information from MSDB table instead

@moveLogsTo

Path to location that log files for the database being restored should be moved to...semi-colon delimited list...

@moveDataTo

Path(s) to location(s) that data files for the database being restored should be moved to...semi-colon delimited list...if more data files exist than paths are passed, data files are simply restored in a round-robin fashion to the locations specified...if more paths are specified here than there are data files, the first paths listed are used up to the # of data files, then the other paths are simply ignored...

@fileFilGroupPageString

Is a string of either a file, filegroup, or page string that will be used (if passed) as the <file_or_filegroup_or_pages> portion of the restore string - should match the proper format as outlined in BOL for this section exactly, since we basically just append here.

@newDbName

Name of the restored database - if left default/null, the @dbname is used...

@filePattern

Pattern of files to match for within the @restorepaths - by default, is everything (i.e. *) - only valid if a value is specified for @restorepaths

@stopAt

Date/time to stop at within the restore, if specified...

@opts

Options that drive execution for the proc. As follows: 
1 bit - If set, execution is suppressed and the strings are simply output...
2 bit - If set, recovery is performed at the end of all restores...by default, db is left in norecovery state...
4 bit - If set, LiteSpeed is used for recovery statements...
8 bit - If set, we will forcefully drop existing connections to the db in order to allow restore...
16 bit - If set, we will NOT use diff backups in the restore, only full and tlog backups...
32 bit - If set, CHECKSUM is used for the restore - this is only valid if a native restore is used...
64 bit - If set, PAGE level restore is used, and the pages to be restored are built from the data in the suspect_pages table in the MSDB. This cannot be used currently with LiteSpeed restores...
128 bit - If set, and a value is set in @restorepath, we will try to find a time/date stamp within the name of each file found in the @restorepath matching @filePattern - we will simply try to find 14 concurrent numbers within the name to signify as such...
256 bit - If set and the 1 bit is not set (i.e. we are executing), errors raised during the execution of the restore statements will be silently captured and reported without re-raising back to the calling code. Error number and message will be output as a print statement, but no error will be raised...

Enjoy!

Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.