Technical Article

Yet another restore script usp_restore

,

usp_restore - This will restore a backup performed with SQL LiteSpeed from DBAssocatesIT or by the standard backup database routines in SQL Server Please check below the declarations to find the user settable options for this script If the target database is currently in a loading status or cannot be read from this restore it will fail. It requires the ability to read from the sysfiles table to do the WITH MOVE clause in the backup statement.

Update to fix a couple of bugs.

--------------------------------------------------------------------------------------------------
--Restore This will restore a backup performed with SQL LiteSpeed from DBAssocatesIT or by--
--the standard backup database routines in SQL Server--
--Please check below the declarations to find the user settable options for this script--
--If the target database is currently in a loading status or cannot be read from this restore   --
--it will fail. It requires the ability to read from the sysfiles table to do the WITH MOVE--
--clause in the backup statement. --
--------------------------------------------------------------------------------------------------
/*
Restore This will restore a backup performed with SQL LiteSpeed from DBAssocatesIT or by the standard backup database routines in SQL Server Please check below the declarations to find the user settable options for this script If the target database is currently in a loading status or cannot be read from this restore it will fail. It requires the ability to read from the sysfiles table to do the WITH MOVE clause in the backup statement.

Options are:
@debug is a bit default 0 valid 0 or 1. 1 sets debug mode on and only prints statements does not run restore.
@shrinkdb is a bit default 1 valid 0 or 1. 1 sets dbcc shrinkdb off after restore 0 turns it on for restores into dev/QA.
@srcsvr is a varchar(255) default is null valid is string. This should be the name of the server the database is from.
@srcdb is a varchar(255) default is null valid is string. This should be the origional database name as it exist on source server.
@trgdb is a varchar(255) default is null valid is string. This should be the database that you want to restore to.
@resttm is a varchar(26) default is null valid is '1' or '12/21/2003 21:00:00.000' a date time string. set to '1' for current date time, or set to a date time string to restore to a point in time.
@standby is a bit default is 0 valid is 0 or 1. 0 sets it to restore fully 1 leaves it in standby mode able to restore additional logs.
@flnamepath is a varchar(300) default is null valid is string, this should be set to a unc like \\backupserver\bak\ or to a physical drive path d:\bak\ must contain trailing \ must hold backups you wish to restore.
@undodat is a varchar(255) default is null valid is string, this should be set to a unc like \\backupserver\hold\ or to a physical drive path d:\hold\ must contain trailing \ must hold backups you wish to restore.

This works with usp_backup and is part of an overall backup/restore/disaster recovery plan.
*/
drop procedure usp_restore
go
create procedure usp_restore

@debug bit = 0,
@shrinkdb bit = 1,
@srcsvr varchar(255) = '',
@srcdb varchar(255) = '',
@trgdb varchar(255) = '',
@resttm varchar(26) = '', 
@standby bit = 0,
@flnamepath varchar(300) = '',
@undodat varchar(255) = ''

as

set nocount on

--declared in the stored proc
declare @err int
declare @i int
declare @cnt int
declare @cnthld int
declare @cnthld2 int
declare @hlddt varchar(26)
declare @fldate datetime
declare @date  char(8)
declare @spid varchar(10)
declare @time varchar(6)
declare @sdbname varchar(500)
declare @flname varchar(255)
declare @cmd varchar(8000)
declare @full varchar(2000)
declare @diff varchar(2000)
declare @tran varchar(2000)
declare @lname varchar(255)
declare @fname varchar(500)
declare @hldresttm as varchar(30)
DECLARE @sql varchar(8000)
DECLARE @file varchar(255)
DECLARE @size varchar(15)
DECLARE @growth varchar(15)
DECLARE @name varchar(255)
DECLARE @group varchar(255)
DECLARE @prevgroup varchar(255)
DECLARE @defaultdata varchar(500)
DECLARE @defaultlog varchar (500)
DECLARE @errortext varchar(8000)
DECLARE @flnamemiss varchar(4000)
DECLARE @flmiss varchar(4000)
DECLARE @slsinst int
declare @stoprest bit

if @srcsvr is null or @srcsvr = ''
begin
set @stoprest = 1
end
if @srcdb is null or @srcdb = ''
begin
set @stoprest = 1
end
if @trgdb is null or @trgdb = ''
begin
set @stoprest = 1
end
if @resttm is null or @resttm = ''
begin
set @stoprest = 1
end
if @flnamepath is null or @flnamepath = ''
begin
set @stoprest = 1
end
if @undodat is null or @undodat = ''
begin
set @stoprest = 1
end

