Switching off every SQL Agent job for maintenance, then turning back on only the ones that were on

#The trap

"Disable all jobs" is easy. "Enable all jobs" afterwards also switches on the jobs that were deliberately disabled: the old archive job that deletes data, the job for a decommissioned app. Always save the state first.

#1. Save the current state

USE DBA;   -- your utility database
IF OBJECT_ID('dbo.JobStateSnapshot') IS NULL
  CREATE TABLE dbo.JobStateSnapshot (
    snapshot_name sysname, job_id uniqueidentifier, job_name sysname,
    enabled tinyint, taken_at datetime2 DEFAULT SYSDATETIME(),
    PRIMARY KEY (snapshot_name, job_id));

DECLARE @snap sysname = N'Patch-2026-10-25';

INSERT dbo.JobStateSnapshot (snapshot_name, job_id, job_name, enabled)
SELECT @snap, job_id, name, enabled FROM msdb.dbo.sysjobs;

SELECT enabled, COUNT(*) FROM dbo.JobStateSnapshot WHERE snapshot_name = @snap GROUP BY enabled;

#2. Disable everything (except what must keep running)

DECLARE @snap sysname = N'Patch-2026-10-25', @job_id uniqueidentifier;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
  SELECT job_id FROM dbo.JobStateSnapshot
  WHERE snapshot_name = @snap AND enabled = 1
    AND job_name NOT IN (N'DBA - Log Backups');     -- keep log backups during long work
OPEN c; FETCH NEXT FROM c INTO @job_id;
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 0;
  FETCH NEXT FROM c INTO @job_id;
END
CLOSE c; DEALLOCATE c;

Use sp_update_job, not UPDATE msdb.dbo.sysjobs. The procedure tells Agent about the change straight away.

#3. Stop jobs that are already running

Disabling doesn't stop a running job:

SELECT j.name, a.start_execution_date
FROM msdb.dbo.sysjobactivity a JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
WHERE a.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
  AND a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL;

-- Decide per job: wait for it, or stop it
EXEC msdb.dbo.sp_stop_job @job_name = N'ETL - Load Sales';

#4. Restore exactly the saved state

DECLARE @snap sysname = N'Patch-2026-10-25', @job_id uniqueidentifier, @en tinyint;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
  SELECT s.job_id, s.enabled
  FROM dbo.JobStateSnapshot s
  JOIN msdb.dbo.sysjobs j ON j.job_id = s.job_id         -- skip jobs deleted meanwhile
  WHERE s.snapshot_name = @snap AND j.enabled <> s.enabled;
OPEN c; FETCH NEXT FROM c INTO @job_id, @en;
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = @en;
  FETCH NEXT FROM c INTO @job_id, @en;
END
CLOSE c; DEALLOCATE c;

#5. Verify

SELECT s.job_name, s.enabled AS was, j.enabled AS now
FROM dbo.JobStateSnapshot s JOIN msdb.dbo.sysjobs j ON j.job_id = s.job_id
WHERE s.snapshot_name = N'Patch-2026-10-25' AND s.enabled <> j.enabled;

An empty result means every job is back as it was.

#Alternatives

Warning

After a migration cut-over, run the restore on the new server and keep jobs disabled on the old one, or both servers will run the same ETL against the same targets.

#Checklist

• Snapshot saved with a clear name
• Jobs disabled; exceptions documented
• Running jobs handled
• State restored and verified