When using T-SQL, you can use the sp_add_schedule stored procedure to add a SQL Server Agent schedule.

This procedure creates the schedule, but it doesn’t attach it to any jobs. To attach it to a job, you must use the sp_attach_schedule procedure.

You can also use sp_add_jobschedule to create the schedule and attach it all with the same procedure.

Example of the sp_add_schedule Procedure

Here’s an example that uses sp_add_schedule to create a new schedule:

EXEC sp_add_schedule 
    @schedule_name = N'RunDailyAM',
    @freq_type = 4,
    @freq_interval = 4,
    @active_start_time = 041000;
GO

This stored procedure accepts more arguments than i’ve included in this example, but these are some of the more common ones. See the tables below for an outline of the accepted values.

@schedule_name is a required argument, but the others are optional, and have default settings in case you don’t provide them.

We can now use sp_attach_schedule to attach that schedule to an existing job:

EXEC sp_attach_schedule  
   @job_name = N'BackupPetHotelDB',  
   @schedule_name = N'RunDailyAM';  
GO

When you use sp_attach_schedule, you must provide the name of the job or its ID (but not both), as well as the schedule name or ID (but not both).

Example of the sp_add_jobschedule Procedure

Here’s an example that uses sp_add_jobschedule to create a new schedule and attach it to a job, all in one go:

EXEC sp_add_jobschedule 
    @job_name = N'BackupMusicDB',
    @name = N'Weekly_Sun_3AM',
    @freq_type = 8,
    @freq_interval = 1,
    @freq_recurrence_factor = 1,
    @active_start_time = 30000;

With this procedure the @job_name argument provides the job name, and the @name argument provides the name that you want to give to the schedule.

Parameter Values

The way schedule data is stored in the msdb database makes it a little bit tricky when it comes to scheduling jobs with T-SQL.

Date/time values are stored as int types. Frequency type is expressed as an int, as is the interval values, and various other options.

Below is an overview of the accepted values for the parameters provided in the above examples.

@freq_type

This argument specifies when a job is to be executed. It accepts the following values:

Value	Description
1	Once
4	Daily
8	Weekly
16	Monthly
32	Monthly, relative to freq_interval
64	Run when SQL Agent service starts
128	Run when the computer is idle

@freq_interval

This argument specifies the days that a job is executed.

This argument depends on the value of the @freq_type argument, as outlined in the following table:

Value of freq_type	Effect on freq_interval
1 (once)	freq_interval is unused.
4 (daily)	Every freq_interval days.
8 (weekly)	freq_interval is one or more of the following (combined with an OR logical operator): 1 (Sunday) 2 (Monday) 4 (Tuesday) 8 (Wednesday) 16 (Thursday) 32 (Friday) 64 (Saturday)
16 (monthly)	On the freq_interval day of the month.
32 (monthly relative)	freq_interval is one of the following: 1 (Sunday) 2 (Monday) 3 (Tuesday) 4 (Wednesday) 5 (Thursday) 6 (Friday) 7 (Saturday) 8 (Day) 9 (Weekday) 10 (Weekend day)
64 (when SQLServerAgent service starts)	freq_interval is unused.
128	freq_interval is unused.

@freq_recurrence_factor

This specifies the number of weeks or months between the scheduled execution of a job.

This is int, with a default of 0, and is used only if freq_type is 8, 16, or 32.

@active_start_time

Specifies the time on any day between the @active_start_date and the @active_end_date arguments to begin execution of a job. @active_start_time is int, with a default of 000000, which indicates 12:00:00 A.M. on a 24-hour clock, and must be entered using the form HHMMSS.

@active_start_date

Although not included in my examples above, the @active_start_date argument allows you to specify the date on which execution of a job can begin. This is int, with a default of NULL, which indicates today’s date. The date is formatted as YYYYMMDD.

@active_end_date

You can also provide an @active_end_date argument, which specifies the date on which execution of a job can stop.

@active_end_date is int, with a default of 99991231, which indicates December 31, 9999. The date is formatted as YYYYMMDD.

More Info

See Microsoft’s documentation for sp_add_schedule and/or Microsoft’s documentation for sp_add_jobschedule for more details on the various arguments and their accepted values.