Differential Backups of all Databases

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.

/*******************************************************************************************
usr_sp_backup_diff_db

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

This is the stored proc that I use to do diff 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 and Win2k
********************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usr_sp_backup_diff_db]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usr_sp_backup_diff_db]
GO

create procedure usr_sp_backup_diff_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 @diff 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 @diskfull as int

select @diskfull = 0

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_DIFF_yyyymmddhhmm.bak
		--the _DIFF_ tells me this is a diff 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+'_DIFF_'+@date+@time+'.BAK'
		select @diff = @bkvar+'_DIFF_'+@date+@time
		select @diff = @diff+'.BAK'
		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 #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

			
		if @db_size < (select [MB Free] from #DriveTable where drive = @bk_drive)
			begin
				--if it fits backup the db
				BACKUP DATABASE @db
				TO DISK = @diff
				WITH 
				DIFFERENTIAL
			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(@diff)+' '+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

				create table #result (output varchar(255) null)

				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
						--debug code to let me know it was found
						--print 'file '+rtrim(@fname)+' found'
						select @cmds = 'del '+rtrim(@diff)
						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
		fetch next from Database_Cursor into @db
	end
--clean up the cursor
close Database_Cursor
deallocate Database_Cursor