if @stoprest = 1
begin
print '--------------------------------------------------------------------------------------------------------------------------------------'
print 'Wrong syntax, please correct.'
print 'Sample: usp_restore 0,0,''MyServer'',''MyDB'',''MyTargetDB'',''07-22-2004 23:30:00.000'',0,''\\backuppath\bak\'',''\\standbypath\hold\'''
print 'Sample: usp_restore 0,0,''MyServer'',''MyDB'',''MyTargetDB'',''1'',0,''\\backuppath\bak\'',''\\standbypath\hold\'''
print 'Options are:'
print '@debug is a bit default 0 valid 0 or 1. 1 sets debug mode on and only prints statements does not run restore.'
print '@shrinkdb is a bit default 1 valid 0 or 1. 1 sets dbcc shrinkdb off after restore 0 turns it on for restores into dev/QA.'
print '@srcsvr is a varchar(255) default is null valid is string. This should be the name of the server the database is from.'
print '@srcdb is a varchar(255) default is null valid is string. This should be the origional database name as it exist on source server.'
print '@trgdb is a varchar(255) default is null valid is string. This should be the database that you want to restore to.' 
print '@resttm is a varchar(26) default is null valid is 1 or 12/21/2003 21:00:00.000 a date time string. set to 1 for current date time,'
print 'or set to a date time string to restore to a point in time.'
print '@standby is a bit default is 0 valid is 0 or 1. 0 sets it to restore fully 1 leaves it in standby mode able to restore additional logs.'
print '@flnamepath is a varchar(300) default is null valid is string, this should be set to a unc like \\bakfile01\bak\ or to a physical'
print 'drive path d:\bak\ must contain trailing \ must hold backups you wish to restore.'
print '@undodat is a varchar(255) default is null valid is string, this should be set to a unc like \\bakfile01\hold\ or to a physical'
print 'drive path d:\hold\ must contain trailing \ must hold backups you wish to restore.'
print ''
print 'Restore Terminated.'
print '--------------------------------------------------------------------------------------------------------------------------------------'
end
else
begin


set @flnamepath = @flnamepath+@srcsvr+'\'+@srcdb+'\'
--this is the path the backup files exist on you shouldn't have to modify this at all

set @undodat = cast(RAND( (DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()) )as varchar(20))
set @undodat = 'undo'+@trgdb+reverse(right(@undodat,len(@undodat) - 1))+'dat'
--this is the path to the undo file for the current restore. 




print '--------------------------------------------------------------------------------------------------------------------------'
print 'Please see bottom of output for any errors, additional statements, or corrective measures to take if this job fails.'
print 'Ignore all the following errors:'
print '''Server: Msg 3242, Level 16, State 1, Line 1'' these errors are checks to see if the backup was done with SQL LiteSpeed.'
print '''Server: Msg 62309, Level 19, State 1, Line 0'''
print '''RESTORE LOG is terminating abnormally.'''
print '''The log in this backup set terminates at LSN  which is too early to apply to the database. A more recent log backup '''
print 'This is fine its the ones that say ''To Late to apply'' Means you have a restore failure.' 
print '--------------------------------------------------------------------------------------------------------------------------'
set @stoprest = 0

--test to see if SQL LiteSpeed is installed
set @slsinst = 0
if exists (select * from master.dbo.sysobjects where id = object_id(N'[dbo].[xp_sqllitespeed_version]'))
begin
set @slsinst = 1
print '--------------------------------------------------------------------------------------------------------------------------'
print 'Error '+ cast(@slsinst as varchar(50)) +'! SQL LiteSpeed not installed on this server.'
print 'This restore will fail if you are trying to restore a SQL LiteSpeed commpressed backup to this server'
print '--------------------------------------------------------------------------------------------------------------------------'
end

--set variables used in this script

if ltrim(rtrim(@resttm)) = '1'
begin
set @resttm = getdate()
end

set @hlddt = getdate()
--this is used to check to se if we are recovering up to the minute

set @hldresttm = CONVERT (varchar(26), @resttm,121)
--have to convert it to use in recovery

set @i = 1
-- counter used for tlog restore later

create table  #direxist
(
fe bit, 
fd bit, 
pd bit
)

CREATE TABLE #detachdata (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[file] [varchar] (255) NOT NULL ,
[size] [varchar] (15) NOT NULL ,
[growth] [varchar] (15) NOT NULL ,
[name] [varchar] (255) NOT NULL ,
[group] [varchar] (255) NULL ,
[filetype] [char] (1) NOT NULL
) ON [PRIMARY]


create table #movelist
(
LogicalName varchar(255),
filename varchar(500)
)

