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
