Large auto-job-creation script for adding backups

,

This script creates a stored proc that was intended to run from a trigger on the master..sysdatabases for each database creation. Alas. It will create three jobs for each database you apply it to. First, it'll create a job to run a full backup each Sun, at 5am (see below). Those backups will be retained for four weeks before being replaced. Next, it'll schedule a differential every night Mon-Wed, then a second for Thu-Sat. Finally, there's a trans log backup every three hours; each of which is not overwritten (eight files rotated).

You can edit the time that it's run by setting @timeOfDay, and you'll need to adjust @backupDevice to match your system. Also, there are four items you'll need to run once on your system before creation of the stored proc. The function is intended to be added to the msdb database; be sure to add permissions for 'public'.

This is my first 'professional' submission; I hope it's useful, and I appreciate any feedback/improvements.

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 */

Rate

Share

Share

Rate