This script has three actions:
1) Put Current Job Information Into Temp Table
2) DISABLE All SQL Server Agent Jobs
3) ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
Note: If you need to disconnect from the server after step 1 then step 3 will not be able to read from the temp table. Instead, you would have to feed a list of job IDs to the query. I have created a place for this just in case this is what you have to do.
Test Results:
Put Current Job Information Into Temp Table: 47 row(s) affected
DISABLE All SQL Server Agent Jobs: 33 row(s) affected
ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: 33 row(s) affected
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SET NOCOUNT ON -- USE MSDB; --Put Current Job Information Into Temp Table SELECT * INTO #SYSJOBS /* DROP TABLE #SYSJOBS */ FROM MSDB.dbo.sysjobs PRINT 'Put Current Job Information Into Temp Table: ' + CONVERT(varchar,@@rowcount) +' row(s) affected' SELECT * FROM #SYSJOBS ; --DISABLE All SQL Server Agent Jobs UPDATE MSDB.dbo.sysjobs SET Enabled = 0 WHERE Enabled = 1 PRINT 'DISABLE All SQL Server Agent Jobs: ' + CONVERT(varchar,@@rowcount) +' row(s) affected' SELECT * FROM MSDB.dbo.sysjobs ; --ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling UPDATE A SET Enabled = 1 FROM MSDB.dbo.sysjobs A WHERE Enabled = 0 AND ( EXISTS (SELECT 1 FROM #SYSJOBS B WHERE B.enabled = 1 AND A.job_id = B.job_id) /*READ FROM TEMP TABLE*/ OR CONVERT(varchar(MAX),job_id) IN ('') /*MANUALLY LIST JOB IDS*/ ) PRINT 'ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: ' + CONVERT(varchar,@@rowcount) +' row(s) affected' SELECT * FROM MSDB.dbo.sysjobs ; SET NOCOUNT OFF |