Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Automate a Database Restore


By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | More > Restore

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



Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, February 19, 2015 - 4:36:45 PM - ankit Back To Top

HI Chad,

  I was going through the script and I was trying to do restore on below scenario so if you can help me how this script can work this scenario.

  I am taking backup thru maintenance plan. so MP assigns data and time stamp on backup file. E.g 'DBA_Support_backup_2015_02_19_143630_3528832' so i have to automate restore with this type of backup file naming how this script can work with? 

currently I am testing from local drive but actual prod to dev restore i will be using UNC path to define backup file location. 

Thanks


Wednesday, September 12, 2012 - 1:16:51 PM - David Nguyen Back To Top

REM I GOT THIS ERROR WHEN EXECUTING THIS VERSION. PLEASE ADVISE.

Msg 8114, Level 16, State 1, Line 1
Error converting data type bigint to int.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 8114, Level 16, State 1, Line 1
Error converting data type bigint to int.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
textdata
---------


Tuesday, March 08, 2011 - 2:20:27 AM - madan agrawal Back To Top

Use below code for sql server 2008 and above

 

 

 

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[sp_backup_restoredb]    Script Date: 03/08/2011 12:46:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER procedure [dbo].[sp_backup_restoredb]

@dbname varchar(150) = null, -- DB to be backed up

@restorepaths varchar(max) = '', -- Path(s) to the files containing backups to be restored from, semi-colon delimited, if any...if not

-- passed, we try to grab information from MSDB instead

@moveLogsTo varchar(max) = '', -- Path to location that log files for the database being restored should be moved to...

@moveDataTo varchar(max) = '', -- 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 varchar(max) = '', -- 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. The format as of me writing this is as follows:

/*

<file_or_filegroup_or_pages> ::=

  FILE = { logical_file_name_in_backup }

  | FILEGROUP = { logical_filegroup_name } }

 | PAGE = 'fileNumber:page [ ,...n ]'  

}

*/

@newDbName varchar(150) = null, -- Name of the restored database - if left default/null, the @dbname is used...

@filePattern varchar(150) = '*', -- 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 datetime = null, -- Date/time to stop at within the restore, if specified...

@opts int = 0 -- Options that drive execution for the proc

-- 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'll forcefully drop existing connections to the db in order to allow restore prior to restoring...

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

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

 

as

set nocount on;

set transaction isolation level read uncommitted;

 

 

 

-- Cleanup as necessary...

if object_id('tempdb..#dbrestore') > 0

drop table #dbrestore;

if object_id('tempdb..#files') > 0

drop table #files;

 

-- Table that creates the sql data for the actual restore operation

create table #dbrestore (id int, textdata varchar(max));

-- List of all the files that are potentially included in the restore

create table #files (pkid int identity(1,1), pathAndFileName varchar(max), filname varchar(max), backupEndDate datetime, 

fileNumber int, sortval varchar(150), groupval varchar(150), backupType smallint);

 

-- Index...

create unique clustered index ixc__#files__pkid__dbRestoreProc on #files (pkid);

create nonclustered index ixc__#files__sortVal_backupType__dbRestoreProc on #files (sortval,backupType);

create nonclustered index ixc__#files__groupval__dbRestoreProc on #files (groupval);

 

-- List of files captured from a 'restore filelistonly' operation

declare @filelist table (LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FileGroupName nvarchar(128), 

Size numeric(20,0), MaxSize numeric(20,0), FileId bigint, CreateLSN numeric(25,0), DropLSN numeric(25,0), UniqueId uniqueidentifier,

ReadOnlyLSN numeric(25,0), ReadWriteLSN numeric(25,0), BackupSizeInBytes bigint,

SourceBlockSize int, FileGroupId int, LogGroupGUID uniqueidentifier, DifferentialBaseLSN numeric(25,0), DifferentialBaseGUID uniqueidentifier,

IsReadOnly bit, IsPresent bit,TDEThumbprint varbinary(32));

declare @filelist_ls table (LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FileGroupName nvarchar(128), 

Size numeric(20,0), MaxSize numeric(20,0), FileId bigint, CreateLSN numeric(25,0), DropLSN numeric(25,0), UniqueId uniqueidentifier,

ReadOnlyLSN numeric(25,0), ReadWriteLSN numeric(25,0), BackupSizeInBytes bigint,

SourceBlockSize int, FileGroupId int, LogGroupGUID uniqueidentifier, DifferentialBaseLSN numeric(25,0), DifferentialBaseGUID uniqueidentifier,

IsReadOnly bit, IsPresent bit,TDEThumbprint varbinary(32));

 

