Schedule Script for SQL Jobs

Question

How to schedule a SQL Jobs using SQL Script.I have around 80 SQL jobs. I want to schedule all my SQL jobs and get details of Job scheduled jobs using script.

SELECT * 
FROM [msdb].[dbo].[sysjobschedules]

The above query only gives me list of schedule jobs, but does not provide any way to schedule a SQL Job. I am looking for a generalized script to Schedule SQL job in Database. How do I do it?

Answer 1

try this

USE msdb ;
GO

EXEC sp_add_schedule
    @schedule_name = N'NightlyJobs' ,
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 010000 ;
GO

EXEC sp_attach_schedule
   @job_name = N'BackupDatabase',
   @schedule_name = N'NightlyJobs' ;
GO

EXEC sp_attach_schedule
   @job_name = N'RunReports',
   @schedule_name = N'NightlyJobs' ;
GO

sp_add_schedule

simple-way-to-create-a-sql-server-job-using-tsql

generate-sql-agent-job-schedule-report

create-MS-SQL-Server-agent-jobs-and-schedule

Answer 2

I would use this built-in proc to do this:

sp_add_schedule

Here is what the proc does:

1) Creates a schedule with the specified parameters and returns the schedule id

2) Attaches the schedule to an existing job.

Sample Code:

declare @start_date varchar(8) = convert(varchar(8), DATEADD(day,1,GETDATE()), 112)

EXEC sp_add_schedule
   @schedule_name = N'SomeName',
   @freq_type = 1,
   @active_start_date = @start_date,
   @active_start_time = 010000; 

Also, you could use this query to find your current job schedules info:

SELECT * FROM  msdb.dbo.sysschedules;

Hope this helps!

Answer 3

sorry, misread the question.

how about something like that:

EXEC msdb.dbo.sp_add_jobschedule @job_id=N'yourjobid',@name=N'TestSchedule1', 
   @enabled=1, 
   @freq_type=8, -- 4 - daily, 8 - weekly
   @freq_interval=2, --for weekly jobs 2 is Monday, 4 is Tuesday etc.
   @freq_subday_type=1, 
   @freq_subday_interval=0, 
   @freq_relative_interval=0, 
   @freq_recurrence_factor=1, 
   @active_start_date=20150729, 
   @active_start_time=0, 
   schedule_id=@schedule_id
GO

or use sp_add_schedule for SQL Server 2014 and 2016. SQL 2012 and below - use sp_add_jobschedule