yet another backup script using litespeed or native backup

,

Updated! Now supports both SQL Litespeed and native backup commands in one script!

Several new bug fixes please see the header of the script. Error messages to add are now at the bottom of the script!

This stored procedure will backup a single database or all databases it will also perform ether a full, diff, or tlog backup. This uses SQL LiteSpeed or native backup provided by SQL Server this stored procedure is designed to take x parameters:

@dbname : required, this is the database you wish to backup null or comma seperated list

@bktype : required, this is the type of backup 0=full,1=diff,2=tlog

@bkpath : required, this is the path can be a drive letter i.e. c:\bak or a unc \\myserver\bak

@bkpathhold : required, this is the directory that backups are spooled to first can be the same as the @bkpath

@wtcnt int : Default 60, this sets the maximum number of wait loops

@sls tinyint : Default 1, this sets the default action to backup using SLS if it is available

usage

usp_backup2 ,2,'',''

/************************************************************************************************
usp_backup

by: Wesley D. Brown
date 07/13/2003
mod  07/24/2003
mod  07/31/2003
mod  08/27/2003
mod  12/08/2003
mod  07/26/2004
*** change log ***
07/24/03
removed the @bakall flag
added usage tracking
added list parsing
added directory creation for holding and storing directories
added support for instance names
07/31/03
bug fix diff backup of master if it was passed in the comma dilimited list
bug fix using @@error without converting to char type.
added backup time tracking to table backup_times
08/17/03
bug fix case issues on latin1_bin collation servers
added custom error numbers
08/27/03
bug fix spaces in db names causing it not to backup
12/08/03
removed any data gathering in this script to msdb
removed redundant code based on list of db's or not
added a test to see if another conflicting backup is running on the database in question
added check to see if database is in a mode that can be backed up
added check to see if there is a full before a diff or tlog exist before trying to back it up
07/26/2004
bug fix diff backups failed if database was read-only
bug fix/added variable set waittime loop would get hung up waiting for other backups or dbcc's
bug fix tlog backups could run during diffs causing sync issues on restore
bug fix reporting not enough space to complete backup when using SLS and there should be enough space
bug fix drivespace may be incorrectly reported to tlog backup step
added support for SLS into single script with SLS install check
** end change log **

this stored procedure will backup a single database or all databases it will also perform
ether a full, diff, or tlog backup. 

this stored procedure is designed to take x parameters:
@dbname 	: required, this is the database you wish to backup null or comma seperated list
@bktype 	: required, this is the type of backup 0=full,1=diff,2=tlog 
@bkpath 	: required, this is the path can be a drive letter i.e. c:\bak or a unc \\myserver\bak
@bkpathhold 	: required, this is the directory that backups are spooled to first
@wtcnt int 	: Default 60, this sets the maximum number of wait loops  
@sls tinyint	: Default 1, this sets the default action to backup using SLS if it is available

usage
usp_backup <null or comma seperated list>,2,'<path to put finished files>','<path to holding files>,60,1'

this has only been tested under ms-sql2k and win2k
************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[usp_backup]') and objectproperty(id, 'isprocedure') = 1)
begin
drop procedure dbo.usp_backup
end
go
--drop it if it is already in the syscatalog

create procedure usp_backup 
@dbname as varchar(255),
@bktype as tinyint,
@bkpath varchar(300),
@bkpathhold varchar(300),
@wtcnt int = 60,
@sls tinyint = 1

with recompile
-- we do with recompile because we don't need an execution plan hanging around
as	

set nocount on

declare @fds as numeric(38,8)
/*holds free drive space*/
declare @fdshld as numeric(38,8)
/*holds free drive space*/
declare @cmd varchar(4000)
/*holds command to be executed*/
declare @dbname_cursor as varchar(255)
/*holds database name for cursor*/
declare @exiterr as bit
/*holds the error indicator*/
declare @db_used as numeric(38,8)
/*holds db space used*/
declare @log_used as numeric(38,8)
/*holds log space used*/
declare @db_bk_mode as varchar(255)
/*holds backup mode indicator*/
declare @dbholder varchar(200)
/*holds db name for parsing*/
declare @pos numeric(38,8)
/*used in parsing*/
declare @bkvar as varchar(255)
/*holds path for backup directory*/
declare @bkvarhld as varchar(255)
/*holds path for backup directory*/
declare @bkpathholdsrv as varchar(500)
/*holds path for backup directory*/
declare @bkpathsrv as varchar(500)
/*holds path for backup directory*/
declare @full as varchar(255)
/*holds path for backup directory*/
declare @fullhld as varchar(255)
/*holds path for backup directory*/
declare @fname as varchar(255)
/*holds path for backup directory*/
declare @svrname as varchar(255)
/*holds path for backup directory*/
declare @time as varchar(255)
/*holds path for backup directory*/
declare @date as varchar(255)
/*holds path for backup directory*/
declare @vrfy as tinyint
/*hold the verification of backup*/
declare @wtcnthld as tinyint
/*loop counter reset for every new loop*/

