Large auto-job-creation script for adding backups

CREATE PROCEDURE sp_insBackupJobs
  @db_name varchar(128)
AS

DECLARE @diffJob binary(16),
@fullJob binary(16),
@tlogJob binary(16),
@rtnCode int,
@timeOfDay int,
@WeekNum tinyint,
@DayNum tinyint,
@db_diff varchar(140),
@db_full varchar(140),
@db_tlog varchar(140),
@mkDir varchar(255),
@backupDevice varchar(255),
@cmdString varchar(1000)

SET @timeOfDay = 050000 /* default 5am; HHMMSS */SET @backupDevice = 'Default' /* set this to the name of the backup device you want to use */SET @WeekNum = DATENAME(wk,GetDate())%4+1
SET @DayNum = CASE
  WHEN msdb.dbo.fn_wkNumber(GetDate())<4 THEN 1
  WHEN msdb.dbo.fn_wkNumber(GetDate())>3 THEN 2
END

SELECT @mkDir = LEFT(phyname,LEN(phyname)-CHARINDEX('\',REVERSE(phyname)))
FROM master.dbo.sysdevices
WHERE (name = @backupDevice)

SET @mkDir = 'IF NOT EXIST "' + @mkDir + '\' + @db_name + '" (MD "' + @mkDir + '\' + @db_name + '")'
EXEC xp_cmdshell @mkDir


BEGIN TRAN
BEGIN
/* hourly log rotations */SET @db_tlog = @db_name + ' trans log backup' /* name for transaction log backups */EXECUTE @rtnCode = msdb.dbo.sp_add_job
@job_name = @db_tlog,
@enabled = 0, /* disable the job until the steps are all created */@description = 'Make periodic transaction log backups',
@category_name = 'Backups - trans log rotation',
@job_id = @tlogJob OUTPUT 
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
EXECUTE @rtnCode = msdb.dbo.sp_add_jobschedule
@job_id = @tlogJob,
@name = @db_tlog,
@freq_type = 4, /* run job daily */@freq_interval = 1,
@freq_subday_interval = 3,/* run job every three ... */@freq_subday_type = 0x8/* hours */IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure

SET @cmdString = 'BACKUP LOG [' + @db_name + ']
  TO DISK=''' + @db_name + '\TransLog_1.bak''
  WITH
NAME = N''' + @db_name + ' Trans log backup; rotation 1'',
INIT,
SKIP,
NOUNLOAD,
NOFORMAT'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @tlogJob,
@step_id = 1,
@step_name = 'Step 1',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 9
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP LOG [' + @db_name + ']
  TO DISK=''' + @db_name + '\TransLog_2.bak''
  WITH
NAME = N''' + @db_name + ' Trans log backup; rotation 2'',
INIT,
SKIP,
NOUNLOAD,
NOFORMAT'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @tlogJob,
@step_id = 2,
@step_name = 'Step 2',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 9
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP LOG [' + @db_name + ']
  TO DISK=''' + @db_name + '\TransLog_3.bak''
  WITH
NAME = N''' + @db_name + ' Trans log backup; rotation 3'',
INIT,
SKIP,
NOUNLOAD,
NOFORMAT'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @tlogJob,
@step_id = 3,
@step_name = 'Step 3',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 9
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP LOG [' + @db_name + ']
  TO DISK=''' + @db_name + '\TransLog_4.bak''
  WITH
NAME = N''' + @db_name + ' Trans log backup; rotation 4'',
INIT,
SKIP,
NOUNLOAD,
NOFORMAT'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @tlogJob,
@step_id = 4,
@step_name = 'Step 4',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 9
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP LOG [' + @db_name + ']
  TO DISK=''' + @db_name + '\TransLog_5.bak''
  WITH
NAME = N''' + @db_name + ' Trans log backup; rotation 5'',
INIT,
SKIP,
NOUNLOAD,
NOFORMAT'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @tlogJob,
@step_id = 5,
@step_name = 'Step 5',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 9
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP LOG [' + @db_name + ']
  TO DISK=''' + @db_name + '\TransLog_6.bak''
  WITH
NAME = N''' + @db_name + ' Trans log backup; rotation 6'',
INIT,
SKIP,
NOUNLOAD,
NOFORMAT'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @tlogJob,
@step_id = 6,
@step_name = 'Step 6',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 9
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP LOG [' + @db_name + ']
  TO DISK=''' + @db_name + '\TransLog_7.bak''
  WITH
NAME = N''' + @db_name + ' Trans log backup; rotation 7'',
INIT,
SKIP,
NOUNLOAD,
NOFORMAT'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @tlogJob,
@step_id = 7,
@step_name = 'Step 7',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 9
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP LOG [' + @db_name + ']
  TO DISK=''' + @db_name + '\TransLog_8.bak''
  WITH
NAME = N''' + @db_name + ' Trans log backup; rotation 8'',
INIT,
SKIP,
NOUNLOAD,
NOFORMAT'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @tlogJob,
@step_id = 8,
@step_name = 'Step 8',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 9
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure


SET @cmdString = 'DECLARE @sID tinyint
SET @sID = DATENAME(hh,GetDate())%8+1
EXECUTE msdb.dbo.sp_update_job
@job_name = ''' + @db_tlog + ''',
@start_step_id = @sID'

EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @tlogJob,
@step_name = 'Step 9: ''goto'' pointer',
@step_id = 9,
@command = @cmdString
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
EXECUTE @rtnCode = msdb.dbo.sp_add_jobserver
@job_id = @tlogJob,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
EXECUTE @rtnCode = msdb.dbo.sp_update_job
@job_name = @db_tlog,
@start_step_id = 1,
@enabled=1
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure






/* daily rotations */SET @db_diff = @db_name + ' differential'
EXECUTE @rtnCode = msdb.dbo.sp_add_job
@job_name = @db_diff,
@enabled = 0, /* disable the job until the steps are all created */@description = 'Alternating differential backups',
@category_name = 'Backups - daily differential',
@job_id = @diffJob OUTPUT 
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
EXECUTE @rtnCode = msdb.dbo.sp_add_jobschedule
@job_id = @diffJob,
@name = @db_diff,
@freq_type = 8, /* run job weekly */@freq_interval = 126, /* run job every 64|32|16|8|4|2 */@freq_recurrence_factor = 1,
@active_start_time = @timeOfDay
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP DATABASE [' + @db_name + ']
  TO DISK=''' + @db_name + '\Differential_1.bak''
  WITH
DIFFERENTIAL,
NAME = N''' + @db_name + ' Differential backup; rotation 1'',
INIT,
SKIP,
NOUNLOAD,
NOFORMAT'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @diffJob,
@step_id = 1,
@step_name = 'Step 1: Mon,Tue,Wed',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 3
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP DATABASE [' + @db_name + ']
  TO DISK=''' + @db_name + '\Differential_2.bak''
  WITH
DIFFERENTIAL,
NAME = N''' + @db_name + ' Differential backup; rotation 2'',
INIT,
SKIP,
NOUNLOAD,
NOFORMAT'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @diffJob,
@step_id = 2,
@step_name = 'Step 2: Thu,Fri,Sat',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 3
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'DECLARE @sID tinyint
SET @sID = 
CASE
  WHEN msdb.dbo.fn_wkNumber(GetDate())<4 THEN 1
  WHEN msdb.dbo.fn_wkNumber(GetDate())>3 THEN 2
END
EXECUTE msdb.dbo.sp_update_job
@job_name = ''' + @db_diff + ''',
@start_step_id = @sID'

EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @diffJob,
@step_name = 'Step 3: ''goto'' pointer',
@step_id = 3,
@command = @cmdString
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
EXECUTE @rtnCode = msdb.dbo.sp_add_jobserver
@job_id = @diffJob,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
EXECUTE @rtnCode = msdb.dbo.sp_update_job
@job_name = @db_diff,
@start_step_id = @DayNum,
@enabled=1
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure






/* weekly rotations */SET @db_full = @db_name + ' Full backup'
EXECUTE @rtnCode = msdb.dbo.sp_add_job
@job_name = @db_full,
@enabled = 0, /* disable the job until the steps are all created */@description = 'Complete backup on a four week rotation',
@category_name = 'Backups - weekly rotation',
@job_id = @fullJob OUTPUT 
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
EXECUTE @rtnCode = msdb.dbo.sp_add_jobschedule
@job_id = @fullJob,
@name = @db_full,
@freq_type = 8, /* run job weekly */@freq_interval = 1, /* run job every Sunday */@freq_recurrence_factor = 1,
@active_start_time = @timeOfDay
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP DATABASE [' + @db_name + ']
  TO DISK=''' + @db_name + '\Full_1.bak''
  WITH
NAME = N''' + @db_name + ' Full backup; rotation 1'',
INIT,
NOSKIP,
NOUNLOAD,
NOFORMAT,
RETAINDAYS = 28'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @fullJob,
@step_id = 1,
@step_name = 'Step 1',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 5
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP DATABASE [' + @db_name + ']
  TO DISK=''' + @db_name + '\Full_2.bak''
  WITH
NAME = N''' + @db_name + ' Full backup; rotation 2'',
INIT,
NOSKIP,
NOUNLOAD,
NOFORMAT,
RETAINDAYS = 28'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @fullJob,
@step_id = 2,
@step_name = 'Step 2',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 5
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP DATABASE [' + @db_name + ']
  TO DISK=''' + @db_name + '\Full_3.bak''
  WITH
NAME = N''' + @db_name + ' Full backup; rotation 3'',
INIT,
NOSKIP,
NOUNLOAD,
NOFORMAT,
RETAINDAYS = 28'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @fullJob,
@step_id = 3,
@step_name = 'Step 3',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 5
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'BACKUP DATABASE [' + @db_name + ']
  TO DISK=''' + @db_name + '\Full_4.bak''
  WITH
NAME = N''' + @db_name + ' Full backup; rotation 4'',
INIT,
NOSKIP,
NOUNLOAD,
NOFORMAT,
RETAINDAYS = 28'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @fullJob,
@step_id = 4,
@step_name = 'Step 4',
@database_name = @db_name,
@command = @cmdString,
@on_success_action = 4, /* trigger 'go to' */@on_success_step_id = 5
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
SET @cmdString = 'DECLARE @sID tinyint
SET @sID = DATENAME(wk,DATEADD(wk,1,GetDate()))%4+1
EXECUTE msdb.dbo.sp_update_job
@job_name = ''' + @db_full + ''',
@start_step_id = @sID'
EXECUTE @rtnCode = msdb.dbo.sp_add_jobstep
@job_id = @fullJob,
@step_name = 'Step 5: ''goto'' pointer',
@step_id = 5,
@command = @cmdString
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
EXECUTE @rtnCode = msdb.dbo.sp_add_jobserver
@job_id = @fullJob,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
EXECUTE @rtnCode = msdb.dbo.sp_update_job
@job_name = @db_full,
@start_step_id = @WeekNum,
@enabled=1
IF (@@ERROR <> 0 OR @rtnCode <> 0) GOTO QuitFailure
END
COMMIT TRAN
GOTO QuitSuccess

QuitFailure:  /*process failed; rollback transaction*/  IF (@@TRANCOUNT > 0) ROLLBACK TRAN

QuitSuccess:  /*graceful exit*/





/* one time
EXECUTE msdb.dbo.sp_add_category @name = N'Backups - weekly rotation'
EXECUTE msdb.dbo.sp_add_category @name = N'Backups - daily differential'
EXECUTE msdb.dbo.sp_add_category @name = N'Backups - trans log rotation'

CREATE FUNCTION dbo.fn_wkNumber
  (@date_in smalldatetime)
RETURNS tinyint

BEGIN
  DECLARE @wkNum tinyint

  SET @wkNum=
    CASE DATENAME(dw,@date_in)
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
WHEN 'Sunday' THEN 7
    END

  RETURN @wkNum
END */