Disable (or enable) all SQL Server Agent jobs

I was doing some heavy lifting for a customer recently... it was after hours, and I needed to disable a few Agent jobs beforehand, to be sure I didn't run into anything. This post is the cursor I used to first disable the jobs, and then re-enable them afterward. Very simple, you can see it's just based on an IN list:

/* Use this to disable multiple SQL Server Agent jobs in one action. */

USE msdb;

DECLARE

     @job VARCHAR(128),

     @success INT,

     @error INT,

     @sql NVARCHAR(255)

     /* declare cursor to disable sysjobs based on the IN list */

     DECLARE DisableJobCursor CURSOR FOR

     SELECT sj.name FROM msdb..sysjobs sj

     WHERE sj.name NOT IN('AFewJobs','YouMayWant','ToIgnore') -- change as needed

     AND enabled = 1

     SELECT @success = 0

     SELECT @error = 0

     OPEN DisableJobCursor

     FETCH NEXT FROM DisableJobCursor

     INTO @job

     WHILE (@@FETCH_STATUS <> -1)

     BEGIN

       IF (@@FETCH_STATUS <> -2)

       BEGIN

        SELECT @sql='EXEC dbo.sp_update_job @job_name = N'''+@job+''',@enabled = 0;'

        EXECUTE sp_executesql @sql

        SELECT @success = @success + 1

       END

       NextRecord:

       FETCH NEXT FROM DisableJobCursor

       INTO @Job

     END

     CLOSE DisableJobCursor

     DEALLOCATE DisableJobCursor

     SELECT 'Disabled Jobs = ' + CONVERT(VARCHAR(10),@success)

     SELECT 'Failures = ' + CONVERT(VARCHAR(10),@error)

If you're working with a lot of jobs, that IN list can become a pain. In that case we could go in more collectively by using the job category. For example, let's say we want to disable all jobs in the 'Database Maintenance' category.  Just use @category in your cursor declaration, instead of the IN list, like this:

    DECLARE

       @job VARCHAR(128),

       @jobcategory  VARCHAR(25) = 'Database Maintenance',

       @sql VARCHAR(255)

       DECLARE DisableJobCursor CURSOR FOR

       SELECT sj.name FROM msdb..sysjobs sj INNER JOIN msdb..syscategories sc

         ON sj.category_id = sc.category_id

       WHERE sc.name = @jobcategory

       AND enabled = 1  

Another option, though a bit reckless, is just to go in for all enabled jobs.  This approach let's you disable/enable en masse:

       DECLARE DisableJobCursor CURSOR FOR

       SELECT sj.name FROM msdb..sysjobs sj

       WHERE sj.enabled = 1

You want to be careful with that last approach... who knows how many jobs you have?!

I would be remiss if I don't caution you about cursor usage. In most cases, I am pretty anti-cursor, but... that's not what this post is about.  I will give you an excellent reference for cursor examples, performance considerations, and alternatives. Don't ignore.  This is an excellent read.

    http://www.brentozar.com/sql-syntax-examples/cursor-example/