/*get our date and time for later use*/
select @date = convert(varchar,getdate(),112)
select @time = replace(convert(varchar,current_timestamp,114),':','')
select @time = left(@time,4)

/*get local servername to use in backup job*/
select @svrname = cast(serverproperty('servername') as varchar(255))

/*check to see if it is an instance name and correct for the slash*/
if charindex('\',@svrname,1) > 0 
begin
	set @svrname = replace(@svrname,'\','_')
end

/*clear the error flag*/
set @exiterr = 0

/*table to hold our parsed list of databases*/
create table #dblist
(
	dbname varchar(255)
)

/*table #free_drive_space to hold free drive space on backup drive from command dir output*/
create table #free_drive_space
(
	dir varchar(8000)
)

/*table #idxtmp to hold used space in db from sysindexes*/
create table #idxtmp
(
	dbname varchar(255),
	used numeric(38,8)
)

/*table #logspace to hold log space useage in db from dbcc sqlperf(logspace)*/
create table #logspace
(
	name varchar(255),
	log_size numeric(38,8),
	used numeric(38,8),
	status bit
)
	
/*table #db_stats to hold database useage statistics*/
create table #db_stats
(
	dbname varchar(255),
	db_size numeric(38,8),
	log_size numeric(38,8),
	db_used numeric(38,8),
	log_used numeric(38,8),
	db_free numeric(38,8),
	log_free numeric(38,8)
) 

/*for error checking and making sure our backup structures exisit on the file system*/
create table  #direxist
(
	fe numeric(38,8), 
	fd numeric(38,8), 
	pd numeric(38,8)
)
/*hold active thread info for delay if needed*/
create table #tmpthread
(
	program_name varchar(500),
	cmd varchar(500)
)

/*grabs the status of every database*/
create table #dbstatus
(
	dbname varchar(100),
	recovery varchar(100),
	status varchar(100),
	updateability varchar(100),
	useraccess varchar(100)
)

/*check to see if SLS is installed and if we can use it*/
create table #slsver
(
name varchar(4000),
value varchar(4000)
)


create table #first_backup
(
	dbname varchar(100),
	create_date datetime,
	backup_start_date datetime
)

insert into #first_backup
select
	s.name as 'database',
	s.crdate as 'create_date',
	b.backup_start_date
from 	
	master.dbo.sysdatabases	s
left outer join	
	msdb..backupset b
	on 
	s.name = b.database_name
	and 
	b.backup_start_date = (select 
					max(backup_start_date)
				from 
					msdb..backupset
				where 
					database_name = b.database_name
					and type = 'D'
				)
where
	s.name <> 'tempdb'
and
	b.backup_start_date IS NOT NULL

/*set command we want to run*/
set @cmd='exec master..xp_cmdshell ''dir "'+@bkpathhold+'"'''
/*insert output of the dir command into temp table*/

insert into #free_drive_space exec (@cmd)

/*pull free drive space in bytes into a variable*/
select @fdshld = (
	select top 1 
		cast(replace(rtrim(ltrim(right(substring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)),len(substring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)))-2))),',','')as float) as drive_space
	from 
		#free_drive_space
	where 
		dir like '%bytes free%'
	order by 
		dir asc
)
truncate table #free_drive_space
/*set command we want to run*/
set @cmd='exec master..xp_cmdshell ''dir "'+@bkpath+'"'''

