Backup Transaction Logs on all Databases

/*******************************************************************************************
usr_sp_backup_tran_db

by: Wesley D. Brown
Date 01/21/01
mod 08/30/01

This is the stored proc that I use to do full backups of my databases.
This script will backup locally then ship the file off via ftp and confirm that it is there.
It has some other basic error checking in it like drive space checking to make sure you 
don't blow up the server by filling up a drive.
Also, It will only delete files that are confirmed on the FTP site. If the file isn't there
it will not be deleted. You will recive an event. The script will run until the disk fills up
then you will get no more backups!

This stored procedure is designed to take 4 parameters
@bksrv: This is the name or the IP address of the spooler or backup server to send to.
@user: Login name for the FTP server
@upass: Password for the FTP server
@path: Local path files will be written to first.

This has only been tested under MS-SQL2k,MS-SQL 7,Win2k,WinNT4sp6a
********************************************************************************************/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usr_sp_backup_tran_db]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usr_sp_backup_tran_db]
GO
--drop it if it is already in the syscatalog

create procedure usr_sp_backup_tran_db @bksrv as varchar(255),
@user as varchar(100), @upass as varchar(100),@path as varchar(255) 
WITH RECOMPILE
-- we do with recompile because we are taking variables in and need to keep this
-- dynamic
as

--declare variables
declare @errnum as int
declare @db_size as decimal
declare @db_holder as varchar(10)
declare @db as varchar(255)
declare @bkvar as varchar(255)
declare @cmdstr as varchar(255)
declare @time as varchar(255)
declare @date as varchar(255)
declare @tran as varchar(255)
declare @bpath as varchar(255)
declare @errmsg as varchar(255)
declare @cmdmsg as varchar(255)
declare @cmds as varchar(255)
declare @fname as varchar(255)
declare @bk_drive as varchar(1)
declare @db_bk_mode as varchar(255)
declare @diskfull as int

select @diskfull = 0
--set disk full flag to no 
declare Database_Cursor cursor for select name from sysdatabases where name <> 'tempdb' and name <> 'model' and name <> 'Northwind' and name <> 'Master'
--get all the db names and load up a cursor
open Database_Cursor
--open up the cursor
fetch next from Database_Cursor into @db
--load the first db name
while @@fetch_status = 0
--while we have db names run the loop!
begin

set @cmdstr = 'md '+@path+@db
-- this is to make the local directories
--create a seperate directory for every database
exec @cmdmsg = master..xp_cmdshell @cmdstr, NO_OUTPUT
--Directories are not removed with the files. This makes it easier to
--recover a database with it's full/diff/trn logs all in one place

--build the path and db backup file name
--I use dbname_TLOG_yyyymmddhhmm.bak
--the _TLOG_ tells me this is a transaction log backup
select @bpath = rtrim(@path)+rtrim(@db)+'\'
select @date = convert(varchar,getdate(),112)
SELECT @time = REPLACE(convert(varchar,CURRENT_TIMESTAMP,114),':','')
select @time = LEFT(@time,4)
select @bkvar = rtrim(@bpath)+rtrim(@db)
select @fname = @db+'_TLOG_'+@date+@time+'.trn'
select @tran = @bkvar+'_TLOG_'+@date+@time
select @tran = @tran+'.trn'
select @bk_drive = LEFT(@path,1)

--these are the two temp tables I use to find out how much free space we have
--on the local server and if the database backup will fit on the local drive
create table #result (output varchar(255) null)
CREATE table #DriveTable (Drive varchar(10),[MB Free] int)
INSERT into #Drivetable Exec master..xp_fixeddrives 
CREATE table #helpdb (name varchar(100),db_size varchar(100),owner varchar(100),dbid varchar(100),created varchar(100),status varchar(255),compatibility_level varchar(100))
INSERT into #helpdb Exec master..sp_helpdb 
select @db_holder = (select db_size from #helpdb where name = @db)
select @db_size = cast(rtrim(ltrim(@db_holder))as decimal)+1

select @db_bk_mode =(select status from #helpdb where status like'%Recovery=SIMPLE%' and name = @db)
--find out if the DB can have a tlog backup performed this is just to cut down on errors
if @db_bk_mode <> ''
begin
drop table #result
drop table #DriveTable
drop table #helpdb
end
else
begin
if @db_size < (select [MB Free] from #DriveTable where drive = @bk_drive)
begin
--if it fits backup the db
BACKUP LOG  @db
TO DISK = @tran
end
else
begin
-- if it don't raise an error and move on to the next one
select @errmsg = 'Backup drive does not have enough space to complete'
raiserror (@errmsg,16,1)WITH LOG
select @diskfull = 1
end

select @errnum = @@ERROR
IF @errnum <> 0 or @diskfull = 1
begin
--if we get an error on the backup or if the disk is full err out
select @errmsg = 'The backup for '+@db+' failed with error '+convert(varchar,@errnum)+' please see logs for details'
raiserror (@errmsg,16,1)WITH LOG
drop table #result
drop table #DriveTable
drop table #helpdb
end
else
begin
--else build the ftp command file needed to ship the backup to the backup server
select @cmds = 'echo OPEN '+@bksrv+' > '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'echo USER '+@user+' >> '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'echo '+@upass+' >> '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'echo mkdir '+@@servername+' >> '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'echo cd '+@@servername+' >> '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'echo mkdir '+@db+' >> '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'echo cd '+@db+' >> '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'echo bin >> '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'echo send '+rtrim(@tran)+' '+rtrim(@fname)+' >> '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'echo ls >> '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'echo bye >> '+@bk_drive+':\ftpcmd.txt'
exec @cmdstr = master..xp_cmdshell @cmds

select @cmds = 'ftp -n -s:'+@bk_drive+':\ftpcmd.txt > '+@bk_drive+':\ftpout.txt'
exec master..xp_cmdshell @cmds

select @cmds = 'type '+@bk_drive+':\ftpout.txt'
insert #result (output) exec master..xp_cmdshell @cmds

select @cmds = 'del '+@bk_drive+':\ftpout.txt'
exec master..xp_cmdshell @cmds

if (select count(*) from #result where [output] like rtrim(@fname)+'%') > 0
begin
select @cmds = 'del '+rtrim(@tran)
exec @cmdstr = master..xp_cmdshell @cmds

end
--clean up the temp tables
drop table #result
drop table #DriveTable
drop table #helpdb

end 

--load the next db name
end

fetch next from Database_Cursor into @db
end

--clean up the cursor
close Database_Cursor
deallocate Database_Cursor