yet another backup script usp_backup

,

I wrote this to backup to a network share you can set the backup to spool to the local disk first then it will move it to another drive or to a unc just fine. Directory to hold and move to are not optional but if you set them to the same path it won't hurt the script at all.

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

usage

usp_backup ,2,'',''

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

by: wesley d. brown
date 07/13/03
mod  07/24/03
mod  07/31/03
mod  08/27/03
mod  12/08/03
*** 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
** 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

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

this has only been tested under ms-sql2k and win2k
************************************************************************************************/
--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

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)

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

/*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
(
	name varchar(100),
	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)
)


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 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
					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  (@db_used+@log_used) < @fds
					begin
						--if it fits backup the db
						truncate table #tmpthread
			
						insert into #tmpthread
						select 
							b.name,
							a.program_name,
							a.cmd 
							from master.dbo.sysprocesses a with (nolock)
						inner join
							master.dbo.sysdatabases b with (nolock)
						on
							a.dbid = b.dbid
						where
						ltrim(rtrim(b.name)) = @dbname
						and
						(
							program_name like '%sqllitespeed%'
							or
							cmd like '%DBCC%'
							or
							cmd like '%BACKUP DATABASE%'
							or
							cmd like '%BACKUP LOG%'
						)
					
						startwatch1:
						if (select count(*) from #tmpthread) > 0
						begin
							truncate table #tmpthread
						
							insert into #tmpthread
							select 
								b.name,
								a.program_name,
								a.cmd 
							from 
								master.dbo.sysprocesses a with (nolock)
							inner join
								master.dbo.sysdatabases b with (nolock)
							on
								a.dbid = b.dbid
							where
								ltrim(rtrim(b.name)) = @dbname
							and
							(
								program_name like '%sqllitespeed%'
								or
								cmd like '%DBCC%'
								or
								cmd like '%BACKUP DATABASE%'
								or
								cmd like '%BACKUP LOG%'
							)
			
							waitfor delay '000:01:00'
							goto startwatch1
						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
				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
				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
				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  (@db_used+@log_used) < @fds
				begin
					--if it fits backup the db
					truncate table #tmpthread
		
					insert into #tmpthread
					select 
						b.name,
						a.program_name,
						a.cmd 
						from master.dbo.sysprocesses a with (nolock)
					inner join
						master.dbo.sysdatabases b with (nolock)
					on
						a.dbid = b.dbid
					where
					ltrim(rtrim(b.name)) = @dbname
					and
					(
						program_name like '%sqllitespeed%'
						or
						cmd like '%DBCC%'
						or
						cmd like '%BACKUP DATABASE%'
						or
						cmd like '%BACKUP LOG%'
					)
				
					startwatch2:
					if (select count(*) from #tmpthread) > 0
					begin
						truncate table #tmpthread
					
						insert into #tmpthread
						select 
							b.name,
							a.program_name,
							a.cmd 
						from 
							master.dbo.sysprocesses a with (nolock)
						inner join
							master.dbo.sysdatabases b with (nolock)
						on
							a.dbid = b.dbid
						where
							ltrim(rtrim(b.name)) = @dbname
						and
						(
							program_name like '%sqllitespeed%'
							or
							cmd like '%DBCC%'
							or
							cmd like '%BACKUP DATABASE%'
							or
							cmd like '%BACKUP LOG%'
						)
		
						waitfor delay '000:01:00'
						goto startwatch2
					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
			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
				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
				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
	
				if  (@log_used) < @fds
				begin
		
						--if it fits backup the db
						truncate table #tmpthread
		
						insert into #tmpthread
						select 
							b.name,
							a.program_name,
							a.cmd 
							from master.dbo.sysprocesses a with (nolock)
						inner join
							master.dbo.sysdatabases b with (nolock)
						on
							a.dbid = b.dbid
						where
						ltrim(rtrim(b.name)) = @dbname
						and
						(
							program_name like '%sqllitespeed%'
							or
							cmd like '%DBCC%'
							or
							cmd like '%BACKUP DATABASE%'
							or
							cmd like '%BACKUP LOG%'
						)
						
						startwatch3:
						if (select count(*) from #tmpthread) > 0
						begin
							truncate table #tmpthread
						
							insert into #tmpthread
							select 
								b.name,
								a.program_name,
								a.cmd 
							from 
								master.dbo.sysprocesses a with (nolock)
							inner join
								master.dbo.sysdatabases b with (nolock)
							on
								a.dbid = b.dbid
							where
								ltrim(rtrim(b.name)) = @dbname
							and
							(
								program_name like '%sqllitespeed%'
								or
								cmd like '%DBCC%'
								or
								cmd like '%BACKUP DATABASE%'
								or
								cmd like '%BACKUP LOG%'
							)
							waitfor delay '000:01:00'
							goto startwatch3
						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

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

Rate

Share

Share

Rate