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