yet another backup script usp_backup

/************************************************************************************************
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.sysdatabasess
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
#dblistd
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
#dblistd
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
#dblistd
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