-- List of header data captured from a 'restore headeronly' operation

declare @headerdata table (BackupName nvarchar(128), BackupDescription nvarchar(255), BackupType smallint, ExpirationDate datetime,

Compressed tinyint, Position smallint, DeviceType tinyint, UserName nvarchar(128), ServerName nvarchar(128), DatabaseName nvarchar(128),

DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(20,0), FirstLSN numeric(25,0), LastLSN numeric(25,0),

CheckpointLSN numeric(25,0), DatabaseBackupLSN numeric(25,0), BackupStartDate datetime, BackupFinishDate datetime, 

SortOrder smallint, CodePage smallint, UnicodeLocaleId int, UnicodeComparisonStyle int, CompatabilityLevel tinyint,

SoftwareVendorId int, SoftwareVersionMajor int, SoftwareVersionMinor int, SoftwareVersionBuild int, MachineName nvarchar(128),

Flags int, BindingID uniqueidentifier, RecoveryForkID uniqueidentifier, Collation nvarchar(128), FamilyGUID uniqueidentifier,

HasBulkLoggedData bit, IsSnapshot bit, IsReadOnly bit, IsSingleUser bit, HasBackupChecksums bit, IsDamaged bit, BeginsLogChain bit,

HasIncompleteMetaData bit, IsForceOffline bit, IsCopyOnly bit, FirstRecoveryForkID uniqueidentifier, ForkPointLSN numeric(25,0),

RecoveryModel nvarchar(60), DifferentialBaseLSN numeric(25,0), DifferentialBaseGUID uniqueidentifier, BackupTypeDescription nvarchar(60),

BackupSetGUID uniqueidentifier,CompressedBackupSize int);

-- Temporary holding location for parsed input data

declare @tempdata table (pkid int identity(1,1), textdata varchar(max));

-- List of locations to move data/logs to

declare @newDataLocations table (pkid int identity(1,1), textdata varchar(max));

 

-- Local var init...

declare @workingpaths varchar(max),

@sql nvarchar(max),

@dir varchar(max),

@cmd varchar(8000),

@i int,

@n int,

@l int,

@minId int,

@latestFullSortVal varchar(150),

@groupVal varchar(150),

@sortVal varchar(150),

@fileName varchar(max),

@pathAndName varchar(max),

@moveLogSql varchar(max),

@moveDataSql varchar(max),

@stopAtChar char(23);

 

-- Format incoming data

select @dbname = ltrim(rtrim(@dbname)),

@restorepaths = isnull(ltrim(rtrim(@restorepaths)),''),

@opts = isnull(@opts,0),

@sql = N'',

@moveLogSql = '',

@moveDataSql = '',

@stopAt = case when @stopAt > 0 then @stopAt else null end,

@fileFilGroupPageString = case when len(@fileFilGroupPageString) > 0 then @fileFilGroupPageString when @opts & 64 = 64 then '' else '' end;

-- Format additional data...

select @newDbName = case when len(@newDbName) > 0 then ltrim(rtrim(@newDbName)) else @dbname end,

@stopAtChar = case when @stopAt > 0 then convert(char(25), @stopAt, 121) else null end,

@filePattern = case when len(@restorepaths) > 0 then ltrim(rtrim(@filePattern)) else null end;

 

-- Ensure we have a trailer

if len(@moveLogsTo) > 0

select @moveLogsTo = @moveLogsTo + case when right(@moveLogsTo,1) = '\' then '' else '\' end;

 

-- Get page restore data if needed...use a mask of 68 since we can't use this with a LiteSpeed restore currently...

if @opts & 68 = 64 begin

select @fileFilGroupPageString = @fileFilGroupPageString + 

case when len(@fileFilGroupPageString) > 0 then ',' else '' end + 

cast(file_id as varchar(10)) + ':' + cast(page_id as varchar(25))

from msdb.dbo.suspect_pages p

where p.database_id = db_id(@dbname)

and p.event_type not in(4,5,7)

 

