SQL: When doing maintenance disable all jobs…

One of the things we have found when doing maintenance is to make sure no jobs kick off in the middle of our manual maintenance tasks.

SELECT * INTO msdb.dbo.sysjobs_temp FROM msdb.dbo.sysjobs
UPDATE msdb.dbo.sysjobs SET enabled = 0

Then, when you are finished:

update sj
set sj.enabled = sjt.enabled
from msdb.dbo.sysjobs sj join msdb.dbo.sysjobs_temp sjt
on sj.job_id = sjt.job_id


Related posts:

1. SQL Server: Sorting job steps
2. SQL Server DBA Harvests: Permissions
3. I will lose 2.5 jobs a month?
4. SQL Server: Server List Table