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)
		and	flname 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