The proper way to disable/enable SQLServer Agent Jobs

Question

I have a number of SQLServer agent scheduled jobs, one of them performs a full database backup. I want to disable some other jobs when backup begins and re-enable them once backup is done. What is the right way to do so? I was thinking about adding one of the following tsql commands to the first step of the backup task (and respective enable commands to the last step), but I cannot find which one is better (or maybe there is another way).

UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] IN (....)

Or a number of EXEC dbo.sp_update_job ?
Thanks.

Answer 1

Definitely use sp_update_job. If the job is already scheduled, then manipulating the sysjobs table directly won't necessarily cause the cached schedule to be re-calculated.

It might work for the ENABLED flag (haven't tried it), but I know for a fact that it doesn't work for columns like start_step_id.

Answer 2

You'd have to run EXEC dbo.sp_update_job because you can't update system tables directly (although I'm not sure if sysjobs still counts as a system table Mitch says it can be updated)

I would consider the use of sp_getapplock and sp_releaseapplock to "lock" other jobs out without actually updating the jobs though.

Answer 3

I would use sp_update_job as it encapsulates reusable piece of logic that is supported. Why re-invent the wheel.

http://msdn.microsoft.com/en-us/library/ms188745.aspx