Another take on SQL Server backups

Part of my job description requires that I perform sysadmin / DBA functions, one of which is database backups. SQL Server makes database backups easy by way of maintenance plans and such, but I was never quite satisfied with the arrangement. I wanted something more… automatic. Most of our projects are short lived applications which means I spend more time than the average DBA creating and dropping databases and, as a result, editing the backup jobs. To fix this, I created a couple of stored procedures:

Backup Databases

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[BackupDatabases]    Script Date: 10/10/2009 14:08:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[BackupDatabases] as

declare @Name varchar(50);          -- database name
declare @Model varchar(50);         -- recovery model
declare @Path varchar(256);         -- path for backup files
declare @DbFile varchar(256);       -- filename for db backup
declare @LogFile varchar(256);      -- filename for log backup
declare @FileDate varchar(20);      -- used for file name

set @Path = '\\elser-web3\Backups\Elser-db2\'
set @FileDate = convert(varchar(20),getdate(),112)

declare db_cursor cursor for
	select [name], [recovery_model_desc] from master.sys.databases
	where name <> 'tempdb';

open db_cursor;
fetch next from db_cursor
     into @Name, @Model;

while @@fetch_status = 0
begin
    set @DbFile = @Path + @Name + '_' + @FileDate + '.bak';
    set @LogFile = @Path + @Name + '_' + @FileDate + '.trn';
    backup database @Name to disk = @DbFile;

	if @Model = 'FULL'
	begin
		backup log @Name to disk = @LogFile;
	end

    dbcc shrinkdatabase (@Name, 10);
    fetch next from db_cursor into @Name, @Model;
end

close db_cursor
deallocate db_cursor

Backup Transaction Logs:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[BackupTxLogs]    Script Date: 10/10/2009 14:11:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[BackupTxLogs] as 

declare @Name varchar(50);          -- database name
declare @Path varchar(256);         -- path for backup files
declare @LogFile varchar(256);      -- filename for log backup
declare @FileDate varchar(20);      -- used for file name

set @Path = '\\elser-web3\Backups\Elser-db2\'
set @FileDate = convert(varchar(20),getdate(),112)

declare db_cursor cursor for
	select [name] from master.sys.databases
	where name <> 'tempdb' and recovery_model_desc = 'FULL'

open db_cursor;
fetch next from db_cursor
     into @Name;

while @@fetch_status = 0
begin
    set @LogFile = @Path + @Name + '_' + @FileDate + '.trn';
	backup log @Name to disk = @LogFile;
    fetch next from db_cursor into @Name;
end

close db_cursor
deallocate db_cursor