--temp table to hold the file list for the WITH MOVE statement on the restore.

create table #verifyfilelistfull
(
LogicalName varchar(255),
PhysicalName varchar(255),
Type Varchar(2),
FileGroupName varchar(255),
Size bigint,
maxsize decimal(28,0)
)

--file list pulled from the full backup file

create table #verifyfilelistdiff
(
LogicalName varchar(255),
PhysicalName varchar(255),
Type Varchar(2),
FileGroupName varchar(255),
Size bigint,
maxsize decimal(28,0)
)

--file list pulled from the diff backup file

create table #sysfiles
(
fileid int,
groupid int,
size decimal(28,0),
maxsize decimal(28,0),
growth int,
status int,
perf int,
name varchar(500),
filename varchar(500)
)

--file list from the database that will recive the restore

create table #resfilelist
(
flname varchar(2000),
wdate datetime
)

--this holds all the backup file names required to do the restore most recent full most recent diff and all tlogs from the diff date forward

create table #file_list
(
fl_name varchar(500) null
)

-- holds directory listing

create table #file_detail
(
ident int IDENTITY(1,1),
alt_name varchar(255) null,
size decimal(28,0),
create_date char(8),
create_time char(6),
last_write_d char(8),
last_write_t char(6),
last_acc_d char(8),
last_acc_t char(6),
attrib smallint,
)

--holds all the attributes of the backup files last write time ect

create table #file_detail_full
(
ident int,
flname varchar (300),
alt_name varchar(255) null,
size decimal(28,0),
create_date char(8),
create_time char(6),
last_write_d char(8),
last_write_t char(6),
last_acc_d char(8),
last_acc_t char(6),
attrib smallint,
)

--table holds users to be killed
CREATE table #tmpUsers
(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30)
)

