How to bulk enable or disable SQL Server agent jobs ?

,

It often happens that you need to disable or enable all jobs in SQL Server Agent in one shot. Script below is useful when you have dozens or hundreds of jobs. It will help you you to avoid doing this work manually.

-- Set @enabled = 1 or @enabled = 0 
-- Run generated script
SELECT 'EXEC msdb.dbo.sp_update_job @job_name=N'''+s.NAME+''' , @enabled = 1'
FROM msdb..sysjobs s 
left join msdb.sys.syslogins l on s.owner_sid = l.sid

Once ran the script just copy and paste it`s results to SQL Server management studio script pane and execute.

 

 

Rate

Share

Share

Rate