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