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
When doing bulk data changes it may be beneficial to disable indexes prior to starting the operation. Fortunately, SQL's rich metadata makes this very easy to automate in a robust fashion.
2018-05-18 (first published: 2015-10-19)
4,660 reads
If you think about it carefully, you’ll realise, that index maintenance is necessary and that it makes sense, and that SQL Server is not doing this task automatically according to the book.
2011-08-18
5,492 reads
Far too often, people who are not trained database administrators somehow end up responsible for a database. They lack the proper training and knowledge to maintain their database, and problems begin to develop. Here’s a primer for all those involuntary DBAs who need a crash course in database maintenance best practices.
2008-08-13
6,696 reads
This script will change the owner of the stored procedure to dbo. It must be run in the same database as the stored procedures.
2001-10-28
1,418 reads
Ever wanted to know whether your triggers are enabled or not? The ObjectProperty command allows you to select information about the status of objects in your database, returning 1 for True and 0 for False. The example below determines whether a single trigger is disabled. SELECT ObjectProperty(object_id('Trigger_Name'), 'ExecIsTriggerDisabled') There are a number of other useful […]
2001-10-28
1,047 reads