select @fileFilGroupPageString = 'PAGE=''' + @fileFilGroupPageString + '''';

 

end

 

-- Init data

select @workingpaths = @restorepaths;

 

-- Get the data needed for building the restore strings

if len(@restorepaths) > 0 begin

-- Parse the specified restore path list into a table set

while charindex(';', @workingpaths) > 0 begin

insert @tempdata (textdata)

select rtrim(ltrim(substring(@workingpaths, 1, charindex(';', @workingpaths) - 1)))

 

-- Trim the list down

select @workingpaths = substring(@workingpaths, charindex(';', @workingpaths) + 1, len(@workingpaths))

end -- while charindex(';', @workingpaths)

 

-- Get the last DB in there if needed

if len(@workingpaths) > 0

insert @tempdata (textdata)

select rtrim(ltrim(@workingpaths))

 

-- Ensure we have a path delimiter...

update @tempdata

set textdata = textdata + '\'

where right(textdata,1) <> '\';

 

-- No path(s) specified, build from msdb if possible

end else begin

select @n = null, @i = null;

select @sql = N'select top 1 @i = backup_set_id

from msdb.dbo.backupset

where database_name = @dbname

and type = ''D''

and is_snapshot = 0

and is_copy_only = 0

and backup_finish_date is not null

and ((backup_start_date <= @stopAt)

or (@stopAt is null))

order by backup_finish_date desc, backup_set_id desc;

 

select top 1 @n = backup_set_id

from msdb.dbo.backupset

where database_name = @dbname

and type = ''I''

and is_snapshot = 0

and is_copy_only = 0

and backup_finish_date is not null

and backup_set_id > @i

order by backup_finish_date desc, backup_set_id desc;

 

if ((@opts & 16 = 16) or (coalesce(@n,-1) < @i))

select @n = @i;';

exec sp_executesql @sql, N'@dbname varchar(250), @opts int, @i int output, @n int output, @stopAt datetime', @dbname, @opts, @i output, @n output, @stopAt;

select @sql = N'

with backupPathList (backupId, position, singleDevice, backupType, fileNumber, backupEndDate) as (

select b.backup_set_id, f.family_sequence_number as position,

coalesce(f.physical_device_name,f.logical_device_name) as singleDevice,

b.type as backupType, b.position as fileNumber, backup_finish_date as backupEndDate

from msdb.dbo.backupset b with(nolock)

join msdb.dbo.backupmediafamily f with(nolock)

on b.media_set_id = f.media_set_id

where b.database_name = @dbname

and b.is_snapshot = 0

and b.is_copy_only = 0

and b.backup_finish_date is not null

)

insert #files (pathAndFileName, filname, fileNumber, sortval, groupval, backupEndDate)

select singleDevice, 

right(singleDevice, charindex(''\'',reverse(singleDevice),1) - 1), 

fileNumber,

right(''000000000000'' + cast(backupId as varchar(10)), 10), 

cast(backupId as varchar(10)), backupEndDate

from backupPathList

where ((backupId = @i)

or (backupId >= @n))

' + case when @opts & 16 = 16 then 'and backupType <> ''I'' ' else '' end + '

order by backupId;';

exec sp_executesql @sql, N'@dbname varchar(250), @i int, @n int', @dbname, @i, @n;

 

end -- else, if len(@restorepaths) > 0

 

-- Ensure we have something(s) to restore from

if ((select count(*) from @tempdata) = 0) and ((select count(*) from #files) = 0) begin

raiserror('No restore data was found for database [%s] and path(s) specified [%s]. Please correct and try again.', 16, 1, @dbname,@restorepaths)

goto finished

end

 

-- Get the move to data locations if needed...

if len(@moveDataTo) > 0 begin

-- Parse the specified restore path list into a table set

while charindex(';', @moveDataTo) > 0 begin

insert @newDataLocations (textdata)

select rtrim(ltrim(substring(@moveDataTo, 1, charindex(';', @moveDataTo) - 1)))

 

-- Trim the list down

select @moveDataTo = substring(@moveDataTo, charindex(';', @moveDataTo) + 1, len(@moveDataTo))

end -- while charindex(';', @workingpaths)

 

-- Get the last location if needed...

if len(@moveDataTo) > 0

insert @newDataLocations (textdata)

select rtrim(ltrim(@moveDataTo));

 

-- Ensure we have  a path delimiter...

update @newDataLocations

set textdata = textdata + '\'

where right(textdata,1) <> '\';

end

 

-- If we are restoring from a filelist and not the system tables, figure out the sort order for these suckers...

if len(@restorepaths) > 0 begin

select @i = 1, @n = max(pkid)

from @tempdata;

 

-- Get all the files from the directories in question...

while @i <= @n begin

select @dir = textdata

from @tempdata

where pkid = @i;

select @cmd = 'dir /B /A-D "' + @dir + case when len(@filePattern) > 0 then @filePattern else '' end + '"';

insert #files (filname)

exec xp_cmdshell @cmd;

update #files

set pathAndFileName = @dir + filname

where pathAndFileName is null;

select @i = @i+1;

end

 

-- Cleanup the list...

delete #files

where (filname is null

or lower(filname) like('%file not found%')

or lower(filname) like('%cannot find the%'));

 

-- Remove diffs (if possible) if we're supposed to try to do so and we are using a date/time grouping (otherwise we pull from the restore header)

if @opts & 144 = 144

delete #files

where ((filname like ('%[_-~!$.]diff[_-~!$.]%'))

or (filname like('%.dif'))

or (filname like('diff[_-~!$.]%')));

 

-- Ensure we have something(s) to restore from

if not exists(select * from #files) begin

raiserror('No restore data was found for database [%s] and path(s) specified [%s]. Please correct and try again.', 16, 1, @dbname,@restorepaths)

goto finished

end

 

select @minId = min(pkid), @i = null from #files;

 

-- if we got a file...

if @minId > 0 begin

 

-- If the caller flagged for us to try and use a date/time stamp within the files as the grouping/sorting value, do

-- so - otherwise we drop into each file to determine the grouping and sorting...

if @opts & 128 = 128 begin

-- Now, with the files, find a spot in the filename that is the date/time stamp...we basically will simply look for

-- a spot with at least 14 concurrent numeric values, preceeded by an underscore, and followed by more digits and a dot and extension...

select @i = patindex('%[_-~!$.][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%[.]%', filname) + 1, @l = len(filname)

from #files

where pkid = @minId;

 

if @i > 1 begin

select @n = charindex('.',filname,@i)

from #files

where pkid = @minId;

if @n = 0

select @n = charindex('_',filname,@i)

from #files

where pkid = @minId;

 

if @n = 0

select @n = charindex('-',filname,@i)

from #files

where pkid = @minId;

select @n = case when @n > 0 then (@[email protected]) else len(filname) end

from #files

where pkid = @minId;

 

update #files

set groupval = substring(filname,(@i + (len(filname) - @l)),@n),

sortval = substring(filname,(@i + (len(filname) - @l)),@n) + filname;

 

end else begin

print 'No DATETIME stamp value could be found in the file names - no special sorting will be applied';

end

 

-- Get the latest full backup sort value...

select @latestFullSortVal = max(sortval)

from #files

where ((filname like ('%[_-~!$.]full[_-~!$.]%'))

or (filname like('%.ful'))

or (filname like('full[_-~!$.]%')));

 

end else begin -- if @opts & 128 = 128...not using a date/time stamp to specify grouping, drop into each file

select @i = min(pkid), @n = max(pkid) from #files;

 

-- Go through each file using the MediaSetID value as the group...

while @i <= @n begin

select @pathAndName = pathAndFileName, @fileName = filname

from #files

where pkid = @i;

 

if @@rowcount > 0 begin

-- Get the header data...

delete @headerdata;

select @sql = case 

when @opts & 4 = 4 then 'exec master.dbo.xp_restore_headeronly @filename = ''' + @pathAndName + ''';'

else 'restore headeronly from disk = ''' + @pathAndName + ''''

end

-- LiteSpeed insert differs slightly...

if @opts & 4 = 4 begin

begin try

insert @headerdata (UnicodeComparisonStyle, Collation, BackupSetGUID, BackupName, BackupDescription, BackupType, ExpirationDate, Compressed, Position, DeviceType, UserName,

ServerName, DatabaseName, DatabaseVersion, DatabaseCreationDate, BackupSize, FirstLSN, DatabaseBackupLSN, CheckpointLSN,

DifferentialBaseLSN, BackupStartDate, BackupFinishDate, SortOrder, CodePage, CompatabilityLevel, SoftwareVendorId,

SoftwareVersionMajor, SoftwareVersionMinor, SoftwareVersionBuild, MachineName, BindingID, RecoveryForkID, Flags)

exec (@sql);

end try

begin catch

-- Could be a native restore using liteSpeed...try normal...

select @sql = 'restore headeronly from disk = ''' + @pathAndName + ''''

insert @headerdata

exec (@sql);

end catch

 

end else begin -- no LiteSpeed...

insert @headerdata

exec (@sql);

end

 

-- First delete the existing record...

delete #files where pkid = @i;

 

-- Insert all the files from this file...

insert #files (pathAndFileName, filname, fileNumber, sortval, groupval, backupType, backupEndDate)

select @pathAndName, @fileName, Position, 

cast(DatabaseBackupLSN as varchar(50)) + '_' + cast(replace(replace(replace(replace(convert(varchar(50), BackupFinishDate, 121),'-',''),':',''),'.',''),' ','') as char(17)) + '_' + cast(Position as varchar(15)),

cast(BackupSetGUID as char(36)),

BackupType, BackupFinishDate

from @headerdata

where DatabaseName = @dbname

and ((@opts & 16 = 0) -- Either we are using diffs, or...

or (@opts & 16 = 16 and BackupType not in(5,6,8))); -- we aren't and this isn't a diff...

 

end -- if @@rowcount > 0 begin

 

select @i = @i + 1;

end -- while @i <= @n begin

 

-- Get the latest full backup sort value...

select @latestFullSortVal = max(sortval)

from #files

where backupType = 1;

 

end -- if @opts & 128 = 128

 

end -- if @minId > 0

 

-- Remove everything before the most recent full backup...

delete #files

where sortval < @latestFullSortVal;

 

-- If we didn't group by...

update #files

set groupval = cast(pkid as varchar(50))

where groupval is null;

 

-- If we are using diffs, try to find the latest diff backup (if we can) and remove any tlog backups

if @opts & 16 = 0 begin

-- Find the latest diff backup, and delete any diff/log backups prior to that set...

if exists(select * from #files where backupType > 0) begin

select top 1 @groupVal = groupval, @sortVal = sortval

from #files

where backupType = 5

order by sortval desc;

 

delete #files

where sortval < @sortVal

and groupval <> @groupVal

and backupType in(5,2);

 

end else begin

select top 1 @groupVal = groupval, @sortVal = sortval

from #files

where ((filname like ('%[_-~!$.]diff[_-~!$.]%'))

or (filname like('%.dif'))

or (filname like('diff[_-~!$.]%')))

order by sortval desc;

 

delete #files

where sortval < @sortVal

and groupval <> @groupVal

and ((filname like ('%[_-~!$.]diff[_-~!$.]%'))

or (filname like('%.dif'))

or (filname like('diff[_-~!$.]%'))

or (filname like('%[_-~!$.]log[_-~!$.]%'))

or (filname like('log[_-~!$.]%'))

or (filname like('%.trn'))

);

end

 

end -- if @opts & 16 = 0

 

end -- if len(@restorepaths) > 0 

 

-- If we didn't group successfully...

update #files

set groupval = cast(pkid as varchar(50))

where groupval is null;

 

-- If we need to move log or data, build list to do so now...

if (len(@moveLogsTo) > 0) or ((select count(*) from @newDataLocations) > 0) begin

select @minId = min(pkid) from #files;

select @sql = case 

when @opts & 4 = 4 then 'exec master.dbo.xp_restore_filelistonly @filename = ''' + pathAndFileName + ''';'

else 'restore filelistonly from disk = ''' + pathAndFileName + ''''

end

from #files

where pkid = @minId;

 

if @opts & 4 = 4 begin

-- Using LiteSpeed

insert @filelist (LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize)

exec (@sql);

 

update f

set FileId = rn

from @filelist f

join (select row_number() over (order by LogicalName) as rn, LogicalName as lName from @filelist f2) f3

on f.LogicalName = f3.lName;

end else begin

-- not using LiteSpeed

insert @filelist

exec (@sql);

end

-- Do we need to move logs?

if len(@moveLogsTo) > 0

select @moveLogSql = @moveLogSql + 

case when len(@moveLogSql) > 0 then ',' else '' end +  

case when @opts & 4 = 4 then ' @with = ''move "' else ' move ''' end + LogicalName + 

