Skip Navigation LinksDiscussions List : Shcedule Maintenance Job using script

Started: 8/24/2019 4:30 PM
Picture Placeholder: Zahid Ahamed
Picture Placeholder: Zahid Ahamed
  • Zahid Ahamed

### Shcedule Maintenance Job using script

It is really tedious and time consuming to set schedule for maintenance job using GUI. Here is the following script to set schedule for maintenance plan.

SET NOCOUNT ON

IF SERVERPROPERTY('EngineEdition') <> 4

BEGIN

 -- All the variables needed for creating the schedules and adding them to the jobs


 DECLARE @SchOwner      nvarchar(max)

 DECLARE @SchName01     nvarchar(max), @SchName02     nvarchar(max), @SchName03     nvarchar(max), @SchName04     nvarchar(max), @SchName05     nvarchar(max), @SchName06     nvarchar(max), @SchName07     nvarchar(max), @SchName08     nvarchar(max)

 DECLARE @SchFreqType01 int,           @SchFreqType02 int,           @SchFreqType03 int,           @SchFreqType04 int,           @SchFreqType05 int,           @SchFreqType06 int,           @SchFreqType07 int,           @SchFreqType08 int

 DECLARE @SchFreqInt01  int,           @SchFreqInt02  int,           @SchFreqInt03  int,           @SchFreqInt04  int,           @SchFreqInt05  int,           @SchFreqInt06  int,           @SchFreqInt07  int,           @SchFreqInt08  int

 DECLARE @SchSubType01  int,           @SchSubType02  int,           @SchSubType03  int,           @SchSubType04  int,           @SchSubType05  int,           @SchSubType06  int,           @SchSubType07  int,           @SchSubType08  int

 DECLARE @SchSubInt01   int,           @SchSubInt02   int,           @SchSubInt03   int,           @SchSubInt04   int,           @SchSubInt05   int,           @SchSubInt06   int,           @SchSubInt07   int,           @SchSubInt08   int

 DECLARE @SchRelInt01   int,           @SchRelInt02   int,           @SchRelInt03   int,           @SchRelInt04   int,           @SchRelInt05   int,           @SchRelInt06   int,           @SchRelInt07   int,           @SchRelInt08   int

 DECLARE @SchFreqRec01  int,           @SchFreqRec02  int,           @SchFreqRec03  int,           @SchFreqRec04  int,           @SchFreqRec05  int,           @SchFreqRec06  int,           @SchFreqRec07  int,           @SchFreqRec08  int

 DECLARE @SchStart01    int,           @SchStart02    int,           @SchStart03    int,           @SchStart04    int,           @SchStart05    int,           @SchStart06    int,           @SchStart07    int,           @SchStart08    int

 DECLARE @SchEnd01      int,           @SchEnd02      int,           @SchEnd03      int,           @SchEnd04      int,           @SchEnd05      int,           @SchEnd06      int,           @SchEnd07      int,           @SchEnd08      int

 DECLARE @JobName01     nvarchar(max), @JobName02     nvarchar(max), @JobName03     nvarchar(max), @JobName04     nvarchar(max), @JobName05     nvarchar(max), @JobName06     nvarchar(max), @JobName07     nvarchar(max), @JobName08     nvarchar(max), @JobName09 nvarchar(max), @JobName10 nvarchar(max), @JobName11 nvarchar(max)

 -- Job Names

 SET @JobName01 = 'Maintenance - Backups - SYSTEM - FULL'

 SET @JobName02 = 'Maintenance - Backups - USER - FULL'

 SET @JobName03 = 'Maintenance - Backups - USER - DIFF'

 SET @JobName04 = 'Maintenance - Backups - USER - LOG'

 SET @JobName05 = 'Maintenance - IndexOptimize - USER'

 SET @JobName06 = 'Maintenance - DatabaseIntegrityCheck - USER'

 SET @JobName07 = 'Maintenance - DatabaseIntegrityCheck - SYSTEM'

 SET @JobName08 = 'Maintenance - CommandLog Cleanup'

 SET @JobName09 = 'Maintenance - Output File Cleanup'

 SET @JobName10 = 'Maintenance - sp_delete_backuphistory'

 SET @JobName11 = 'Maintenance - sp_purge_jobhistory'

 -- Set SA account as schedule owner

 SET @SchOwner  = SUSER_SNAME(0x01)

 -- Schedule Names

 SET @SchName01 = 'Production System Full Backups - Daily 9:30PM'

 SET @SchName02 = 'Production User Full Backups - Daily 12AM'

 SET @SchName03 = 'Production User Diff Backups - MTWThFSa 12AM'

 SET @SchName04 = 'Production User TLog Backups - Hourly 2AM-10PM'

 SET @SchName05 = 'Production Index Maintenance - TThSa 10PM'

 SET @SchName06 = 'Production User CheckDB - MWF 10PM'

 SET @SchName07 = 'Production System CheckDB - Daily 9PM'

 SET @SchName08 = 'Production Cleanup - Sunday 11PM'

 -- Frequency Type: 1 = Once, 4 = Daily, 8 = Weekly, 16 = Monthly, 32 = Monthly relativity to freq_interval, 64 = When the agent starts, 128 = When idle

 -- Frequency Interval, depends on freq_type:

  -- 1  : Unused

  -- 4  : Every specified number of days

  -- 8  : Logical OR, 1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday

  -- 16 : On the specified day of the month

  -- 32 : 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday, 8 = Day, 9 = Weekday, 10 = Weekend day

  -- 64 : Unused

  -- 128: Unused

 -- Frequency Subday Type, units for freq_subday_interval: 1 = At the specified time, 2 = Seconds, 4 = Minutes, 8 = Hours

 -- Frequency Subday Interval, number of freq_subday_type periods to occur between each execution

 -- Frequency Relative Interval, only used for freq_interval of 32: 1 = First, 2 = Second, 4 = Third, 8 = Fourth, 16 = Last

 -- Frequency Recurrence Factor, only used for freq_type of 8, 16, or 32: Number of weeks or months between each execution

 -- Start Time on the 24 hr clock

 -- End Time on the 24 hr clock

 SELECT @SchFreqType01 = 4,      @SchFreqType02 = 4,      @SchFreqType03 = 8,      @SchFreqType04 = 4,      @SchFreqType05 = 8,      @SchFreqType06 = 8,      @SchFreqType07 = 4,      @SchFreqType08 = 8

 SELECT @SchFreqInt01  = 1,      @SchFreqInt02  = 1,      @SchFreqInt03  = 126,    @SchFreqInt04  = 1,      @SchFreqInt05  = 84,     @SchFreqInt06  = 42,     @SchFreqInt07  = 1,      @SchFreqInt08  = 1

 SELECT @SchSubType01  = 1,      @SchSubType02  = 1,      @SchSubType03  = 1,      @SchSubType04  = 8,      @SchSubType05  = 1,      @SchSubType06  = 1,      @SchSubType07  = 1,      @SchSubType08  = 1

 SELECT @SchSubInt01   = 0,      @SchSubInt02   = 0,      @SchSubInt03   = 0,      @SchSubInt04   = 1,      @SchSubInt05   = 0,      @SchSubInt06   = 0,      @SchSubInt07   = 0,      @SchSubInt08   = 0

 SELECT @SchRelInt01   = 0,      @SchRelInt02   = 0,      @SchRelInt03   = 0,      @SchRelInt04   = 0,      @SchRelInt05   = 0,      @SchRelInt06   = 0,      @SchRelInt07   = 0,      @SchRelInt08   = 0

 SELECT @SchFreqRec01  = 1,      @SchFreqRec02  = 1,      @SchFreqRec03  = 1,      @SchFreqRec04  = 1,      @SchFreqRec05  = 1,      @SchFreqRec06  = 1,      @SchFreqRec07  = 1,      @SchFreqRec08  = 1

 SELECT @SchStart01    = 213000, @SchStart02    = 0,      @SchStart03    = 0,      @SchStart04    = 20000,  @SchStart05    = 220000, @SchStart06    = 220000, @SchStart07    = 210000, @SchStart08    = 230000

 SELECT @SchEnd01      = 235959, @SchEnd02      = 235959, @SchEnd03      = 235959, @SchEnd04      = 215959, @SchEnd05      = 235959, @SchEnd06      = 235959, @SchEnd07      = 235959, @SchEnd08      = 235959

 -- Create Schedules

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName01) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName01, @enabled = 1, @freq_type = @SchFreqType01, @freq_interval = @SchFreqInt01, @freq_subday_type = @SchSubType01, @freq_subday_interval = @SchSubInt01, @freq_relative_interval = @SchRelInt01, @freq_recurrence_factor = @SchFreqRec01, @active_start_time = @SchStart01, @active_end_time = @SchEnd01, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName02) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName02, @enabled = 1, @freq_type = @SchFreqType02, @freq_interval = @SchFreqInt02, @freq_subday_type = @SchSubType02, @freq_subday_interval = @SchSubInt02, @freq_relative_interval = @SchRelInt02, @freq_recurrence_factor = @SchFreqRec02, @active_start_time = @SchStart02, @active_end_time = @SchEnd02, @owner_login_name = @SchOwner;

 -- IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName03) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName03, @enabled = 1, @freq_type = @SchFreqType03, @freq_interval = @SchFreqInt03, @freq_subday_type = @SchSubType03, @freq_subday_interval = @SchSubInt03, @freq_relative_interval = @SchRelInt03, @freq_recurrence_factor = @SchFreqRec03, @active_start_time = @SchStart03, @active_end_time = @SchEnd03, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName04) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName04, @enabled = 1, @freq_type = @SchFreqType04, @freq_interval = @SchFreqInt04, @freq_subday_type = @SchSubType04, @freq_subday_interval = @SchSubInt04, @freq_relative_interval = @SchRelInt04, @freq_recurrence_factor = @SchFreqRec04, @active_start_time = @SchStart04, @active_end_time = @SchEnd04, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName05) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName05, @enabled = 1, @freq_type = @SchFreqType05, @freq_interval = @SchFreqInt05, @freq_subday_type = @SchSubType05, @freq_subday_interval = @SchSubInt05, @freq_relative_interval = @SchRelInt05, @freq_recurrence_factor = @SchFreqRec05, @active_start_time = @SchStart05, @active_end_time = @SchEnd05, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName06) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName06, @enabled = 1, @freq_type = @SchFreqType06, @freq_interval = @SchFreqInt06, @freq_subday_type = @SchSubType06, @freq_subday_interval = @SchSubInt06, @freq_relative_interval = @SchRelInt06, @freq_recurrence_factor = @SchFreqRec06, @active_start_time = @SchStart06, @active_end_time = @SchEnd06, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName07) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName07, @enabled = 1, @freq_type = @SchFreqType07, @freq_interval = @SchFreqInt07, @freq_subday_type = @SchSubType07, @freq_subday_interval = @SchSubInt07, @freq_relative_interval = @SchRelInt07, @freq_recurrence_factor = @SchFreqRec07, @active_start_time = @SchStart07, @active_end_time = @SchEnd07, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName08) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName08, @enabled = 1, @freq_type = @SchFreqType08, @freq_interval = @SchFreqInt08, @freq_subday_type = @SchSubType08, @freq_subday_interval = @SchSubInt08, @freq_relative_interval = @SchRelInt08, @freq_recurrence_factor = @SchFreqRec08, @active_start_time = @SchStart08, @active_end_time = @SchEnd08, @owner_login_name = @SchOwner;

 -- Assign Schedules to Jobs

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName01) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName01, @schedule_name = @SchName01;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName02) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName02, @schedule_name = @SchName02;

 -- IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName03) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName03, @schedule_name = @SchName03;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName04) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName04, @schedule_name = @SchName04;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName05) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName05, @schedule_name = @SchName05;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName06) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName06, @schedule_name = @SchName06;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName07) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName07, @schedule_name = @SchName07;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName08) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName08, @schedule_name = @SchName08;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName09) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName09, @schedule_name = @SchName08;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName10) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName10, @schedule_name = @SchName08;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName11) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName11, @schedule_name = @SchName08;

 

