Script Commands to Disable/Enable SQL Agent Jobs

The script below is a quick way to create disable and enable SQL Agent job commands for existing jobs. It will script out the correct ‘EXEC msdb..sp_update_job’ statements. In this example, the where clause filters for “enabled = 1”, but you can use it create statements based of various job characteristics. We tend to use these when migrating to new servers and want to leave the old server in place.

/*
Script commands to disable/enable jobs
5/20/2014 JCD
Tested: 2005, 2008 R2 and 2012 SP1

You can further filter jobs with other sysjobs columns to specify
an even smaller subset of enabled jobs. Good examples for adding
filers are owner_sid, description and category_id.

*/
SET NOCOUNT ON
GO

USE msdb
GO
-- Script disable commands for enabled jobs
SELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=0;'
FROM msdb..sysjobs
WHERE enabled = 1;
GO
-- Script enable commands to enabled jobs after disabling. Should be run when creating the disable script
SELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=1;'
FROM msdb..sysjobs
WHERE enabled = 1;
GO
Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Tiny URL for this post:
 

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

WordPress Anti Spam by WP-SpamShield