case when @opts & 4 = 4 then '"' else '''' end + ' to ' + 

case when @opts & 4 = 4 then '"' else '''' end + @moveLogsTo + @newDbName + 

case when (select count(*) from @filelist where Type = 'L') > 1 then cast(FileId as varchar(5)) else '' end +

'.ldf' +

case when @opts & 4 = 4 then '"''' else '''' end

from @filelist 

where Type = 'L';

-- Do we need to move data files?

if exists(select * from @newDataLocations) begin

-- If we have data locations, ensure we have as many data location records as we have files to move...

while (select count(*) from @filelist where Type <> 'L') > (select count(*) from @newDataLocations)

insert @newDataLocations (textdata)

select textdata

from @newDataLocations

order by pkid;

 

-- Build the move to string for data files

select @moveDataSql = @moveDataSql + 

case when len(@moveDataSql) > 0 then ', ' + char(13) + char(10) else '' end +  

case when @opts & 4 = 4 then '      @with = ''move "' else '      move ''' end + f.LogicalName + 

case when @opts & 4 = 4 then '"' else '''' end + ' to ' +

case when @opts & 4 = 4 then '"' else '''' end + l.textdata + 

case when id = 1 then @newDbName + '.mdf' else LogicalName + '.ndf' end +

case when @opts & 4 = 4 then '"''' else '''' end

from (

select row_number() over(order by FileId) as id, LogicalName, FileId

from @filelist

where Type <> 'L'

) f

join @newDataLocations l

on f.id = l.pkid;

 

end

 

end -- if (len(@moveLogsTo) > 0) or ((select count(*) from @newDataLocations) > 0)

 

-- Need to combine any grouped sets...

if exists(select groupval from #files group by groupval having count(*) > 1) begin

declare groupCursor cursor local fast_forward for

select min(pkid), groupval

from #files

group by groupval

having count(*) > 1;

open groupCursor;

while 1=1 begin

fetch next from groupCursor into @i, @groupVal;

if @@fetch_status <> 0

break;

select @sql = '';

-- Build the full from clause for this particular group...

select @sql = @sql + case when len(@sql) > 0 then ''', ' + char(13) + char(10) + '   ' +

