Stored Procedure to Backup all Databases

/*******************************************************************************************
usr_sp_backup_all_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 and Win2k
********************************************************************************************/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usr_sp_backup_all_db]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usr_sp_backup_all_db]
GO
--drop the old proc if it is there

create procedure usr_sp_backup_all_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 @full as varchar(255)
declare @diff 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 @diskfull as int
select @diskfull = 0

declare Database_Cursor cursor scroll for select name from sysdatabases where name <> 'tempdb' and name <> 'model' and name <> 'Northwind'
--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_db_yyyymmddhhmm.bak
--the _db_ tells me this is a full 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+'_db_'+@date+@time+'.BAK'
select @full = @bkvar+'_db_'+@date+@time
select @full = @full+'.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 = @full
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
--clean up the temp tables
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(@full)+' '+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
select @cmds = 'del '+rtrim(@full)
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