Listing 1: The sp_backup_databases Procedure

CREATE proc sp_backup_databases @backupDir varchar(50)
as
declare @dbname sysname
declare @bckstmt varchar(100)
begin 
  create table #userdbs (name sysname)
  insert into #userdbs select name from sysdatabases where dbid > 6
  declare cdb cursor for select name from #userdbs 
  open cdb
  fetch cdb into @dbname
  while @@fetch_status = 0 
  begin 
    set @bckstmt = 'BackUp Database ' + @dbname + ' to ' +
      'Disk = ' + char(39) + @backupDir + '\' + 
      rtrim(ltrim(@dbname)) + '.bak' + char(39)
    exec (@bckstmt)
    fetch cdb into @dbname
  end 
  close cdb
  deallocate cdb
  drop table #userdbs
end
GO