SQL Stuff

Here’s How To Mass Update Agent Job Schedules

Here’s some logic I wrote up which allows you to update a particular Job schedule across multiple servers. It’s designed to find the Job, Pull the Job ID and Schedule ID for that particular Job (provided is has only one schedule attached to it), then it updates it to run nightly excluding Saturday Night, Sunday Night. The Jobs runs at 12:00am (midnight). it does run on Saturday, but remember 12:00am on Saturday is the first hour of the day so what happens is the Job is checking the backups from the night before (Friday Night). You don’t need to check backups for the weekends because that’s the maintenance window where backups are typically not taken.

use master;
set nocount on

-- update job to run every night at 12:00am excluding saturday night, sunday night, and monday night.
declare	@update_job_schedule	varchar(max)
set		@update_job_schedule	= ''
select	@update_job_schedule	= @update_job_schedule +
'use msdb;' + char(10) +
'exec msdb.dbo.sp_attach_schedule @job_id=''' + cast(sj.job_id as varchar(255)) + ''',@schedule_id=' + cast(sjsched.schedule_id as varchar(255)) + char(10) + 
'exec msdb.dbo.sp_update_schedule @schedule_id=' + cast(sjsched.schedule_id as varchar(255)) + ', @freq_interval=124' + char(10)
	msdb..sysjobs sj join msdb..sysjobsteps sjs on sj.job_id = sjs.job_id
	join msdb..sysjobschedules sjsched on sj.job_id = sjsched.job_id
	sj.name = 'SEND SQL BACKUP ALERTS'  --> put your job name here.

exec	(@update_job_schedule)

Categories: SQL Stuff

1 reply »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s