/*insert output of the dir command into temp table*/
insert into #free_drive_space exec (@cmd)

/*pull free drive space in bytes into a variable*/
select @fds = (
	select top 1 
		cast(replace(rtrim(ltrim(right(substring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)),len(substring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)))-2))),',','')as float) as drive_space
	from 
		#free_drive_space
	where 
		dir like '%bytes free%'
	order by 
		dir asc
)

/*insert output of dbcc sqlperf(logspace) into temp table*/
insert into #logspace exec('dbcc sqlperf(logspace) with no_infomsgs')

/*build cursor to populate #idxtmp*/
declare db_cursor cursor for 
	select name from master.dbo.sysdatabases
open db_cursor
fetch next from db_cursor into @dbname_cursor
while @@fetch_status = 0
	begin
			insert into #dbstatus
			select 
			@dbname_cursor as dbname,
			convert(sysname,databasepropertyex(@dbname_cursor,'recovery')), 
			convert(sysname,databasepropertyex(@dbname_cursor,'status')),
			convert(sysname,databasepropertyex(@dbname_cursor,'updateability')),
			convert(sysname,databasepropertyex(@dbname_cursor,'useraccess '))

		if (select status from #dbstatus where dbname = @dbname_cursor) <> 'RESTORING'
		begin
			exec('insert into #idxtmp select '''+@dbname_cursor+''' as dbname,sum(convert(numeric(38,8),(used))*8192)
				from ['+@dbname_cursor+'].dbo.sysindexes
				where indid in (0, 1, 255)')
		end
	
	   fetch next from db_cursor into @dbname_cursor
	end
close db_cursor
deallocate db_cursor

/*populate #db_stats for later use*/
insert into #db_stats
select
	a.name,
	a.db_size,
	b.log_size,
	isnull(i.used,0) as db_used,
	isnull(l.used,0) as log_used,
	(a.db_size-i.used) as db_free,
	isnull((b.log_size-l.used),0) as log_free
from
(
	select
		d.name,
		sum(cast(f.size as numeric(38,8)))*8192 as db_size
	from 
		master..sysdatabases d
	inner join
		master..sysaltfiles f
	on 
		d.dbid = f.dbid
	where
		(f.status & 64 = 0)
	group by
		d.name
)a
inner join
(
	select
		d.name,
		sum(cast(f.size as numeric(38,8)))*8192 as log_size
	from 
		master..sysdatabases d
	inner join
		master..sysaltfiles f
	on 
		d.dbid = f.dbid
	where
		(f.status & 64 <> 0)
	group by
		d.name
)b
on
	a.name = b.name
inner join
	#idxtmp i
on
	a.name = i.dbname
left outer join
(
	select 
		name,
		cast(round((log_size*1048576)*(used/100),0,1)as numeric(38,8)) as used from #logspace
) l
on
	a.[name] = l.[name]
where
a.name not like '%tempdb%'

/*if backup directory isn't found issue and error*/
if @fds is null or @fdshld is null
begin
	raiserror (51000,16,1) with log
	set @exiterr = 1
end

/*if wrong backup type indicated issue error*/
if @bktype is null or @bktype > 2
begin
	raiserror (51001,16,1) with log
	set @exiterr = 1
end

/*if the database name is not null parse the list*/
if @dbname is not null
begin
	set @dbname = ltrim(rtrim(@dbname))+ ','
	set @pos = charindex(',', @dbname, 1)
	if replace(@dbname, ',', '') <> ''
	begin
		while @pos > 0
		begin
			set @dbholder = ltrim(rtrim(left(@dbname, @pos - 1)))
			if @dbholder <> ''
			begin
				insert into #dblist (dbname) values (@dbholder) --use appropriate conversion
			end
			set @dbname = right(@dbname, len(@dbname) - @pos)
			set @pos = charindex(',', @dbname, 1)
		end
	end	
end
else
begin
	insert into #dblist 
	select dbname from #db_stats
end

if exists (select * from master.dbo.sysobjects where id = object_id(N'[dbo].[xp_sqllitespeed_version]'))
begin
	insert into #slsver exec master.dbo.xp_sqllitespeed_version
	--select * from #slsver
	if @@ERROR <> 0
	begin
		print 'SLS not installed'
		set @sls = 0
	end
	
	if (select value from #slsver where name = 'Product Version' and Value like '3.1.%') <> ''
	begin
		set @sls = 1
		if (select value from #slsver where name = 'Restore only support' and Value = '0') <> ''
		begin
			set @sls = 1
		end
		else
		begin
			set @sls = 0
		end
	end
	else
	if (select value from #slsver where name = 'Product Name' and Value like '%SQL%') <> ''
	begin
		set @sls = 1
	end
	drop table #slsver
end
else
begin
	print 'SLS not installed'
	set @sls = 0
end

/* if there are no errors from above continue on*/
/*build directory structures to hold backups*/
if @exiterr = 0
	begin
		select @bkpath = rtrim(@bkpath)
		select @bkpathsrv = rtrim(@bkpath)+'\'+rtrim(@svrname)
		select @bkpathhold = rtrim(@bkpathhold)
		select @bkpathholdsrv = rtrim(@bkpathhold)+'\'+rtrim(@svrname)
	
		truncate table #direxist
		insert into #direxist exec master..xp_fileexist @bkpathsrv
		if (select fd from #direxist) = 1
		begin
			truncate table #direxist
		end
		else
		if (select fd from #direxist) = 0
		begin
			set @cmd='exec master..xp_cmdshell ''md "'+@bkpathsrv+'"'',no_output'
			exec(@cmd)
		end

		truncate table #direxist
		insert into #direxist exec master..xp_fileexist @bkpathholdsrv
		if (select fd from #direxist) = 1
		begin
			truncate table #direxist
		end
		else
		if (select fd from #direxist) = 0
		begin
			set @cmd='exec master..xp_cmdshell ''md "'+@bkpathholdsrv+'"'',no_output'
			exec(@cmd)
		end


		declare full_cursor cursor scroll for 
		select 
			dbname
		from 
			#db_stats 
		where 
			dbname <> 'tempdb' 
		and 
			dbname <> 'pubs' 
		and 
			dbname <> 'northwind'

		--get all the db names and load up a cursor
		open full_cursor
		--open up the cursor
		fetch next from full_cursor into @dbname
		--load the first db name
		while @@fetch_status = 0
		--while we have db names run the loop!
		begin
			select @bkvar = rtrim(@bkpath)+'\'+rtrim(@svrname)+'\'+rtrim(@dbname)
			select @bkvarhld = rtrim(@bkpathhold)+'\'+rtrim(@svrname)+'\'+rtrim(@dbname)
	
			truncate table #direxist
			insert into #direxist exec master..xp_fileexist @bkvar
			if (select fd from #direxist) = 0
			begin
				set @cmd='exec master..xp_cmdshell ''md "'+@bkvar+'"'',no_output'
				exec(@cmd)
			end
			
			truncate table #direxist
			insert into #direxist exec master..xp_fileexist @bkvarhld
			if (select fd from #direxist) = 0
			begin
				set @cmd='exec master..xp_cmdshell ''md "'+@bkvarhld+'"'',no_output'
				exec(@cmd)
			end
			truncate table #direxist
			fetch next from full_cursor into @dbname
		end
		close full_cursor
		deallocate full_cursor

		/*if there is nothing in the db list process all databases*/
		if (select count(*) from #dblist) > 0
		begin
			if @bktype = 0
			begin
				declare full_cursor cursor scroll for 		
				select 
					s.dbname,
					s.db_used,
					s.log_used 
				from 
					#db_stats s
				inner join
					#dblist	d
				on
					s.dbname=d.dbname
				inner join 
					#dbstatus c
				on
					d.dbname=c.dbname
				where
					c.status = 'ONLINE'
				and
					c.useraccess = 'MULTI_USER'
				and
					s.dbname <> 'tempdb' 
				and 
					s.dbname <> 'pubs' 
				and 
					s.dbname <> 'northwind'		

				--get all the db names and load up a cursor
				open full_cursor
				--open up the cursor
				fetch next from full_cursor into @dbname,@db_used,@log_used
				--load the first db name
				while @@fetch_status = 0
				--while we have db names run the loop!
				begin
					truncate table #tmpthread
					set @wtcnthld = 0
					insert into #tmpthread
					select 
						a.program_name,
						a.cmd 
					from 
							master.dbo.sysprocesses a with (nolock)
					where
					(
						program_name like '%sqllitespeed%'
						or
						cmd like '%BACKUP DATABASE%'
						or
						cmd like '%BACKUP LOG%'
					)
				
					startwatch1:
					if (select count(*) from #tmpthread) > 0 and (@wtcnthld < @wtcnt)
					begin
						truncate table #tmpthread
						insert into #tmpthread
						select 
							
							a.program_name,
							a.cmd 
						from 
								master.dbo.sysprocesses a with (nolock)
						where
						(
							program_name like '%sqllitespeed%'
							or
							cmd like '%BACKUP DATABASE%'
							or
							cmd like '%BACKUP LOG%'
						)
			
							set @wtcnthld = @wtcnthld + 1
							waitfor delay '000:01:00'
							goto startwatch1
					end
					else 
					begin
						select @bkpath = rtrim(@bkpath)
						select @bkpathsrv = rtrim(@bkpath)+'\'+rtrim(@svrname)
						select @bkvar = rtrim(@bkpath)+'\'+rtrim(@svrname)+'\'+rtrim(@dbname)
						select @fname = @dbname+'_full_'+@date+@time+'.bak'
						select @full = @bkvar+'\'+@fname
					
						select @bkpathhold = rtrim(@bkpathhold)
						select @bkpathholdsrv = rtrim(@bkpathhold)+'\'+rtrim(@svrname)
						select @bkvarhld = rtrim(@bkpathhold)+'\'+rtrim(@svrname)+'\'+rtrim(@dbname)
						select @fname = @dbname+'_full_'+@date+@time+'.bak'
						select @fullhld = @bkvarhld+'\'+@fname
				
						select @fds = 
							(
								select top 1 
									cast(replace(rtrim(ltrim(right(substring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)),len(substring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)))-2))),',','')as bigint) as drive_space
								from 
									#free_drive_space
								where 
									dir like '%bytes free%'
								order by 
									dir asc
							)
						if @sls = 1
						begin
							set @fds = @fds * 3
						end
						--if it fits backup the db
						if  (@db_used+@log_used) < @fds
						begin
							if @sls = 1 
							begin
								exec master.dbo.xp_backup_database    
							      	  @database = @dbname
								, @filename = @fullhld
								, @threads = 3
								, @priority = 1
								, @logging = 2
	
								set @vrfy = 0
								exec @vrfy = master.dbo.xp_restore_verifyonly @filename=@fullhld , @logging = 1
								if @vrfy <> 0
								begin
									raiserror (51002,16,1) with log
								end
							end
							else
							begin
								--backup database
								backup database @dbname
								to disk = @fullhld
								--verify backup
								restore verifyonly
								from
								disk=@fullhld
	
								if @@error <> 0
								begin
									raiserror (51002,16,1) with log
								end
							end				
							truncate table #direxist
							insert into #direxist exec master..xp_fileexist @fullhld
				
							if (select fe from #direxist) = 1
							begin
								/*set command we want to run*/
								set @cmd='move "'+@fullhld+'" "'+@full+'"'
								/*move files to backup locale*/
								exec master..xp_cmdshell @cmd,no_output
				
								truncate table #direxist
				
								insert into #direxist exec master..xp_fileexist @full
				
								if (select fe from #direxist) = 0 
								begin
									raiserror (51003,16,1) with log
									fetch next from full_cursor into @dbname,@db_used,@log_used
								end
							end
							else
							begin
								raiserror (51004,16,1) with log
							end
							truncate table #direxist
							fetch next from full_cursor into @dbname,@db_used,@log_used		
						end
						else
						begin
							raiserror (51005,16,1) with log
							truncate table #direxist
							fetch next from full_cursor into @dbname,@db_used,@log_used		
						end
					end
				end
				close full_cursor
				deallocate full_cursor
			end
		else
		if @bktype = 1
		begin
			declare diff_cursor cursor scroll for 		
			select 
				s.dbname,
				s.db_used,
				s.log_used 
			from 
				#db_stats s
			inner join
				#dblist	d
			on
				s.dbname=d.dbname
			inner join 
				#dbstatus c
			on
				d.dbname=c.dbname
			inner join
				#first_backup e
			on
				c.dbname = e.dbname
			where
				c.status = 'ONLINE'
			and
				c.useraccess = 'MULTI_USER'
			and
				c.updateability = 'READ_WRITE'
			and
				s.dbname <> 'tempdb' 
			and 
				s.dbname <> 'pubs' 
			and 
				s.dbname <> 'northwind'		
			and 
				s.dbname <> 'master'		
		
			--get all the db names and load up a cursor
			open diff_cursor
			--open up the cursor
			fetch next from diff_cursor into @dbname,@db_used,@log_used
			--load the first db name
			while @@fetch_status = 0
			--while we have db names run the loop!
			begin
				truncate table #tmpthread
				set @wtcnthld = 0
				insert into #tmpthread
				select 

					a.program_name,
					a.cmd 
				from 
						master.dbo.sysprocesses a with (nolock)
				where
				(
					program_name like '%sqllitespeed%'
					or
					cmd like '%BACKUP DATABASE%'
					or
					cmd like '%BACKUP LOG%'
				)
			
				startwatch2:
				if (select count(*) from #tmpthread) > 0 and (@wtcnthld < @wtcnt)
				begin
					truncate table #tmpthread
					insert into #tmpthread
					select 
						
						a.program_name,
						a.cmd 
					from 
							master.dbo.sysprocesses a with (nolock)
					where
					(
						program_name like '%sqllitespeed%'
						or
						cmd like '%BACKUP DATABASE%'
						or
						cmd like '%BACKUP LOG%'
					)
		
						set @wtcnthld = @wtcnthld + 1
						waitfor delay '000:01:00'
						goto startwatch2
				end
				else 
				begin
					select @bkpath = rtrim(@bkpath)
					select @bkpathsrv = rtrim(@bkpath)+'\'+rtrim(@svrname)
					select @bkvar = rtrim(@bkpath)+'\'+rtrim(@svrname)+'\'+rtrim(@dbname)
					select @fname = @dbname+'_diff_'+@date+@time+'.bak'
					select @full = @bkvar+'\'+@fname
				
					select @bkpathhold = rtrim(@bkpathhold)
					select @bkpathholdsrv = rtrim(@bkpathhold)+'\'+rtrim(@svrname)
					select @bkvarhld = rtrim(@bkpathhold)+'\'+rtrim(@svrname)+'\'+rtrim(@dbname)
					select @fname = @dbname+'_diff_'+@date+@time+'.bak'
					select @fullhld = @bkvarhld+'\'+@fname
			
					select @fds = 
						(
							select top 1 
								cast(replace(rtrim(ltrim(right(substring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)),len(substring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)))-2))),',','')as bigint) as drive_space
							from 
								#free_drive_space
							where 
								dir like '%bytes free%'
							order by 
								dir asc
						)
					if @sls = 1
					begin
						set @fds = @fds * 3
					end
					if  (@db_used+@log_used) < @fds
					begin
						if @sls = 1
						begin
							exec master.dbo.xp_backup_database    
						      	  @database = @dbname
							, @filename = @fullhld
							, @threads = 3
							, @logging = 2
							, @with = 'differential'
							, @priority = 1
	
							set @vrfy = 0
							exec @vrfy = master.dbo.xp_restore_verifyonly @filename=@fullhld , @logging = 1
			
							if @vrfy <> 0
							begin
								raiserror (51002,16,1) with log
							end
						end
						else
						begin
							--backup database
							backup database @dbname
							to disk = @fullhld
							with differential
							--verify backup
							restore verifyonly
							from
							disk=@fullhld
	
							if @@error <> 0
							begin
								raiserror (51002,16,1) with log
							end
						end			
						truncate table #direxist
						insert into #direxist exec master..xp_fileexist @fullhld
			
						if (select fe from #direxist) = 1
						begin
							/*set command we want to run*/
							set @cmd='move "'+@fullhld+'" "'+@full+'"'
							/*move files to backup locale*/
							exec master..xp_cmdshell @cmd,no_output
			
							truncate table #direxist
			
							insert into #direxist exec master..xp_fileexist @full
			
							if (select fe from #direxist) = 0 
							begin
								raiserror (51003,16,1) with log
								fetch next from diff_cursor into @dbname,@db_used,@log_used
							end
						end
						else
						begin
							raiserror (51004,16,1) with log
						end
						truncate table #direxist
						fetch next from diff_cursor into @dbname,@db_used,@log_used		
					end
					else
					begin
						raiserror (51005,16,1) with log
						truncate table #direxist
						fetch next from diff_cursor into @dbname,@db_used,@log_used		
					end
				end
			end
			close diff_cursor
			deallocate diff_cursor
		end
		else
		if @bktype = 2
		begin
			declare tlog_cursor cursor scroll for 
			select 
				s.dbname,
				s.db_used,
				s.log_used 
			from 
				#db_stats s
			inner join
				#dblist	d
			on
				s.dbname=d.dbname
			inner join 
				#dbstatus c
			on
				d.dbname=c.dbname
			inner join
				#first_backup e
			on
				c.dbname = e.dbname
			where
				c.status = 'ONLINE'
			and
				c.useraccess = 'MULTI_USER'
			and
				c.updateability = 'READ_WRITE'
			and
				s.dbname <> 'tempdb' 
			and 
				s.dbname <> 'pubs' 
			and 
				s.dbname <> 'northwind'		
			and 
				s.dbname <> 'master'		
			and
				c.recovery <> 'SIMPLE'

			--get all the db names and load up a cursor
			open tlog_cursor
			--open up the cursor
			fetch next from tlog_cursor into @dbname,@db_used,@log_used
			--load the first db name
			while @@fetch_status = 0
			--while we have db names run the loop!
			begin
				truncate table #tmpthread
				set @wtcnthld = 0
				insert into #tmpthread
				select 
					
					a.program_name,
					a.cmd 
				from 
						master.dbo.sysprocesses a with (nolock)
				where
				(
					program_name like '%sqllitespeed%'
					or
					cmd like '%BACKUP DATABASE%'
					or
					cmd like '%BACKUP LOG%'
				)
			
				startwatch3:
				if (select count(*) from #tmpthread) > 0 and (@wtcnthld < @wtcnt)
				begin
					truncate table #tmpthread
					insert into #tmpthread
					select 
						
						a.program_name,
						a.cmd 
					from 
							master.dbo.sysprocesses a with (nolock)
					where
					(
						program_name like '%sqllitespeed%'
						or
						cmd like '%BACKUP DATABASE%'
						or
						cmd like '%BACKUP LOG%'
					)
		
						set @wtcnthld = @wtcnthld + 1
						waitfor delay '000:01:00'
						goto startwatch3
				end
				else 
				begin
					select @bkpath = rtrim(@bkpath)
					select @bkpathsrv = rtrim(@bkpath)+'\'+rtrim(@svrname)
					select @bkvar = rtrim(@bkpath)+'\'+rtrim(@svrname)+'\'+rtrim(@dbname)
					select @fname = @dbname+'_tran_'+@date+@time+'.trn'
					select @full = @bkvar+'\'+@fname
					
					select @bkpathhold = rtrim(@bkpathhold)
					select @bkpathholdsrv = rtrim(@bkpathhold)+'\'+rtrim(@svrname)
					select @bkvarhld = rtrim(@bkpathhold)+'\'+rtrim(@svrname)+'\'+rtrim(@dbname)
					select @fname = @dbname+'_tran_'+@date+@time+'.trn'
					select @fullhld = @bkvarhld+'\'+@fname
					select @fds = 
						(
							select top 1 
								cast(replace(rtrim(ltrim(right(substring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)),len(substring(substring(dir,1,charindex(' bytes',dir)),charindex('(s)',dir),len(dir)))-2))),',','')as bigint) as drive_space
							from 
								#free_drive_space
							where 
								dir like '%bytes free%'
							order by 
								dir asc
						)

					if @sls = 1
					begin
						set @fds = @fds * 3
					end

					if  (@log_used) < @fds
					begin
						if @sls = 1
						begin
							--backup database
							exec master.dbo.xp_backup_log
						      	  @database = @dbname
							, @filename = @fullhld
							, @logging = 2
							, @threads = 3
							, @priority = 1
							/*set command we want to run*/
			
							set @vrfy = 0
							exec @vrfy = master.dbo.xp_restore_verifyonly @filename=@fullhld , @logging = 1
			
							if @vrfy <> 0
							begin
								raiserror (51002,16,1) with log
							end
						end
						else
						begin
							--backup database
							backup log @dbname
							to disk = @fullhld
							--verify backup
							restore verifyonly
							from
							disk=@fullhld
		
							if @@error <> 0
							begin
								raiserror (51002,16,1) with log
							end
						end
						truncate table #direxist
						insert into #direxist exec master..xp_fileexist @fullhld
						if (select fe from #direxist) = 1
						begin
							/*set command we want to run*/
							set @cmd='move "'+@fullhld+'" "'+@full+'"'
							/*move files to backup locale*/
							exec master..xp_cmdshell @cmd,no_output
							truncate table #direxist
							insert into #direxist exec master..xp_fileexist @full
							if (select fe from #direxist) = 0 
							begin
								raiserror (51003,16,1) with log
							end
						end
						else
						begin
							raiserror (51004,16,1) with log
						end
						fetch next from tlog_cursor into @dbname,@db_used,@log_used
					end
					else
					begin
						raiserror (51005,16,1) with log
						fetch next from tlog_cursor into @dbname,@db_used,@log_used
					end
				end
			end
			close tlog_cursor
			deallocate tlog_cursor
		end
	end
end	
/*drop all temp tables*/

drop table #dbstatus
drop table #first_backup
drop table #tmpthread
drop table #logspace
drop table #idxtmp
drop table #db_stats
drop table #free_drive_space
drop table #dblist
drop table #direxist

set nocount off

go

--installes the error messages for the backup scripts. 
if (select count(*) from dbo.sysmessages where error between 51000 and 51005) > 0
begin
 print 'error messages may already exist, please check and correct the error.'
end
else
begin 
 EXEC sp_addmessage 51000, 16, 
  N'backup space does not exist',
  @with_log = 'true',
         @replace = 'replace'
 
 EXEC sp_addmessage 51001, 16, 
  N'error in syntax ether set @bkall to 0=full,1=diff, or 2=tlog',
  @with_log = 'true',
         @replace = 'replace'
 
 EXEC sp_addmessage 51002, 16, 
  N'backup file corrupt',
  @with_log = 'true',
         @replace = 'replace'
 
 EXEC sp_addmessage 51003, 16, 
  N'backup file did not copy',
  @with_log = 'true',
         @replace = 'replace'
 
 EXEC sp_addmessage 51004, 16, 
  N'backup file does not exist to move',
  @with_log = 'true',
         @replace = 'replace'
 
 EXEC sp_addmessage 51005, 16, 
  N'backup drive does not have enough space to complete',
  @with_log = 'true',
         @replace = 'replace'
end
go

Rate

Share

Share

Rate