/*********************************************************/
/* Job requires use of xp_create_subdir and xp_delete_file to
create backup directories and delete old files */
/*********************************************************/
 
-- All Databases excluding (non-read only and sample) databases are backed up.
-- Expired backups are deleted
 
-- Backups of master, model and msdb are written to a 'System' folder
-- Backups of user databases are written to folders named after the databases
 
-- Get names of eligible databases (excluding tempdb and sample databases)
 
declare databases_to_backup cursor
FOR
SELECT name FROM master..sysdatabases
WHERE name NOT IN ('NorthWind','Pubs', 'AdventureWorks', 'tempdb')
AND (STATUS & 1024) <> 1024
ORDER BY name
FOR READ only
go
 
SET nocount ON
 
declare @db_name varchar(50),
@master_backup_dir varchar(255),
@specific_backup_dir varchar(255),
@backup_date char(8),
@backup_time char(4),
@filename varchar(255),
@retain_days tinyint,
@delete_date datetime
 
-- Specify high level backup directory and number of days' backups to keep on disk
SET @master_backup_dir = 'D:\data\backup\sql2005\'
set @retain_days = 14
 
-- Open the cursor and retrieve the first database name
open databases_to_backup
fetch databases_to_backup into @db_name
 
while (@@fetch_status = 0)
begin
-- Build a datetime string to uniquely identify the backup
set @backup_date = convert(char(8),getdate(),112)
set @backup_time = right('00' + convert(varchar(2),datepart(hh,getdate())),2) + right('00' + convert(varchar(2),datepart(mi,getdate())),2)
 
-- Build the specific backup directory path
if @db_name in ('master','model','msdb')
set @specific_backup_dir = @master_backup_dir + 'System\'
else
set @specific_backup_dir = @master_backup_dir + @db_name + '\'
 
-- Verify the backup directory exists
exec master.dbo.xp_create_subdir @specific_backup_dir
 
-- Build a unique filename and backup the database
set @filename = @specific_backup_dir + @db_name + '_db_' + @backup_date + @backup_time + '.bak'
BACKUP DATABASE @db_name TO DISK = @filename
 
-- Delete files older than specified retain days
set @delete_date = dateadd(d, (@retain_days * -1) , getdate())
exec xp_delete_file 0, @specific_backup_dir, N'bak', @delete_date
 
-- Get the next database name
fetch databases_to_backup into @db_name
end
 
set nocount off
 
close databases_to_backup
deallocate databases_to_backup