In SQL Server, you can use the sp_update_job stored procedure to modify an existing SQL Server Agent job.

Example

In this example, we create a job, then we use sp_update_job to modify that job.

Create the Job

First, create the job:

USE msdb;  
GO  
EXEC sp_add_job  
    @job_name = N'BakMovies',
    @description = N'Backup the Movies database.',
    @category_name = 'Database Maintenance';
GO  
EXEC sp_add_jobstep  
    @job_name = N'BakMovies',  
    @step_name = N'Run the backup',  
    @subsystem = N'TSQL',  
    @command = N'BACKUP DATABASE [Movies] TO DISK = N''C:\Backups\Movies.bak'' WITH CHECKSUM',
    @retry_attempts = 5,  
    @retry_interval = 5;
GO
EXEC sp_add_schedule 
    @schedule_name = N'Run_Sat_5AM',
    @freq_type = 8,
    @freq_interval = 64,
    @freq_recurrence_factor = 1,
    @active_start_time = 050000;
GO  
EXEC sp_attach_schedule  
   @job_name = N'BakMovies',  
   @schedule_name = N'Run_Sat_5AM';
GO  
EXEC sp_add_jobserver  
    @job_name = N'BakMovies';
GO

The above code uses five stored procedures but the sp_update_job procedure only updates those details that were created with the sp_add_job procedure. The other procedures have their own matching “update” procedure.

So just to be clear, the following part is the part that we can update with the sp_update_job procedure.

EXEC sp_add_job  
    @job_name = N'BakMovies',
    @description = N'Backup the Movies database.',
    @category_name = 'Database Maintenance';

In this case we only provided three arguments when creating the job. The procedure actually accepts a lot more than that, but the only required argument is the job’s name.

Update the Job

We can now use sp_update_job to modify the above job.

EXEC sp_update_job  
    @job_name = N'BakMovies',
    @new_name = N'BackupMoviesDB',
    @description = N'Backup the Movies database.',
    @category_name = 'Database Maintenance';

The only thing this example does is change the name of the job.

The sp_update_job stored procedure changes only those settings for which parameter values are supplied. If a parameter is omitted, the current setting is retained.

Therefore, in this instance, we could have omitted the description and category name:

EXEC sp_update_job  
    @job_name = N'BakMovies',
    @new_name = N'BackupMoviesDB';

Disable a Job

You can disable a job by setting @enabled to 0:

EXEC sp_update_job  
    @job_name = N'BackupMoviesDB',
    @new_name = N'BackupMoviesDB (DISABLED)',
    @enabled = 0;

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

Check the Job

You can use sp_help_job to view the current settings of a job.

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

EXEC sp_help_job;

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

Like this:

EXEC sp_help_job 
	@job_name = 'BackupMoviesDB';

Here’s what the result looks like when executing it in SSMS:

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

EXEC sp_help_job 
	@job_name = 'BackupMoviesDB (DISABLED)';

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

Example:

EXEC sp_help_job 
	@job_id = '158D714B-6686-48FD-BB41-D35C6EE70AED';

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

Example:

EXEC sp_help_job '158D714B-6686-48FD-BB41-D35C6EE70AED';

What Happens to the Job Steps, Schedule, etc?

Modifying a job doesn’t break that job’s steps and schedule information. A job’s steps and schedule will remain intact after you make any modifications.

If you need to modify the steps or schedule, you’ll need to use the appropriate stored procedure for them (for example, use sp_update_schedule to update its schedule and sp_update_jobstep to update its job step).

Full Syntax

The full syntax of sp_update_job goes like this:

sp_update_job [ @job_id =] job_id | [@job_name =] 'job_name'  
     [, [@new_name =] 'new_name' ]   
     [, [@enabled =] enabled ]  
     [, [@description =] 'description' ]   
     [, [@start_step_id =] step_id ]  
     [, [@category_name =] 'category' ]   
     [, [@owner_login_name =] 'login' ]  
     [, [@notify_level_eventlog =] eventlog_level ]  
     [, [@notify_level_email =] email_level ]  
     [, [@notify_level_netsend =] netsend_level ]  
     [, [@notify_level_page =] page_level ]  
     [, [@notify_email_operator_name =] 'operator_name' ]  
     [, [@notify_netsend_operator_name =] 'netsend_operator' ]  
     [, [@notify_page_operator_name =] 'page_operator' ]  
     [, [@delete_level =] delete_level ]   
     [, [@automatic_post =] automatic_post ]

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