How to generate scripts to disable a SQL Agent Job

To disable a SQL Agent job you can right click on it and choose Disable. The trouble is if you want to disable a large number of them at once this can be time consuming and a nuisance.

You can also disable a job by running the following command:

EXEC msdb.dbo.sp_update_job @job_name = 'Your job name'

       ,@enabled = 0

You can get 'Your job name' from msdb.dbo.sysjobs so all we need to do is query that table and plug the result into the script below which will generate a list of scripts we can use:

USE [msdb]

GO

SELECT  

'EXEC msdb.dbo.sp_update_job @job_id=N''' + CONVERT(NVARCHAR(36), job_id) + ''',

              @enabled=0

GO'

, name

FROM msdb.dbo.sysjobs

WHERE name LIKE 'LSRestore_%'

In this case I wanted to return all the jobs that are involved with a log ship restore. The results returned look like this:

Click to enlarge

To generate the script to enable all the jobs, simply replace @enabled=0 with @enabled=1

[This piece of code is ideal for a Snippet]