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. <\/p>\n
\/*\r\nScript commands to disable\/enable jobs\r\n5\/20\/2014 JCD\r\nTested: 2005, 2008 R2 and 2012 SP1\r\n\r\nYou can further filter jobs with other sysjobs columns to specify\r\nan even smaller subset of enabled jobs. Good examples for adding\r\nfilers are owner_sid, description and category_id.\r\n\r\n*\/\r\nSET NOCOUNT ON\r\nGO\r\n\r\nUSE msdb\r\nGO\r\n-- Script disable commands for enabled jobs\r\nSELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=0;'\r\nFROM msdb..sysjobs\r\nWHERE enabled = 1;\r\nGO\r\n-- Script enable commands to enabled jobs after disabling. Should be run when creating the disable script\r\nSELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=1;'\r\nFROM msdb..sysjobs\r\nWHERE enabled = 1;\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Quick way to script disable and enable SQL Agent jobs.<\/p>\n