case when @opts & 4 = 4 then '@filename = ''' else 'disk = ''' end else ''

end + pathAndFileName

from #files

where groupval = @groupVal

order by pkid;

-- Update this group's main string

update #files

set pathAndFileName = @sql

where pkid = @i;

-- Remove all others but the main for this group

delete #files

where groupval = @groupVal

and pkid <> @i;

end

close groupCursor;

deallocate groupCursor;

end -- if exists(select groupval from #files group by groupval having count(*) > 1)

 

-- If we should be disconnecting clients, build string to do so now...

if @opts & 8 = 8

insert #dbrestore (id, textdata)

select 1, 'if db_id(''' + @newDbName + ''') > 0 ' + char(13) + char(10) +

'   alter database ' + @newDbName + ' set read_only with rollback immediate;';

 

select @i = count(*)

from #dbrestore with(nolock);

 

-- Build the restore strings...

if @opts & 4 = 4

-- Using LiteSpeed...

insert #dbrestore (id, textdata)

select (a.rownum + (a.rownum - 1)),

'exec master.dbo.' +

case

when backupType = 2 then 'xp_restore_log '

when filname like('%[_-~!$.]log[_-~!$.]%') then 'xp_restore_log '

when filname like('log[_-~!$.]%') then 'xp_restore_log '

when filname like('%.trn') then 'xp_restore_log '

else 'xp_restore_database '

end + char(13) + char(10) +

'   @database = ''' + @newDbName + ''', ' + char(13) + char(10) +

'   @filename = ''' + pathAndFileName + ''',' + char(13) + char(10) + 

'   @logging = 0, ' + char(13) + char(10) + 

'   @filenumber = ' + cast(coalesce(fileNumber,1) as varchar(25)) + ', ' + char(13) + char(10) + 

'   @with = ''norecovery'', ' + char(13) + char(10) + 

'   @with = ''replace''' +

case when len(@stopAtChar) > 0 then ', ' + char(13) + char(10) + '   @with = ''stopat = ''''' + @stopAtChar + '''''''' else '' end +

case when len(@moveLogSql) > 0 then ', ' + char(13) + char(10) + '     ' + @moveLogSql else '' end +

case when len(@moveDataSql) > 0 then ', ' + char(13) + char(10) + @moveDataSql else '' end +

';'

from (

select (row_number() over (order by sortval,pkid) + @i) as rownum, fileNumber, filname, pathAndFileName, backupType 

from #files

) a;

else

-- Not using LiteSpeed...

insert #dbrestore (id, textdata)

select (a.rownum + (a.rownum - 1)),

'restore ' +

case 

when backupType = 2 then 'log '

when filname like('%[_-~!$.]log[_-~!$.]%') then 'log '

when filname like('log[_-~!$.]%') then 'log '

when filname like('%.trn') then 'log '

else 'database '

end + @newDbName + ' ' +

case -- Need to use the page/file setting when this is NOT a log backup...and we've specified a value...

when len(@fileFilGroupPageString) > 0 then 

case

when backupType = 2 then ''

when filname like('%[_-~!$.]log[_-~!$.]%') then ''

when filname like('log[_-~!$.]%') then ''

when filname like('%.trn') then ''

else char(13) + char(10) + '   ' + @fileFilGroupPageString + ' ' + char(13) + char(10) 

end

else '' 

end +

'from ' + char(13) + char(10) + 

'   disk = ''' + pathAndFileName + '''' + char(13) + char(10) + 

'   with file = ' + cast(coalesce(fileNumber,1) as varchar(25)) + ', norecovery, replace' +

case when @opts & 32 = 32 then ', checksum' else '' end +

case when len(@stopAtChar) > 0 then ', stopat = ''' + @stopAtChar + '''' else '' end + 

case when len(@moveLogSql) > 0 then ', ' + char(13) + char(10) + '     ' + @moveLogSql else '' end +

case when len(@moveDataSql) > 0 then ', ' + char(13) + char(10) + @moveDataSql else '' end +

';'

from (

select (row_number() over (order by sortval,pkid) + @i) as rownum, fileNumber, filname, pathAndFileName, backupType

from #files

) a;

 

-- Put in GO's between each statement if we aren't executing...

if @opts & 1 = 1

insert #dbrestore (id,textdata)

select id + 1, 'GO '

from #dbrestore;

 

select @i = max(id)

from #dbrestore;

 

-- Add recovery if appropriate

if @opts & 2 = 2 begin

insert #dbrestore (id, textdata)

select @i+1, 'restore database ' + @newDbName + ' with recovery;';

 

if @opts & 1 = 1

insert #dbrestore (id, textdata)

select @i+2, 'GO ';

end

-- Output results if desired...

if @opts & 1 = 1

select textdata from #dbrestore order by id;

 

-- Execute if appropriate...

if @opts & 1 = 0 begin

declare restoreSql cursor local fast_forward for

select textdata from #dbrestore order by id;

 

open restoreSql;

 

-- Process each restore...

while 1=1 begin

fetch next from restoreSql into @sql;

 

-- Break when all done...

if @@fetch_status <> 0

break;

 

-- If we are to trap/eat errors, execute within try/catch, otherwise

-- simply execute so errors are spit out to the client...

if @opts & 256 = 256 begin

-- Execute...

begin try

exec (@sql);

end try

begin catch

print '!!!!!!!!!!!!!!! ERROR START !!!!!!!!!!!!!!!';

print '   Message: ' + quotename(error_message());

print '   Number: ' + quotename(cast(error_number() as varchar(50)));

print '   Statement: [' + left(isnull(@sql,'<NULL>'),1000) + ']';

print '!!!!!!!!!!!!!!! ERROR STOP  !!!!!!!!!!!!!!!';

end catch

end else begin

exec (@sql);

end

 

end -- while 1=1 begin

 

end -- if @opts & 1 = 0 begin

 

finished:

 

-- Close cursor as needed...

if cursor_status('local', 'restoreSql') >= 0 begin

close restoreSql;

deallocate restoreSql;

end

if cursor_status('local', 'groupCursor') >= 0 begin

close groupCursor;

deallocate groupCursor;

end

 

-- Cleanup

if object_id('tempdb..#dbrestore') > 0

drop table #dbrestore;

if object_id('tempdb..#files') > 0

drop table #files;

 


Learn more about SQL Server tools