I would recommend to start by creating a staging table that will hold some information for you. The table should be constructed to hold the data the below code outputs. I recommend to store the Schedule_ID, name, and job_id at the minimum. (If you want to cheat, you can expand the asterisks and select the columns you want, and then add the word INTO along with the database.schema.table_name_you_want_to_create and it will create the table for the first time for you. After that you can change it into an insert.)
SELECT * FROM
MSDB.dbo.sysschedules ss
INNER JOIN msdb.dbo.sysjobschedules jss
ON jss.schedule_id = ss.schedule_id
WHERE ss.enabled = 1
That code returns the schedules for all schedules and it also performs an inner join to limit the data returned to only schedules that are paired to jobs currently that are enabled.
You can then create a loop or cursor or something similar to loop through the staging table and execute the sp_update_schedule procedure. This will disable all schedules that are enabled and paired to a job. Once your maintenance is complete, you can run the loop once more, but this time enabling the schedules you had disabled earlier.
If you would like an example of a loop, you can see an example I have created in the past on stack overflow.
I have gone back and used the suggestions to create a complete code solution. Please see below, there are two scripts in total. The first will disable your jobs and the second will re-enable them. TEST THIS IN A TEST ENVIRONMENT BEFORE PUTTING IT IN PRODUCTION. Source for completeness.
Script one:
USE MSDB;
/*************************************************************
Checking for history table. Creating it if it doesn't exist.
*************************************************************/
IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[JobsEnabledTracker](
[Id] [INT] IDENTITY(1, 1) NOT NULL,
[job_id] [UNIQUEIDENTIFIER] NULL,
[schedule_id] [BIGINT] NULL,
[enabled] [BIT] NULL);
END;
IF EXISTS
(
SELECT
1
FROM [dbo].[JobsEnabledTracker]
WHERE [enabled] = 1
)
OR
(
SELECT
COUNT(*)
FROM [dbo].[JobsEnabledTracker]
) = 0
BEGIN
PRINT 'There are jobs enabled or there are no jobs yet populated in the history table.';
/***********************
Clear out history table
***********************/
PRINT 'Truncating history table: dbo.JobsEnabledTracker';
TRUNCATE TABLE [dbo].[JobsEnabledTracker];
PRINT 'Inserting records into history table: dbo.JobsEnabledTracker';
/******************************
Add in values to history table
******************************/
INSERT INTO [dbo].[JobsEnabledTracker]
(
[job_id],
[schedule_id],
[enabled]
)
SELECT
[jss].[job_id],
[jss].[schedule_id],
1 AS 'enabled'
FROM [msdb].[dbo].[sysschedules] AS [ss]
INNER JOIN [msdb].[dbo].[sysjobschedules] AS [jss] ON [jss].[schedule_id] = [ss].[schedule_id]
WHERE [ss].[enabled] = 1;
/**********************************************************************************
Table variable to hold schedules and jobs enabled. This is important for the loop.
**********************************************************************************/
DECLARE @JobsEnabled TABLE
([Id] INT
PRIMARY KEY IDENTITY(1, 1),
[job_id] UNIQUEIDENTIFIER,
[schedule_id] BIGINT,
[enabled] BIT
);
/*****************************************
Insert schedules that we need to disable.
*****************************************/
INSERT INTO @JobsEnabled
(
[job_id],
[schedule_id],
[enabled]
)
SELECT
[job_id],
[schedule_id],
[enabled]
FROM [dbo].[JobsEnabledTracker];
/********************************
Holds the job id and schedule id
********************************/
DECLARE @jobid UNIQUEIDENTIFIER;
DECLARE @scheduleid BIGINT;
/***********************************
Holds the ID of the row in the loop
***********************************/
DECLARE @ID INT= 0;
/**********************
Check if records exist
**********************/
IF EXISTS
(
SELECT
[Id]
FROM @JobsEnabled
)
BEGIN
PRINT 'Loop mode, jobs found enabled.';
/**********
Begin loop
**********/
WHILE(1 = 1)
BEGIN
/***************************************
Grab jobid, scheduleid, and id of rows.
***************************************/
SELECT
@jobid =
(
SELECT TOP 1
[job_id]
FROM @JobsEnabled
ORDER BY
[job_id]
);
SELECT
@scheduleid =
(
SELECT TOP 1
[schedule_id]
FROM @JobsEnabled
ORDER BY
[job_id]
);
SELECT
@ID =
(
SELECT TOP 1
[Id]
FROM @JobsEnabled
ORDER BY
[job_id]
);
/************************************
Re-enable schedule associated to job
************************************/
PRINT 'Disabling schedule_id: '+CAST(@scheduleid AS VARCHAR(255))+' paired to job_id: '+CAST(@jobid AS VARCHAR(255));
EXEC [sp_update_schedule]
@schedule_id = @scheduleid,
@enabled = 0;
/*********************
Removes row from loop
*********************/
DELETE FROM @JobsEnabled
WHERE
[Id] = @ID;
UPDATE [dbo].[JobsEnabledTracker]
SET
[enabled] = 0
WHERE
[job_id] = @jobid
AND [schedule_id] = @scheduleid;
/****************************
No more rows, stops deleting
****************************/
IF
(
SELECT
COUNT(*)
FROM @JobsEnabled
) <= 0
BEGIN
BREAK
END;
/********
End Loop
********/
END;
PRINT 'Exiting loop, disabling schedules paired to jobs complete.';
/**********
End elseif
**********/
END;
ELSE
BEGIN
PRINT 'All done';
END;
END;
ELSE
BEGIN
PRINT 'YOU HAVE JOBS STILL DISABLED, EXITING SCRIPT. PLEASE RUN SCRIPT TWO FIRST.';
END;
Script two
USE MSDB;
/*******************************************************************************
Check for history table. This physical table tells us what jobs we are going to
enable the scheduler for.
*******************************************************************************/
IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NOT NULL
BEGIN
IF EXISTS
(
SELECT 1
FROM [dbo].[JobsEnabledTracker]
WHERE [enabled] = 0
)
BEGIN
PRINT 'Jobs disabled in history table: dbo.JobsEnabledTracker found.';
/**********************************************************************************
Table variable to hold schedules and jobs enabled. This is important for the loop.
**********************************************************************************/
DECLARE @JobsEnabled TABLE
(
[Id] int PRIMARY KEY IDENTITY(1, 1)
, [job_id] uniqueidentifier
, [schedule_id] bigint
, [enabled] bit
);
/*******************************************************************************
Insert schedules that we had disabled that we need to go back in and re-enable.
*******************************************************************************/
INSERT INTO @JobsEnabled( [job_id], [schedule_id], [enabled] )
SELECT [job_id], [schedule_id], [enabled]
FROM [dbo].[JobsEnabledTracker];
/********************************
Holds the job id and schedule id
********************************/
DECLARE @jobid uniqueidentifier;
DECLARE @scheduleid bigint;
/***********************************
Holds the ID of the row in the loop
***********************************/
DECLARE @ID int= 0;
/**********************
Check if records exist
**********************/
IF EXISTS
(
SELECT [Id]
FROM @JobsEnabled
)
BEGIN
PRINT 'Loop mode, jobs found disabled.';
/**********
Begin loop
**********/
WHILE 1 = 1
BEGIN
/***************************************
Grab jobid, scheduleid, and id of rows.
***************************************/
SELECT @jobid =
(
SELECT TOP 1 [job_id]
FROM @JobsEnabled
ORDER BY [job_id]
);
SELECT @scheduleid =
(
SELECT TOP 1 [schedule_id]
FROM @JobsEnabled
ORDER BY [job_id]
);
SELECT @ID =
(
SELECT TOP 1 [Id]
FROM @JobsEnabled
ORDER BY [job_id]
);
/***************************************
Re-enable schedule associated to job
***************************************/
PRINT 'Enabling schedule_id: '+CAST(@scheduleid AS varchar(255))+' paired to job_id: '+CAST(@jobid AS varchar(255));
EXEC [sp_update_schedule] @schedule_id = @scheduleid, @enabled = 1;
/*********************
Removes row from loop
*********************/
DELETE FROM @JobsEnabled
WHERE [Id] = @ID;
/***********************
Set job back to enabled
***********************/
UPDATE [dbo].[JobsEnabledTracker]
SET [enabled] = 1
WHERE [job_id] = @jobid AND
[schedule_id] = @scheduleid;
/****************************
No more rows, stops deleting
****************************/
IF
(
SELECT COUNT(*)
FROM @JobsEnabled
) <= 0
BEGIN
BREAK;
END;
/********
End Loop
********/
END;
PRINT 'Exiting loop, enabling schedules paired to jobs complete.';
/**********
End elseif
**********/
END;
ELSE
BEGIN
PRINT 'All done';
END;
END;
ELSE
BEGIN
PRINT 'dbo.JobsEnabledTracker has no disabled jobs currently.';
END;
END;
ELSE
BEGIN
PRINT 'dbo.JobsEnabledTracker is NULL, you may need to run the first script to create and populate this table.';
END;