END

 

Please let me know if you have any questions.

 

Zahid

Picture Placeholder: Zahid Ahamed
  • Zahid Ahamed
/_layouts/15/images/person.gif" alt="Picture Placeholder: Zahid Ahamed" />
Zahid Ahamed

It is really tedious and time consuming to set schedule for maintenance job using GUI. Here is the following script to set schedule for maintenance plan.

SET NOCOUNT ON

IF SERVERPROPERTY('EngineEdition') <> 4

BEGIN

 -- All the variables needed for creating the schedules and adding them to the jobs


 DECLARE @SchOwner      nvarchar(max)

 DECLARE @SchName01     nvarchar(max), @SchName02     nvarchar(max), @SchName03     nvarchar(max), @SchName04     nvarchar(max), @SchName05     nvarchar(max), @SchName06     nvarchar(max), @SchName07     nvarchar(max), @SchName08     nvarchar(max)

 DECLARE @SchFreqType01 int,           @SchFreqType02 int,           @SchFreqType03 int,           @SchFreqType04 int,           @SchFreqType05 int,           @SchFreqType06 int,           @SchFreqType07 int,           @SchFreqType08 int

 DECLARE @SchFreqInt01  int,           @SchFreqInt02  int,           @SchFreqInt03  int,           @SchFreqInt04  int,           @SchFreqInt05  int,           @SchFreqInt06  int,           @SchFreqInt07  int,           @SchFreqInt08  int

 DECLARE @SchSubType01  int,           @SchSubType02  int,           @SchSubType03  int,           @SchSubType04  int,           @SchSubType05  int,           @SchSubType06  int,           @SchSubType07  int,           @SchSubType08  int

 DECLARE @SchSubInt01   int,           @SchSubInt02   int,           @SchSubInt03   int,           @SchSubInt04   int,           @SchSubInt05   int,           @SchSubInt06   int,           @SchSubInt07   int,           @SchSubInt08   int

 DECLARE @SchRelInt01   int,           @SchRelInt02   int,           @SchRelInt03   int,           @SchRelInt04   int,           @SchRelInt05   int,           @SchRelInt06   int,           @SchRelInt07   int,           @SchRelInt08   int

 DECLARE @SchFreqRec01  int,           @SchFreqRec02  int,           @SchFreqRec03  int,           @SchFreqRec04  int,           @SchFreqRec05  int,           @SchFreqRec06  int,           @SchFreqRec07  int,           @SchFreqRec08  int

 DECLARE @SchStart01    int,           @SchStart02    int,           @SchStart03    int,           @SchStart04    int,           @SchStart05    int,           @SchStart06    int,           @SchStart07    int,           @SchStart08    int

 DECLARE @SchEnd01      int,           @SchEnd02      int,           @SchEnd03      int,           @SchEnd04      int,           @SchEnd05      int,           @SchEnd06      int,           @SchEnd07      int,           @SchEnd08      int

 DECLARE @JobName01     nvarchar(max), @JobName02     nvarchar(max), @JobName03     nvarchar(max), @JobName04     nvarchar(max), @JobName05     nvarchar(max), @JobName06     nvarchar(max), @JobName07     nvarchar(max), @JobName08     nvarchar(max), @JobName09 nvarchar(max), @JobName10 nvarchar(max), @JobName11 nvarchar(max)

 -- Job Names

 SET @JobName01 = 'Maintenance - Backups - SYSTEM - FULL'

 SET @JobName02 = 'Maintenance - Backups - USER - FULL'

 SET @JobName03 = 'Maintenance - Backups - USER - DIFF'

 SET @JobName04 = 'Maintenance - Backups - USER - LOG'

 SET @JobName05 = 'Maintenance - IndexOptimize - USER'

 SET @JobName06 = 'Maintenance - DatabaseIntegrityCheck - USER'

 SET @JobName07 = 'Maintenance - DatabaseIntegrityCheck - SYSTEM'

 SET @JobName08 = 'Maintenance - CommandLog Cleanup'

 SET @JobName09 = 'Maintenance - Output File Cleanup'

 SET @JobName10 = 'Maintenance - sp_delete_backuphistory'

 SET @JobName11 = 'Maintenance - sp_purge_jobhistory'

 -- Set SA account as schedule owner

 SET @SchOwner  = SUSER_SNAME(0x01)

 -- Schedule Names

 SET @SchName01 = 'Production System Full Backups - Daily 9:30PM'

 SET @SchName02 = 'Production User Full Backups - Daily 12AM'

 SET @SchName03 = 'Production User Diff Backups - MTWThFSa 12AM'

 SET @SchName04 = 'Production User TLog Backups - Hourly 2AM-10PM'

 SET @SchName05 = 'Production Index Maintenance - TThSa 10PM'

 SET @SchName06 = 'Production User CheckDB - MWF 10PM'

 SET @SchName07 = 'Production System CheckDB - Daily 9PM'

 SET @SchName08 = 'Production Cleanup - Sunday 11PM'

 -- Frequency Type: 1 = Once, 4 = Daily, 8 = Weekly, 16 = Monthly, 32 = Monthly relativity to freq_interval, 64 = When the agent starts, 128 = When idle

 -- Frequency Interval, depends on freq_type:

  -- 1  : Unused

  -- 4  : Every specified number of days

  -- 8  : Logical OR, 1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday

  -- 16 : On the specified day of the month

  -- 32 : 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday, 8 = Day, 9 = Weekday, 10 = Weekend day

  -- 64 : Unused

  -- 128: Unused

 -- Frequency Subday Type, units for freq_subday_interval: 1 = At the specified time, 2 = Seconds, 4 = Minutes, 8 = Hours

 -- Frequency Subday Interval, number of freq_subday_type periods to occur between each execution

 -- Frequency Relative Interval, only used for freq_interval of 32: 1 = First, 2 = Second, 4 = Third, 8 = Fourth, 16 = Last

 -- Frequency Recurrence Factor, only used for freq_type of 8, 16, or 32: Number of weeks or months between each execution

 -- Start Time on the 24 hr clock

 -- End Time on the 24 hr clock

 SELECT @SchFreqType01 = 4,      @SchFreqType02 = 4,      @SchFreqType03 = 8,      @SchFreqType04 = 4,      @SchFreqType05 = 8,      @SchFreqType06 = 8,      @SchFreqType07 = 4,      @SchFreqType08 = 8

 SELECT @SchFreqInt01  = 1,      @SchFreqInt02  = 1,      @SchFreqInt03  = 126,    @SchFreqInt04  = 1,      @SchFreqInt05  = 84,     @SchFreqInt06  = 42,     @SchFreqInt07  = 1,      @SchFreqInt08  = 1

 SELECT @SchSubType01  = 1,      @SchSubType02  = 1,      @SchSubType03  = 1,      @SchSubType04  = 8,      @SchSubType05  = 1,      @SchSubType06  = 1,      @SchSubType07  = 1,      @SchSubType08  = 1

 SELECT @SchSubInt01   = 0,      @SchSubInt02   = 0,      @SchSubInt03   = 0,      @SchSubInt04   = 1,      @SchSubInt05   = 0,      @SchSubInt06   = 0,      @SchSubInt07   = 0,      @SchSubInt08   = 0

 SELECT @SchRelInt01   = 0,      @SchRelInt02   = 0,      @SchRelInt03   = 0,      @SchRelInt04   = 0,      @SchRelInt05   = 0,      @SchRelInt06   = 0,      @SchRelInt07   = 0,      @SchRelInt08   = 0

 SELECT @SchFreqRec01  = 1,      @SchFreqRec02  = 1,      @SchFreqRec03  = 1,      @SchFreqRec04  = 1,      @SchFreqRec05  = 1,      @SchFreqRec06  = 1,      @SchFreqRec07  = 1,      @SchFreqRec08  = 1

 SELECT @SchStart01    = 213000, @SchStart02    = 0,      @SchStart03    = 0,      @SchStart04    = 20000,  @SchStart05    = 220000, @SchStart06    = 220000, @SchStart07    = 210000, @SchStart08    = 230000

 SELECT @SchEnd01      = 235959, @SchEnd02      = 235959, @SchEnd03      = 235959, @SchEnd04      = 215959, @SchEnd05      = 235959, @SchEnd06      = 235959, @SchEnd07      = 235959, @SchEnd08      = 235959

 -- Create Schedules

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName01) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName01, @enabled = 1, @freq_type = @SchFreqType01, @freq_interval = @SchFreqInt01, @freq_subday_type = @SchSubType01, @freq_subday_interval = @SchSubInt01, @freq_relative_interval = @SchRelInt01, @freq_recurrence_factor = @SchFreqRec01, @active_start_time = @SchStart01, @active_end_time = @SchEnd01, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName02) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName02, @enabled = 1, @freq_type = @SchFreqType02, @freq_interval = @SchFreqInt02, @freq_subday_type = @SchSubType02, @freq_subday_interval = @SchSubInt02, @freq_relative_interval = @SchRelInt02, @freq_recurrence_factor = @SchFreqRec02, @active_start_time = @SchStart02, @active_end_time = @SchEnd02, @owner_login_name = @SchOwner;

 -- IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName03) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName03, @enabled = 1, @freq_type = @SchFreqType03, @freq_interval = @SchFreqInt03, @freq_subday_type = @SchSubType03, @freq_subday_interval = @SchSubInt03, @freq_relative_interval = @SchRelInt03, @freq_recurrence_factor = @SchFreqRec03, @active_start_time = @SchStart03, @active_end_time = @SchEnd03, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName04) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName04, @enabled = 1, @freq_type = @SchFreqType04, @freq_interval = @SchFreqInt04, @freq_subday_type = @SchSubType04, @freq_subday_interval = @SchSubInt04, @freq_relative_interval = @SchRelInt04, @freq_recurrence_factor = @SchFreqRec04, @active_start_time = @SchStart04, @active_end_time = @SchEnd04, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName05) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName05, @enabled = 1, @freq_type = @SchFreqType05, @freq_interval = @SchFreqInt05, @freq_subday_type = @SchSubType05, @freq_subday_interval = @SchSubInt05, @freq_relative_interval = @SchRelInt05, @freq_recurrence_factor = @SchFreqRec05, @active_start_time = @SchStart05, @active_end_time = @SchEnd05, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName06) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName06, @enabled = 1, @freq_type = @SchFreqType06, @freq_interval = @SchFreqInt06, @freq_subday_type = @SchSubType06, @freq_subday_interval = @SchSubInt06, @freq_relative_interval = @SchRelInt06, @freq_recurrence_factor = @SchFreqRec06, @active_start_time = @SchStart06, @active_end_time = @SchEnd06, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName07) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName07, @enabled = 1, @freq_type = @SchFreqType07, @freq_interval = @SchFreqInt07, @freq_subday_type = @SchSubType07, @freq_subday_interval = @SchSubInt07, @freq_relative_interval = @SchRelInt07, @freq_recurrence_factor = @SchFreqRec07, @active_start_time = @SchStart07, @active_end_time = @SchEnd07, @owner_login_name = @SchOwner;

 IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName08) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName08, @enabled = 1, @freq_type = @SchFreqType08, @freq_interval = @SchFreqInt08, @freq_subday_type = @SchSubType08, @freq_subday_interval = @SchSubInt08, @freq_relative_interval = @SchRelInt08, @freq_recurrence_factor = @SchFreqRec08, @active_start_time = @SchStart08, @active_end_time = @SchEnd08, @owner_login_name = @SchOwner;

 -- Assign Schedules to Jobs

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName01) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName01, @schedule_name = @SchName01;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName02) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName02, @schedule_name = @SchName02;

 -- IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName03) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName03, @schedule_name = @SchName03;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName04) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName04, @schedule_name = @SchName04;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName05) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName05, @schedule_name = @SchName05;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName06) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName06, @schedule_name = @SchName06;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName07) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName07, @schedule_name = @SchName07;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName08) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName08, @schedule_name = @SchName08;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName09) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName09, @schedule_name = @SchName08;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName10) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName10, @schedule_name = @SchName08;

 IF EXISTS     (SELECT * FROM msdb.dbo.sysjobs      WHERE [name] = @JobName11) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName11, @schedule_name = @SchName08;

 

END

 

Please let me know if you have any questions.

 

Zahid

08/24/2019 4:30 PM8/24/2019 4:30 PMNoGeneral
0
8/18/2019 10:40 PM
There are no items to show in this view of the "Discussions List" discussion board.