SQL Server Agent schedules can be managed independently of jobs. This means you can update the schedule directly without updating the job itself.

You can do this with the sp_update_schedule stored procedure.

Example

Create the Schedule

First, let’s create a schedule:

EXEC sp_add_schedule 
    @schedule_name = N'Weekly_Sat_1AM',
    @freq_type = 8,
    @freq_interval = 64,
    @freq_recurrence_factor = 1,
    @active_start_time = 10000;

We can now use sp_update_schedule to modify that schedule.

Update the Schedule

Here’s an example of updating the schedule we just created.

EXEC sp_update_schedule 
    @name = N'Weekly_Sat_1AM',
    @new_name = N'Weekly_Sun_4AM',
    @freq_type = 8,
    @freq_interval = 1,
    @freq_recurrence_factor = 1,
    @active_start_time = 40000;

In this case, I changed the name of the schedule, as well as the frequency interval and the start time.

Note that sp_update_schedule changes only those settings for which parameter values are supplied. If a parameter is omitted, the current setting is retained.

Therefore, since some of our settings remained the same (specifically, @freq_type and @freq_recurrence_factor), we could have omitted those parameters altogether.

Like this:

EXEC sp_update_schedule 
    @name = N'Weekly_Sat_1AM',
    @new_name = N'Weekly_Sun_4AM',
    @freq_interval = 1,
    @active_start_time = 40000;

Disable a Schedule

You can set @enabled to 0 to disable a schedule.

Example:

EXEC sp_update_schedule 
    @name = N'Weekly_Sun_4AM',
    @new_name = N'Weekly_Sun_4AM (DISABLED)',
    @enabled = 0;

In this example I also changed the name to make it apparent that the job is disabled.

Check the Schedule

You can use sp_help_schedule to view the current settings of a schedule.

You can use it with or without parameters. When used without parameters, it returns all schedules.

EXEC sp_help_schedule;

If you only want to see the details of one schedule, you can provide the schedule’s name or ID.

Like this:

EXEC sp_help_schedule 
	@schedule_name = 'Weekly_Sun_4AM';

Although, when providing the name, you’ll need to remember the last name that you gave the schedule. In our case, if we run this procedure after the previous example (when we disabled the schedule), we would need to do this:

EXEC sp_help_schedule 
	@schedule_name = 'Weekly_Sun_4AM (DISABLED)';

However, the schedule’s ID remains constant, so you could use that regardless of the schedule’s name.

Example:

EXEC sp_help_schedule 
	@schedule_id = 16;

When using the ID, you can omit the parameter name if you wish.

Example:

EXEC sp_help_schedule 16;

What Happens to Jobs that Use the Modified Schedule?

All jobs that use the schedule immediately use the new settings. However, changing a schedule does not stop jobs that are currently running.

Full Syntax

The full syntax of sp_update_schedule goes like this:

sp_update_schedule   
    {   [ @schedule_id = ] schedule_id   
      | [ @name = ] 'schedule_name' }  
    [ , [ @new_name = ] new_name ]  
    [ , [ @enabled = ] enabled ]  
    [ , [ @freq_type = ] freq_type ]  
    [ , [ @freq_interval = ] freq_interval ]   
    [ , [ @freq_subday_type = ] freq_subday_type ]   
    [ , [ @freq_subday_interval = ] freq_subday_interval ]   
    [ , [ @freq_relative_interval = ] freq_relative_interval ]   
    [ , [ @freq_recurrence_factor = ] freq_recurrence_factor ]   
    [ , [ @active_start_date = ] active_start_date ]   
    [ , [ @active_end_date = ] active_end_date ]   
    [ , [ @active_start_time = ] active_start_time ]   
    [ , [ @active_end_time = ] active_end_time ]   
    [ , [ @owner_login_name = ] 'owner_login_name' ]  
    [ , [ @automatic_post =] automatic_post ]

See Microsoft’s documentation for sp_update_schedule for an explanation of each parameter, as well as the values that each accepts.