How to disable and enable all the Jobs in SQL Server

Disable and enable all the SQL jobs at one time

I have found in many of the blogs where it has been advised to update the system table in msdb database to enable/disable the SQL job(s). This does not seem good to me. Microsoft recommends do not try to update the system table directly.

Here is the simple script that I have written to disable/enable all the SQL jobs at one time.

Disable the Jobs:

DECLARE @sql_command VARCHAR(MAX)

SET @sql_command=''
SELECT @sql_command=@sql_command+' EXEC msdb..sp_update_job @job_id='''+cast(job_id AS VARCHAR(100))+''',@enabled=0;' FROM msdb..sysjobs 
WHERE enabled=1
EXEC(@sql_command)

Enable the Jobs:

DECLARE @sql_command VARCHAR(MAX)

SET @sql_command=''
SELECT @sql_command=@sql_command+' EXEC msdb..sp_update_job @job_id='''+cast(job_id AS VARCHAR(100))+''',@enabled=1;' FROM msdb..sysjobs 
WHERE enabled=0
EXEC(@sql_command)

NOTE: Remember if you have to disable all the SQL jobs in a server, you can stop SQL server agent job as well. This will not allow any schedule jobs to run automatically. If this is not possible then you can use above script. If you have to disable some of the jobs you can expand the where clause. You can also use the LIKE keyword filter some of the jobs. Up to you to modify the script and get the required action done.

Hope this helps.