--test to see if the file path exist to grab restores from
insert into #direxist
exec master..xp_fileexist @flnamepath
if (select fd from #direxist) <> 1
begin
print '--------------------------------------------------------------------------------------------------------------------------'
print 'restore path does not exist check the @flnamepath variable and correct.'
print '--------------------------------------------------------------------------------------------------------------------------'
set @stoprest = 1
end
else
begin


SET @cmd='EXEC master..xp_cmdshell ''dir "'+@flnamepath+'"/b /s'''
--Insert output of the dir command into temp table

INSERT INTO #file_list EXEC (@cmd)
--holds all the attributes of the backup files last write time ect adds the filename back in

DECLARE db_cursor CURSOR FOR 
select fl_name from #file_list where fl_name is not null
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @flname
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #file_detail exec master..xp_getfiledetails @flname
insert into #file_detail_full select ident,@flname as flname,alt_name,size,create_date,create_time,last_write_d,last_write_t,last_acc_d,last_acc_t,attrib from #file_detail where ident = @i
FETCH NEXT FROM db_cursor INTO @flname
set @i = @i + 1
END
CLOSE db_cursor
DEALLOCATE db_cursor

--build file list to restore
insert into #resfilelist
select top 1 flname,max(cast((substring(substring(flname,(charindex('_full',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_full',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_full',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_full',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_full',flname,0)+6),12),11,2)+':00.000')as datetime))
from 
#file_detail_full
where
flname like '%[_]full[_]%'
and
@resttm >= cast((substring(substring(flname,(charindex('_full',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_full',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_full',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_full',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_full',flname,0)+6),12),11,2)+':00.000')as datetime)
group by
flname,
cast((substring(substring(flname,(charindex('_full',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_full',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_full',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_full',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_full',flname,0)+6),12),11,2)+':00.000')as datetime)
order by
cast((substring(substring(flname,(charindex('_full',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_full',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_full',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_full',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_full',flname,0)+6),12),11,2)+':00.000')as datetime) desc

insert into #resfilelist
select top 1
flname,
max(cast((substring(substring(flname,(charindex('_diff',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_diff',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),11,2)+':00.000')as datetime)) as wdate 
from 
#file_detail_full
where
(select wdate from #resfilelist where flname like '%[_]full[_]%') <= cast((substring(substring(flname,(charindex('_diff',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_diff',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),11,2)+':00.000') as datetime)
and
(flname like '%[_]diff[_]%')
and
@resttm >= cast((substring(substring(flname,(charindex('_diff',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_diff',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),11,2)+':00.000') as datetime)
group by
flname,
cast((substring(substring(flname,(charindex('_diff',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_diff',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),11,2)+':00.000') as datetime)
order by
cast((substring(substring(flname,(charindex('_diff',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_diff',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_diff',flname,0)+6),12),11,2)+':00.000') as datetime) desc

if (select count(*) from #resfilelist where flname like '%[_]diff[_]%') > 0
begin
insert into #resfilelist
select 
flname,
cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime) as wdate 
from 
#file_detail_full
where
--caused log files that completed after the diff to be skipped. leaving it in until I feel like it can be deleted -- wes
--(select max(wdate) as wdate from #resfilelist where flname like '%[_]diff[_]%') <= cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime)
(select max(wdate) as wdate from #resfilelist where flname like '%[_]diff[_]%') <= cast(substring(last_write_d,1,4)+'/'+substring(last_write_d,5,2)+'/'+substring(last_write_d,7,2)+' '+substring(replace((space((6-len(last_write_t)))+ltrim(rtrim(last_write_t))),' ','0'),1,2)+':'+substring(replace((space((6-len(last_write_t)))+ltrim(rtrim(last_write_t))),' ','0'),3,2)+':'+substring(replace((space((6-len(last_write_t)))+ltrim(rtrim(last_write_t))),' ','0'),5,2) as datetime)
and@resttm >= cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime)
andflname like '%[_]tran[_]%'
order by
cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime)
if @resttm < @hlddt
begin
insert into #resfilelist
select top 1
flname,
cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime) as wdate 
from 
#file_detail_full
where
flname like '%[_]tran[_]%'
and
(select max(wdate) from #resfilelist where flname like '%[_]tran[_]%') < cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime)
end
end
else
begin
insert into #resfilelist
select 
flname,
cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime) as wdate 
from 
#file_detail_full
where
(select wdate from #resfilelist where flname like '%[_]full[_]%') <= cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime)
and
flname like '%[_]tran[_]%'
and
@resttm >= cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime)
order by
cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime)
end

if @resttm < @hlddt
begin
if (select count(*) from #resfilelist where flname like'%[_]tran[_]%') > 0
begin
insert into #resfilelist
select top 1
flname,
cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime) as wdate 
from 
#file_detail_full
where
flname like '%[_]tran[_]%'
and
(select max(wdate) from #resfilelist where flname like '%[_]tran[_]%') < cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime)
end
if (select count(*) from #resfilelist where flname like '%[_]tran[_]%') = 0
begin
insert into #resfilelist
select top 1
flname,
cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime) as wdate 
from 
#file_detail_full
where
flname like '%[_]tran[_]%'
and (select max(wdate) from #resfilelist where flname like '%[_]diff[_]%') < cast((substring(substring(flname,(charindex('_tran',flname,0)+6),12),1,4)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),5,2)+'/'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),7,2)+' '+substring(substring(flname,(charindex('_tran',flname,0)+6),12),9,2)+':'+substring(substring(flname,(charindex('_tran',flname,0)+6),12),11,2)+':00.000')as datetime)
end
end

/*
select * from #resfilelist
select * from #file_list
select * from #file_detail
select * from #file_detail_full
select * from #resfilelist
*/--test files to see if they are SQL LiteSpeed files or normal files
DECLARE SLSCursor CURSOR
READ_ONLY
FOR SELECT flname from #resfilelist

OPEN SLSCursor

FETCH NEXT FROM SLSCursor INTO @flname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
exec('RESTORE FILELISTONLY FROM DISK = '''+@flname+'''')
if @@error <> 0
begin
if @slsinst = 1
begin
print '--------------------------------------------------------------------------------------------------------------------------'
print 'Trying to restore SQL LiteSpeed files without SQL LiteSpeed installed.'
print 'Ending restore, Install SQL LiteSpeed'
print '--------------------------------------------------------------------------------------------------------------------------'
set @stoprest = 1
end
end
END
FETCH NEXT FROM SLSCursor INTO  @flname
END

CLOSE SLSCursor
DEALLOCATE SLSCursor
end

--if there isn't a stop issued then run the restore
if @stoprest = 0
begin

set @flname = ''
select @full = flname 
from 
#resfilelist 
where
flname like '%[_]full[_]%'

select @diff = flname 
from 
#resfilelist 
where
flname like '%[_]diff[_]%'

--build file list for fulls and diffs for the WITH MOVE statements
insert into #verifyfilelistfull exec('RESTORE FILELISTONLY FROM DISK = '''+@full+'''')
if @@error <> 0 
begin
insert into #verifyfilelistfull EXEC master.dbo.xp_restore_filelistonly  @filename = @full
end

if @diff is not null
begin
insert into #verifyfilelistdiff exec('RESTORE FILELISTONLY FROM DISK = '''+@diff+'''')
if @@error <> 0 
begin
insert into #verifyfilelistdiff EXEC master.dbo.xp_restore_filelistonly  @filename = @diff
end
end

select @cnthld = count(flname) from #resfilelist where flname like '%[_]diff[_]%'
select @cnthld2 = count(flname) from #resfilelist where flname like '%[_]tran[_]%'

if (select count(*) from master.dbo.sysdatabases where name = @trgdb) = 0
--if the target database doesn't exist the abort the restore
begin
print '--------------------------------------------------------------------------------------------------------------------------'
print 'TARGET DATABASE DOES NOT EXIST!!!!'
INSERT INTO #detachdata ([file],[size],[growth],[name],[group],[filetype])
select PhysicalName,(Size/1024) as Size,'10%' as Growth,LogicalName,FileGroupName,Type from #verifyfilelistfull

SET @sql = 'CREATE DATABASE ' + RTRIM(@trgdb) + ' ON PRIMARY '

DECLARE DetachData CURSOR FOR
SELECT [file], [size], [growth], [name], [group]
FROM #detachdata
WHERE filetype = 'D'

OPEN DetachData
FETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @group
WHILE @@FETCH_STATUS = 0
  BEGIN
IF RTRIM(@group) = 'PRIMARY'
    SET @sql = @sql
   ELSE IF @group != @prevgroup
           SET @sql = @sql +  ' FILEGROUP ' + @group + ' '

  SET @prevgroup = @group

  SET @sql = @sql + '('
      SET @sql = @sql + 'NAME = ''' + RTRIM(@name) + ''','
  SET @sql = @sql + 'FILENAME = ''' + RTRIM(@file) + ''','
  SET @sql = @sql + 'SIZE = 10, '
   SET @sql = @sql + 'FILEGROWTH = ' + @growth
SET @sql = @sql + ')'
      FETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @group
    
      IF @@FETCH_STATUS = 0
        SET @sql = @sql + ','
  END

CLOSE DetachData
DEALLOCATE DetachData

SET @sql = @sql + ' LOG ON '

DECLARE DetachData CURSOR FOR
SELECT [file], [size], [growth], [name], [group]
FROM #detachdata
WHERE [filetype] = 'L'

OPEN DetachData
FETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @group
WHILE @@FETCH_STATUS = 0
  BEGIN
  
  SET @sql = @sql + '('
  SET @sql = @sql + 'NAME = ''' + RTRIM(@name) + ''','
  SET @sql = @sql + 'FILENAME = ''' + RTRIM(@file) + ''','
  SET @sql = @sql + 'SIZE = 10, '
   SET @sql = @sql + 'FILEGROWTH = ' + @growth
  SET @sql = @sql + ')'
      FETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @group
    
      IF @@FETCH_STATUS = 0
        SET @sql = @sql + ','
  END

CLOSE DetachData
DEALLOCATE DetachData

print 'Suggested create database statement based on backup information or user defined default file locations.'
print 'You may need to change the drive letter and path of the MDF and LDF files.'
print ''
PRINT @sql
print ''
print 'Restore terminated.'
print '--------------------------------------------------------------------------------------------------------------------------'
end
else
if (select count(b.filename) from sysdatabases a inner join sysaltfiles b on a.dbid = b.dbid where a.name = @trgdb) <> (select count(*) from #verifyfilelistfull)
begin

print '--------------------------------------------------------------------------------------------------------------------------'
Print 'The database file structure is different from the file structure in the full backup file.'
Print 'Files missing from backup file are:'

DECLARE FileErrCursor CURSOR
READ_ONLY
FOR
select b.name,b.filename from sysdatabases a inner join sysaltfiles b on a.dbid = b.dbid 
left outer join #verifyfilelistfull c 
on 
b.name = c.LogicalName
where a.name = @trgdb and c.PhysicalName is null

OPEN FileErrCursor

FETCH NEXT FROM FileErrCursor INTO @flnamemiss,@flmiss
WHILE (@@fetch_status <> -1)
BEGIN
print 'Logical file name: '+ltrim(rtrim(@flnamemiss))+' Physical file name: '+ltrim(rtrim(@flmiss))
FETCH NEXT FROM FileErrCursor INTO  @flnamemiss,@flmiss
END

CLOSE FileErrCursor
DEALLOCATE FileErrCursor

print 'Restore terminated.'
print '--------------------------------------------------------------------------------------------------------------------------'

end
else
if (select count(b.filename) from sysdatabases a inner join sysaltfiles b on a.dbid = b.dbid where a.name = @trgdb) <> (select count(*) from #verifyfilelistdiff)
begin
print '--------------------------------------------------------------------------------------------------------------------------'
Print 'The database file structure is different from the file structure in the diff backup file.'
Print 'Files missing from backup file are:'

DECLARE FileErrCursor CURSOR
READ_ONLY
FOR
select b.name,b.filename from sysdatabases a inner join sysaltfiles b on a.dbid = b.dbid 
left outer join #verifyfilelistdiff c 
on 
b.name = c.LogicalName
where a.name = @trgdb and c.PhysicalName is null

OPEN FileErrCursor

FETCH NEXT FROM FileErrCursor INTO @flnamemiss,@flmiss
WHILE (@@fetch_status <> -1)
BEGIN
print 'Logical file name: '+ltrim(rtrim(@flnamemiss))+' Physical file name: '+ltrim(rtrim(@flmiss))
FETCH NEXT FROM FileErrCursor INTO  @flnamemiss,@flmiss
END

CLOSE FileErrCursor
DEALLOCATE FileErrCursor

print 'Restore terminated.'
print '--------------------------------------------------------------------------------------------------------------------------'
end
else
if (select count(name) from master.dbo.sysdatabases where name = @trgdb) > 0 and (convert(sysname,databasepropertyex(@trgdb,'recovery')) <> 'RESTORING')
begin
insert into #sysfiles exec('select * from '+@trgdb+'..sysfiles')
--inserts the file list from the database that is going to recive the restore

--kill all users
INSERT INTO #tmpUsers EXEC sp_who

DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @trgdb

OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @sdbname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @cmd = 'KILL ' + @spid
if @debug = 0
begin
EXEC (@cmd)
end
END
FETCH NEXT FROM LoginCursor INTO  @spid, @sdbname
END

CLOSE LoginCursor
DEALLOCATE LoginCursor
--end kill all users

if @cnthld >= 1 or @cnthld2 >= 1
begin
insert into #movelist
select 
a.LogicalName,
b.filename
from
#verifyfilelistfull a
inner join
#sysfiles b
on
a.LogicalName = b.name

DECLARE db_cursor CURSOR FOR 
select * from #movelist

set @cmd = 'restore database '+@trgdb+' FROM DISK = '''+@full+''' WITH REPLACE, STANDBY = '''+@undodat+''' '
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @lname,@fname
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = @cmd + ', MOVE '''+ltrim(rtrim(@lname))+''' TO '''+ltrim(rtrim(@fname))+''' '
FETCH NEXT FROM db_cursor INTO @lname,@fname
END
CLOSE db_cursor
DEALLOCATE db_cursor

print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
if @@error <> 0 
begin
DECLARE db_cursor CURSOR FOR 
select * from #movelist

set @cmd = 'EXEC master.dbo.xp_restore_database  @database='''+@trgdb+''', @filename = '''+@full+''', @with = ''REPLACE'' , @with=''STANDBY = "'+@undodat+'"'' '
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @lname,@fname
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = @cmd +' , @with = ''MOVE "'+ltrim(rtrim(@lname))+'" TO "'+ltrim(rtrim(@fname))+'"'''
FETCH NEXT FROM db_cursor INTO @lname,@fname
END
CLOSE db_cursor
DEALLOCATE db_cursor

print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
end
end
else
begin
insert into #movelist
select 
a.LogicalName,
b.filename
from
#verifyfilelistfull a
inner join
#sysfiles b
on
a.LogicalName = b.name

DECLARE db_cursor CURSOR FOR 
select * from #movelist
if @standby = 0
begin
set @cmd = 'restore database '+@trgdb+' FROM DISK = '''+@full+''' WITH REPLACE, RECOVERY'

end
else
begin
set @cmd = 'restore database '+@trgdb+' FROM DISK = '''+@full+''' WITH REPLACE, STANDBY = '''+@undodat+''' '
end
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @lname,@fname
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = @cmd + ', MOVE '''+ltrim(rtrim(@lname))+''' TO '''+ltrim(rtrim(@fname))+''' '
FETCH NEXT FROM db_cursor INTO @lname,@fname
END
CLOSE db_cursor
DEALLOCATE db_cursor
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
if @@error <> 0
begin
DECLARE db_cursor CURSOR FOR 
select * from #movelist
if @standby = 0
begin
set @cmd = 'EXEC master.dbo.xp_restore_database  @database='''+@trgdb+''', @filename = '''+@full+''', @with = ''REPLACE'' , @with=''RECOVERY'''

end
else
begin
set @cmd = 'EXEC master.dbo.xp_restore_database  @database='''+@trgdb+''', @filename = '''+@full+''', @with = ''REPLACE'' , @with=''STANDBY = "'+@undodat+'"'' '
end
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @lname,@fname
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = @cmd +' , @with = ''MOVE "'+ltrim(rtrim(@lname))+'" TO "'+ltrim(rtrim(@fname))+'"'''
FETCH NEXT FROM db_cursor INTO @lname,@fname
END
CLOSE db_cursor
DEALLOCATE db_cursor
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
end
end
if @cnthld2 >= 1
begin
truncate table #movelist
insert into #movelist
select 
a.LogicalName,
b.filename
from
#verifyfilelistdiff a
inner join
#sysfiles b
on
a.LogicalName = b.name

DECLARE db_cursor CURSOR FOR 
select * from #movelist
set @cmd = 'restore database '+@trgdb+' FROM DISK = '''+@diff+''' WITH REPLACE, STANDBY = '''+@undodat+''' '
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @lname,@fname
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = @cmd + ', MOVE '''+ltrim(rtrim(@lname))+''' TO '''+ltrim(rtrim(@fname))+''' '
FETCH NEXT FROM db_cursor INTO @lname,@fname
END
CLOSE db_cursor
DEALLOCATE db_cursor
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end

if @@error <> 0
begin
DECLARE db_cursor CURSOR FOR 
select * from #movelist
set @cmd = 'EXEC master.dbo.xp_restore_database  @database='''+@trgdb+''', @filename = '''+@diff+''', @with = ''REPLACE'' , @with=''STANDBY = "'+@undodat+'"'' '
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @lname,@fname
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = @cmd +' , @with = ''MOVE "'+ltrim(rtrim(@lname))+'" TO "'+ltrim(rtrim(@fname))+'"'''
FETCH NEXT FROM db_cursor INTO @lname,@fname
END
CLOSE db_cursor
DEALLOCATE db_cursor
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
end
end
else
begin
truncate table #movelist
insert into #movelist
select 
a.LogicalName,
b.filename
from
#verifyfilelistdiff a
inner join
#sysfiles b
on
a.LogicalName = b.name

DECLARE db_cursor CURSOR FOR 
select * from #movelist
if @standby = 0
begin
set @cmd = 'restore database '+@trgdb+' FROM DISK = '''+@diff+''' WITH REPLACE, RECOVERY'
end
else
begin
set @cmd = 'restore database '+@trgdb+' FROM DISK = '''+@diff+''' WITH REPLACE, STANDBY = '''+@undodat+''' '
end
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @lname,@fname
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = @cmd + ', MOVE '''+ltrim(rtrim(@lname))+''' TO '''+ltrim(rtrim(@fname))+''' '
FETCH NEXT FROM db_cursor INTO @lname,@fname
END
CLOSE db_cursor
DEALLOCATE db_cursor
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
if @@error <> 0 
begin
DECLARE db_cursor CURSOR FOR 
select * from #movelist
if @standby = 0
begin
set @cmd = 'EXEC master.dbo.xp_restore_database  @database='''+@trgdb+''', @filename = '''+@diff+''', @with = ''REPLACE'' , @with=''RECOVERY'''

end
else
begin
set @cmd = 'EXEC master.dbo.xp_restore_database  @database='''+@trgdb+''', @filename = '''+@diff+''', @with = ''REPLACE'' , @with=''STANDBY = "'+@undodat+'"'' '
end
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @lname,@fname
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = @cmd +' , @with = ''MOVE "'+ltrim(rtrim(@lname))+'" TO "'+ltrim(rtrim(@fname))+'"'''
FETCH NEXT FROM db_cursor INTO @lname,@fname
END
CLOSE db_cursor
DEALLOCATE db_cursor
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
end
end


if @cnthld2 >= 1
begin
select @cnt = count(flname) from #resfilelist 
where
flname like '%[_]tran[_]%'

set @i = 1
DECLARE db_cursor CURSOR FOR 
select * from #resfilelist 
where
flname like '%[_]tran[_]%'
order by
wdate
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @flname,@fldate
WHILE @cnt > @i
BEGIN
set @cmd = 'restore log '+@trgdb+' FROM DISK = '''+@flname+''' WITH STANDBY = '''+@undodat+''' '
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
if @@error <> 0
begin
set @cmd = 'EXEC master.dbo.xp_restore_log  @database='''+@trgdb+''', @filename = '''+@flname+''' , @with=''STANDBY = "'+@undodat+'"'' '
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
end
set @i = @i + 1
FETCH NEXT FROM db_cursor INTO @flname,@fldate
END
CLOSE db_cursor
DEALLOCATE db_cursor
end
if @cnthld2 >= 1 
begin
select @flname = flname from #resfilelist 
where
flname like '%[_]tran[_]%'
order by wdate 

if @standby = 0
begin
if @resttm = @hlddt
begin
set @cmd = 'restore log '+@trgdb+' FROM DISK = '''+@flname+''' WITH RECOVERY'
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
if @@error <> 0
begin

set @cmd = 'EXEC master.dbo.xp_restore_log  @database='''+@trgdb+''', @filename = '''+@flname+''' , @with=''RECOVERY'' '
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
end
end
else
begin
set @cmd = 'restore log '+@trgdb+' FROM DISK = '''+@flname+''' WITH  STOPAT='''+@hldresttm+''''
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
if @@error <> 0 
begin
set @cmd = 'EXEC master.dbo.xp_restore_log  @database='''+@trgdb+''', @filename = '''+@flname+''' , @with = ''STOPAT = "'+@hldresttm+'"'' '
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
end
end
end
else
begin

if @resttm = @hlddt
begin
set @cmd = 'restore log '+@trgdb+' FROM DISK = '''+@flname+''' WITH STANDBY = '''+@undodat+''''
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end
if @@error <> 0 
begin

set @cmd = 'EXEC master.dbo.xp_restore_log  @database='''+@trgdb+''', @filename = '''+@flname+''' , @with=''STANDBY = "'+@undodat+'"'' '
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end

set @cmd = 'EXEC master.dbo.xp_restore_log  @database='''+@trgdb+''', @filename = '''+@flname+''' , @with=''RECOVERY'' '
print '-------This is the command that will restore the last tlog that put the database in recovery mode------'
print @cmd
print '-------------------------------------------------------------------------------------------------------'
end
else 
begin
set @cmd = 'restore log '+@trgdb+' FROM DISK = '''+@flname+''' WITH RECOVERY'
print '-------This is the command that will restore the last tlog that put the database in recovery mode------'
print @cmd
print '-------------------------------------------------------------------------------------------------------'
end
end
else
begin
set @cmd = 'restore log '+@trgdb+' FROM DISK = '''+@flname+''' WITH  STANDBY = '''+@undodat+''' , STOPAT='''+@hldresttm+''''
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end

if @@error <> 0 
begin
set @cmd = 'EXEC master.dbo.xp_restore_log  @database='''+@trgdb+''', @filename = '''+@flname+''' , @with=''STANDBY = "'+@undodat+'"'', @with = ''STOPAT = "'+@hldresttm+'"'' '
print @cmd
if @debug = 0
begin
EXEC (@cmd)
end

set @cmd = 'EXEC master.dbo.xp_restore_log  @database='''+@trgdb+''', @filename = '''+@flname+''' , @with = ''STOPAT = "'+@hldresttm+'"'' '
print '-------This is the command that will restore the last tlog that put the database in recovery mode------'
print @cmd
print '-------------------------------------------------------------------------------------------------------'
end
else 
begin
set @cmd = 'restore log '+@trgdb+' FROM DISK = '''+@flname+''' WITH  RECOVERY, STOPAT='''+@hldresttm+''''
print '-------This is the command that will restore the last tlog that put the database in recovery mode------'
print @cmd
print '-------------------------------------------------------------------------------------------------------'
end

end

end
end
end
else
begin
select @cmd = 'Logical file names do not match between databases!'
select @cmd  = @cmd+' Source Database --> '+@srcdb+' Target Database--> '+@trgdb+' expected Logical File Names--> '
select @cmd = @cmd+' '+ltrim(rtrim(LogicalName))  from #verifyfilelistfull order by LogicalName desc
select @cmd = @cmd+' '+'recived --> '
select @cmd = @cmd+' '+ltrim(rtrim(name)) from #sysfiles b
print '--------------------------------------------------------------------------------------------------------------------------'
print @cmd
print '--------------------------------------------------------------------------------------------------------------------------'
end
end
else
begin
print '--------------------------------------------------------------------------------------------------------------------------'
print 'Restore Terminated'
print '--------------------------------------------------------------------------------------------------------------------------'
end

drop table #resfilelist
drop table #file_detail_full
drop table #file_detail
drop table #file_list
drop table #verifyfilelistfull
drop table #verifyfilelistdiff
drop table #sysfiles
drop table #movelist
drop table #detachdata
drop table #tmpUsers
drop table #direxist

if @shrinkdb = 0
begin
exec sp_dboption @trgdb, 'trunc. log on chkpt.' ,'TRUE'
dbcc shrinkdatabase(@trgdb)
end
end
set nocount off
go

Rate

Share

Share

Rate