SQL Agent jobs schedules

Question

Hi everybody,

I have about 30 SQL Server Agent jobs. I want every job (except for a few not ours) to have the schedule named SCHED_ + name of the job. Some jobs already have schedule named this way, some use (and share) the schedule named differently. In case they use and share a schedule I need to make a copy of the current schedule with the aforementioned name.

Is there a way to automate such process? I don't want to manually create new schedules matching original and unfortunately the 'Copy' button is missing when I look at the schedule for the job in its properties.

What can I do? [Going to start on the manual process for now while waiting for response]

Thanks in advance.

Answer 1

Try this:

USE [msdb];
GO

SELECT 'SCHED_' + j.name
--UPDATE s
--SET s.name = 'SCHED_' + j.name
  FROM [dbo].[sysjobschedules] AS js
INNER JOIN [dbo].[sysschedules] AS s ON s.schedule_id = js.schedule_id
INNER JOIN [dbo].[sysjobs] AS j ON j.job_id = js.job_id
WHERE j.name NOT IN ('NOT_MY_JOB_01', 'NOT_MY_JOB_02') 
AND s.name NOT LIKE 'SCHED_%'
AND j.enabled = 1;