Idempotent SQL Server Agent Job Deployment Across Many Instances
Say the same maintenance job runs on forty instances. Last month someone changed step 1 on SQLPROD07 by hand to work around a problem, and nobody wrote it down. Now the job has to change everywhere, and the only deployment script is one that SQL Server Management Studio (SSMS) produced two years ago, from “Script Job as” and then “CREATE To”.
I wanted an idempotent script: one I can run on every instance, as often as I like, and get the same end state each time. I tested what the SSMS script does on a second run, wrote a deploy script that can be rerun, and wrote a query that shows which instances have drifted.
## What the Scripted Job Does on a Second Run
SSMS builds its scripts with SQL Server Management Objects (SMO), the .NET library that Management Studio uses to script an operation instead of running it.[1] I called SMO’s `Script()` method from PowerShell on two jobs, `sat_deploy_demo` and `sat_deploy_demo_b`, on SQL Server 2019. Both jobs share one schedule. Your version of SSMS may produce slightly different output.
The script doesn’t hard-code the `job_id`. It declares `@jobId` and gets the value back from `sp_add_job` as an `OUTPUT` parameter. It does hard-code two other values:
Transact-SQL
@active_start_date=20261030, @schedule_uid=N'ebf27d06-9ddc-4212-8d6e-8b8631ee9e61'
12 | @active_start_date=20261030,@schedule_uid=N'ebf27d06-9ddc-4212-8d6e-8b8631ee9e61'
---|---
The start date is the day the schedule was created on the source. The `schedule_uid` is the source schedule’s GUID.
I ran the script again on the instance it came from. Each job’s batch stopped at `sp_add_job`, and the script’s `GOTO QuitWithRollback` rolled back the transaction:
Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 60 The specified @name ('sat_deploy_demo') already exists. Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 60 The specified @name ('sat_deploy_demo_b') already exists.
1234 | Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 60The specified @name ('sat_deploy_demo') already exists.Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 60The specified @name ('sat_deploy_demo_b') already exists.
---|---
Nothing changed, and nothing was duplicated. The script also can’t update a job that exists, so it’s no use for pushing a change.
The “DROP And CREATE” form fails in a different way. SMO wrote the drop as a name check followed by a delete by ID:
Transact-SQL
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'sat_deploy_demo') EXEC msdb.dbo.sp_delete_job @job_id=N'e9541ea4-383f-4b35-80a8-93339f5bf08d', @delete_unused_schedule=1
12 | IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'sat_deploy_demo')EXEC msdb.dbo.sp_delete_job @job_id=N'e9541ea4-383f-4b35-80a8-93339f5bf08d', @delete_unused_schedule=1
---|---
That GUID belongs to the job on the source instance. On SQL Server 2025 the job had the same name and a different `job_id`, so the delete raised Msg 14262 (“The specified @job_id … does not exist”) and the create then raised Msg 14261. On the source instance, where that ID matches, the drop would also delete the job’s history, because `sp_delete_job` defaults `@delete_history` to 1.[2]
## The schedule_uid Has a Side Effect
To see what happens on an instance that doesn’t have the job, I renamed the jobs in the script to `sat_ssms_copy` and `sat_ssms_copy_b` and ran it on SQL Server 2025. The first copy created a new schedule with the source’s `schedule_uid`. The second copy didn’t create another one. It attached to the first, because `sp_add_schedule` looks up an existing schedule by `schedule_uid` and updates and reuses it. A comment in the procedure’s source says a target server (TSX) uses this to match schedules downloaded from a master server. The `sp_add_schedule` documentation lists `@schedule_uid` as an `OUTPUT` parameter and doesn’t mention that an incoming value matches an existing schedule.[3] The documentation for `sp_add_jobschedule`, the procedure the script calls, describes the parameter only as “a unique identifier for the schedule”. The same page recommends `sp_add_schedule` and `sp_attach_schedule` instead.[4]
When the GUID matches, the procedure’s source sets the existing schedule’s name and its frequency and time settings from the values the script passes. An old script run again could put back a schedule that someone has edited since. I read that in the source and didn’t test it.
That instance already had a schedule called `sat_nightly_0200`, created by my deploy script with its own GUID. After the copy ran, it had two schedules with that name. Schedule names don’t have to be unique. The only index on `msdb.dbo.sysschedules` is the primary key on `schedule_id`. After that, `sp_attach_schedule` with `@schedule_name` failed:
Msg 14371, Level 16, State 1, Procedure msdb.dbo.sp_verify_schedule_identifiers, Line 113 There are two or more schedules named "sat_nightly_0200". Specify @schedule_id instead of @schedule_name to uniquely identify the schedule.
12 | Msg 14371, Level 16, State 1, Procedure msdb.dbo.sp_verify_schedule_identifiers, Line 113There are two or more schedules named "sat_nightly_0200". Specify @schedule_id instead of @schedule_name to uniquely identify the schedule.
---|---
I removed the two copy jobs before going on.
## A Deploy Script You Can Rerun
The script below finds the job by name in `msdb.dbo.sysjobs` and calls `sp_add_job` or `sp_update_job`. It adds or updates each step by `step_id`, deletes steps it no longer defines, attaches one named schedule, and targets the job at the local server. It runs in one transaction, so a failure leaves the job as it was. The msdb update procedures keep a column’s current value when its parameter is NULL, so the script passes every setting that the drift query further down compares, including the ones left at their defaults. A setting the script leaves out keeps any hand change through the next deploy. For the second job I changed only `@job_name`.
Transact-SQL
USE [msdb]; SET NOCOUNT ON; DECLARE @job_name sysname = N'sat_deploy_demo'; DECLARE @job_description nvarchar(512) = N'Deployed by deploy-job.sql. Change the script, not the job.'; DECLARE @category_name sysname = N'sat_Deployed'; DECLARE @owner_login sysname = N'sa'; DECLARE @schedule_name sysname = N'sat_nightly_0200'; DECLARE @notify_eventlog int = 2; DECLARE @notify_email int = 0; DECLARE @delete_level int = 0; DECLARE @job_id uniqueidentifier; DECLARE @schedule_id int; DECLARE @step_id int; DECLARE @max_step_id int; DECLARE @owns_transaction bit = 0; DECLARE @steps TABLE ( [step_id] int NOT NULL PRIMARY KEY , [step_name] sysname NOT NULL , [database_name] sysname NOT NULL , [command] nvarchar(max) NOT NULL , [database_user_name] sysname NULL , [proxy_name] sysname NULL , [on_success_action] tinyint NOT NULL , [on_success_step_id] int NOT NULL , [on_fail_action] tinyint NOT NULL , [on_fail_step_id] int NOT NULL , [retry_attempts] int NOT NULL , [retry_interval] int NOT NULL ); /* on_success_action / on_fail_action: 1 = quit with success, 2 = quit with failure, 3 = go to next step, 4 = go to the step in on_success_step_id / on_fail_step_id */ INSERT INTO @steps ( [step_id] , [step_name] , [database_name] , [command] , [database_user_name] , [proxy_name] , [on_success_action] , [on_success_step_id] , [on_fail_action] , [on_fail_step_id] , [retry_attempts] , [retry_interval] ) VALUES (1, N'Check', N'sat_agent_deploy', N'SELECT 1;', NULL, NULL, 3, 0, 2, 0, 0, 0) , (2, N'Report', N'sat_agent_deploy', N'SELECT DB_NAME();', NULL, NULL, 1, 0, 2, 0, 1, 1); IF @@TRANCOUNT = 0 BEGIN SET @owns_transaction = 1; BEGIN TRANSACTION; END; BEGIN TRY IF NOT EXISTS ( SELECT 1 FROM [dbo].[syscategories] AS [c] WHERE [c].[name] = @category_name AND [c].[category_class] = 1 ) BEGIN EXEC [dbo].[sp_add_category] @class = N'JOB' , @type = N'LOCAL' , @name = @category_name; PRINT N'Category added.'; END; SELECT @job_id = [j].[job_id] FROM [dbo].[sysjobs] AS [j] WHERE [j].[name] = @job_name; IF @job_id IS NULL BEGIN EXEC [dbo].[sp_add_job] @job_name = @job_name , @enabled = 1 , @description = @job_description , @category_name = @category_name , @owner_login_name = @owner_login , @notify_level_eventlog = @notify_eventlog , @notify_level_email = @notify_email , @delete_level = @delete_level , @job_id = @job_id OUTPUT; PRINT N'Job added.'; END ELSE BEGIN EXEC [dbo].[sp_update_job] @job_id = @job_id , @enabled = 1 , @description = @job_description , @category_name = @category_name , @owner_login_name = @owner_login , @notify_level_eventlog = @notify_eventlog , @notify_level_email = @notify_email , @delete_level = @delete_level; PRINT N'Job updated.'; END; SELECT @max_step_id = MAX([s].[step_id]) FROM @steps AS [s]; SET @step_id = 1; /* The step_id values in @steps must run from 1 with no gaps */ WHILE @step_id <= @max_step_id BEGIN DECLARE @step_name sysname; DECLARE @database_name sysname; DECLARE @command nvarchar(max); DECLARE @database_user_name sysname; DECLARE @proxy_name sysname; DECLARE @on_success_action tinyint; DECLARE @on_success_step_id int; DECLARE @on_fail_action tinyint; DECLARE @on_fail_step_id int; DECLARE @retry_attempts int; DECLARE @retry_interval int; SELECT @step_name = [s].[step_name] , @database_name = [s].[database_name] , @command = [s].[command] , @database_user_name = [s].[database_user_name] , @proxy_name = [s].[proxy_name] , @on_success_action = [s].[on_success_action] , @on_success_step_id = [s].[on_success_step_id] , @on_fail_action = [s].[on_fail_action] , @on_fail_step_id = [s].[on_fail_step_id] , @retry_attempts = [s].[retry_attempts] , @retry_interval = [s].[retry_interval] FROM @steps AS [s] WHERE [s].[step_id] = @step_id; IF EXISTS ( SELECT 1 FROM [dbo].[sysjobsteps] AS [js] WHERE [js].[job_id] = @job_id AND [js].[step_id] = @step_id ) BEGIN /* On update, NULL keeps the current value. An empty string clears it. */ SET @database_user_name = ISNULL(@database_user_name, N''); SET @proxy_name = ISNULL(@proxy_name, N''); EXEC [dbo].[sp_update_jobstep] @job_id = @job_id , @step_id = @step_id , @step_name = @step_name , @subsystem = N'TSQL' , @database_name = @database_name , @command = @command , @database_user_name = @database_user_name , @proxy_name = @proxy_name , @on_success_action = @on_success_action , @on_success_step_id = @on_success_step_id , @on_fail_action = @on_fail_action , @on_fail_step_id = @on_fail_step_id , @retry_attempts = @retry_attempts , @retry_interval = @retry_interval; END ELSE BEGIN EXEC [dbo].[sp_add_jobstep] @job_id = @job_id , @step_id = @step_id , @step_name = @step_name , @subsystem = N'TSQL' , @database_name = @database_name , @command = @command , @database_user_name = @database_user_name , @proxy_name = @proxy_name , @on_success_action = @on_success_action , @on_success_step_id = @on_success_step_id , @on_fail_action = @on_fail_action , @on_fail_step_id = @on_fail_step_id , @retry_attempts = @retry_attempts , @retry_interval = @retry_interval; END; SET @step_id = @step_id + 1; END; /* Remove steps the script no longer defines, highest first */ SELECT @step_id = MAX([js].[step_id]) FROM [dbo].[sysjobsteps] AS [js] WHERE [js].[job_id] = @job_id; WHILE @step_id > @max_step_id BEGIN EXEC [dbo].[sp_delete_jobstep] @job_id = @job_id , @step_id = @step_id; SET @step_id = @step_id - 1; END; EXEC [dbo].[sp_update_job] @job_id = @job_id , @start_step_id = 1; IF ( SELECT COUNT_BIG(1) FROM [dbo].[sysschedules] AS [ss] WHERE [ss].[name] = @schedule_name ) > 1 BEGIN RAISERROR(N'More than one schedule is named %s. Fix that before deploying.', 16, 1, @schedule_name); END; SELECT @schedule_id = [ss].[schedule_id] FROM [dbo].[sysschedules] AS [ss] WHERE [ss].[name] = @schedule_name; IF @schedule_id IS NULL BEGIN EXEC [dbo].[sp_add_schedule] @schedule_name = @schedule_name , @enabled = 1 , @freq_type = 4 , @freq_interval = 1 , @freq_subday_type = 1 , @freq_subday_interval = 0 , @freq_relative_interval = 0 , @freq_recurrence_factor = 0 , @active_start_time = 20000 , @active_end_time = 235959 , @active_end_date = 99991231 , @owner_login_name = @owner_login , @schedule_id = @schedule_id OUTPUT; PRINT N'Schedule added.'; END ELSE BEGIN EXEC [dbo].[sp_update_schedule] @schedule_id = @schedule_id , @enabled = 1 , @freq_type = 4 , @freq_interval = 1 , @freq_subday_type = 1 , @freq_subday_interval = 0 , @freq_relative_interval = 0 , @freq_recurrence_factor = 0 , @active_start_time = 20000 , @active_end_time = 235959 , @active_end_date = 99991231 , @owner_login_name = @owner_login; END; IF NOT EXISTS ( SELECT 1 FROM [dbo].[sysjobschedules] AS [jsc] WHERE [jsc].[job_id] = @job_id AND [jsc].[schedule_id] = @schedule_id ) BEGIN EXEC [dbo].[sp_attach_schedule] @job_id = @job_id , @schedule_id = @schedule_id; PRINT N'Schedule attached.'; END; IF NOT EXISTS ( SELECT 1 FROM [dbo].[sysjobservers] AS [jsv] WHERE [jsv].[job_id] = @job_id ) BEGIN EXEC [dbo].[sp_add_jobserver] @job_id = @job_id , @server_name = N'(local)'; PRINT N'Job server added.'; END; IF @owns_transaction = 1 BEGIN COMMIT TRANSACTION; END; END TRY BEGIN CATCH IF @owns_transaction = 1 AND @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; THROW; END CATCH;
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330 | USE [msdb]; SET NOCOUNT ON; DECLARE @job_name sysname = N'sat_deploy_demo';DECLARE @job_description nvarchar(512) = N'Deployed by deploy-job.sql. Change the script, not the job.';DECLARE @category_name sysname = N'sat_Deployed';DECLARE @owner_login sysname = N'sa';DECLARE @schedule_name sysname = N'sat_nightly_0200';DECLARE @notify_eventlog int = 2;DECLARE @notify_email int = 0;DECLARE @delete_level int = 0;DECLARE @job_id uniqueidentifier;DECLARE @schedule_id int;DECLARE @step_id int;DECLARE @max_step_id int;DECLARE @owns_transaction bit = 0; DECLARE @steps TABLE( [step_id] int NOT NULL PRIMARY KEY , [step_name] sysname NOT NULL , [database_name] sysname NOT NULL , [command] nvarchar(max) NOT NULL , [database_user_name] sysname NULL , [proxy_name] sysname NULL , [on_success_action] tinyint NOT NULL , [on_success_step_id] int NOT NULL , [on_fail_action] tinyint NOT NULL , [on_fail_step_id] int NOT NULL , [retry_attempts] int NOT NULL , [retry_interval] int NOT NULL); /* on_success_action / on_fail_action: 1 = quit with success, 2 = quit with failure, 3 = go to next step, 4 = go to the step in on_success_step_id / on_fail_step_id*/INSERT INTO @steps( [step_id] , [step_name] , [database_name] , [command] , [database_user_name] , [proxy_name] , [on_success_action] , [on_success_step_id] , [on_fail_action] , [on_fail_step_id] , [retry_attempts] , [retry_interval])VALUES (1, N'Check', N'sat_agent_deploy', N'SELECT 1;', NULL, NULL, 3, 0, 2, 0, 0, 0) , (2, N'Report', N'sat_agent_deploy', N'SELECT DB_NAME();', NULL, NULL, 1, 0, 2, 0, 1, 1); IF @@TRANCOUNT = 0BEGIN SET @owns_transaction = 1; BEGIN TRANSACTION;END; BEGIN TRY IF NOT EXISTS ( SELECT 1 FROM [dbo].[syscategories] AS [c] WHERE [c].[name] = @category_name AND [c].[category_class] = 1 ) BEGIN EXEC [dbo].[sp_add_category] @class = N'JOB' , @type = N'LOCAL' , @name = @category_name; PRINT N'Category added.'; END; SELECT @job_id = [j].[job_id] FROM [dbo].[sysjobs] AS [j] WHERE [j].[name] = @job_name; IF @job_id IS NULL BEGIN EXEC [dbo].[sp_add_job] @job_name = @job_name , @enabled = 1 , @description = @job_description , @category_name = @category_name , @owner_login_name = @owner_login , @notify_level_eventlog = @notify_eventlog , @notify_level_email = @notify_email , @delete_level = @delete_level , @job_id = @job_id OUTPUT; PRINT N'Job added.'; END ELSE BEGIN EXEC [dbo].[sp_update_job] @job_id = @job_id , @enabled = 1 , @description = @job_description , @category_name = @category_name , @owner_login_name = @owner_login , @notify_level_eventlog = @notify_eventlog , @notify_level_email = @notify_email , @delete_level = @delete_level; PRINT N'Job updated.'; END; SELECT @max_step_id = MAX([s].[step_id]) FROM @steps AS [s]; SET @step_id = 1; /* The step_id values in @steps must run from 1 with no gaps */ WHILE @step_id <= @max_step_id BEGIN DECLARE @step_name sysname; DECLARE @database_name sysname; DECLARE @command nvarchar(max); DECLARE @database_user_name sysname; DECLARE @proxy_name sysname; DECLARE @on_success_action tinyint; DECLARE @on_success_step_id int; DECLARE @on_fail_action tinyint; DECLARE @on_fail_step_id int; DECLARE @retry_attempts int; DECLARE @retry_interval int; SELECT @step_name = [s].[step_name] , @database_name = [s].[database_name] , @command = [s].[command] , @database_user_name = [s].[database_user_name] , @proxy_name = [s].[proxy_name] , @on_success_action = [s].[on_success_action] , @on_success_step_id = [s].[on_success_step_id] , @on_fail_action = [s].[on_fail_action] , @on_fail_step_id = [s].[on_fail_step_id] , @retry_attempts = [s].[retry_attempts] , @retry_interval = [s].[retry_interval] FROM @steps AS [s] WHERE [s].[step_id] = @step_id; IF EXISTS ( SELECT 1 FROM [dbo].[sysjobsteps] AS [js] WHERE [js].[job_id] = @job_id AND [js].[step_id] = @step_id ) BEGIN /* On update, NULL keeps the current value. An empty string clears it. */ SET @database_user_name = ISNULL(@database_user_name, N''); SET @proxy_name = ISNULL(@proxy_name, N''); EXEC [dbo].[sp_update_jobstep] @job_id = @job_id , @step_id = @step_id , @step_name = @step_name , @subsystem = N'TSQL' , @database_name = @database_name , @command = @command , @database_user_name = @database_user_name , @proxy_name = @proxy_name , @on_success_action = @on_success_action , @on_success_step_id = @on_success_step_id , @on_fail_action = @on_fail_action , @on_fail_step_id = @on_fail_step_id , @retry_attempts = @retry_attempts , @retry_interval = @retry_interval; END ELSE BEGIN EXEC [dbo].[sp_add_jobstep] @job_id = @job_id , @step_id = @step_id , @step_name = @step_name , @subsystem = N'TSQL' , @database_name = @database_name , @command = @command , @database_user_name = @database_user_name , @proxy_name = @proxy_name , @on_success_action = @on_success_action , @on_success_step_id = @on_success_step_id , @on_fail_action = @on_fail_action , @on_fail_step_id = @on_fail_step_id , @retry_attempts = @retry_attempts , @retry_interval = @retry_interval; END; SET @step_id = @step_id + 1; END; /* Remove steps the script no longer defines, highest first */ SELECT @step_id = MAX([js].[step_id]) FROM [dbo].[sysjobsteps] AS [js] WHERE [js].[job_id] = @job_id; WHILE @step_id > @max_step_id BEGIN EXEC [dbo].[sp_delete_jobstep] @job_id = @job_id , @step_id = @step_id; SET @step_id = @step_id - 1; END; EXEC [dbo].[sp_update_job] @job_id = @job_id , @start_step_id = 1; IF ( SELECT COUNT_BIG(1) FROM [dbo].[sysschedules] AS [ss] WHERE [ss].[name] = @schedule_name ) > 1 BEGIN RAISERROR(N'More than one schedule is named %s. Fix that before deploying.', 16, 1, @schedule_name); END; SELECT @schedule_id = [ss].[schedule_id] FROM [dbo].[sysschedules] AS [ss] WHERE [ss].[name] = @schedule_name; IF @schedule_id IS NULL BEGIN EXEC [dbo].[sp_add_schedule] @schedule_name = @schedule_name , @enabled = 1 , @freq_type = 4 , @freq_interval = 1 , @freq_subday_type = 1 , @freq_subday_interval = 0 , @freq_relative_interval = 0 , @freq_recurrence_factor = 0 , @active_start_time = 20000 , @active_end_time = 235959 , @active_end_date = 99991231 , @owner_login_name = @owner_login , @schedule_id = @schedule_id OUTPUT; PRINT N'Schedule added.'; END ELSE BEGIN EXEC [dbo].[sp_update_schedule] @schedule_id = @schedule_id , @enabled = 1 , @freq_type = 4 , @freq_interval = 1 , @freq_subday_type = 1 , @freq_subday_interval = 0 , @freq_relative_interval = 0 , @freq_recurrence_factor = 0 , @active_start_time = 20000 , @active_end_time = 235959 , @active_end_date = 99991231 , @owner_login_name = @owner_login; END; IF NOT EXISTS ( SELECT 1 FROM [dbo].[sysjobschedules] AS [jsc] WHERE [jsc].[job_id] = @job_id AND [jsc].[schedule_id] = @schedule_id ) BEGIN EXEC [dbo].[sp_attach_schedule] @job_id = @job_id , @schedule_id = @schedule_id; PRINT N'Schedule attached.'; END; IF NOT EXISTS ( SELECT 1 FROM [dbo].[sysjobservers] AS [jsv] WHERE [jsv].[job_id] = @job_id ) BEGIN EXEC [dbo].[sp_add_jobserver] @job_id = @job_id , @server_name = N'(local)'; PRINT N'Job server added.'; END; IF @owns_transaction = 1 BEGIN COMMIT TRANSACTION; END;END TRYBEGIN CATCH IF @owns_transaction = 1 AND @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; THROW;END CATCH;
---|---
* **Owner.** The job and the schedule both belong to `sa`. `sp_attach_schedule` requires the schedule and the job to have the same owner.[5] The owner section below covers why the owner shouldn’t be a person.
* **Category.** `sp_add_job` with a category that doesn’t exist raised Msg 14262 (“The specified @category_name (‘sat_NoSuchCategory’) does not exist.”) on both versions, so the script creates the category first. A category of its own also gives the drift query an easy way to find the deployed jobs.
* **Schedule.** The script finds the schedule by name and stops with an error if more than one has that name. I tested that guard while the duplicate schedule existed, and the transaction rolled back. `schedule_id` and `schedule_uid` were different on each instance (366 and 45 for the same schedule), because the script lets `sp_add_schedule` assign them. A script could pass a fixed `@schedule_uid`, as the SMO script does, but then it gets the side effects in the previous section, so I match on the name.
* **Job server.** `sp_add_jobserver` with `@server_name = N'(local)'` targets the job at the local instance.[6] Without it, `sp_start_job` refuses to start the job. On a test job with no job server it raised Msg 14256: “Cannot start the job … because it does not have any job server or servers defined.”
* **Enabled.** The script sets `@enabled = 1` every time. If someone disables a job on one instance on purpose, the next deploy turns it back on. Decide whether the script or the DBA owns that flag, and remove the parameter from `sp_update_job` if it’s the DBA.
## Running It Twice
I ran the script twice for each job on both instances: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer), on one workstation. On the 2025 instance I removed the jobs, their schedule and the category first, so the first run added the category, job, schedule and job server. The second run printed only “Job updated”. The `job_id` didn’t change, both jobs still shared one schedule, and each job still had one job server row (`server_id` 0, the local server).
Next I checked the settings the script passes at their default values. On the 2019 instance I changed the job’s `notify_level_eventlog`, step 1’s `on_fail_action`, `on_fail_step_id` and `database_user_name`, and the shared schedule’s `freq_subday_type`, `freq_subday_interval` and `active_end_time`. An earlier version of the script passed only the parameters it needed for this job. It set `on_fail_action` back and left the other six changes in place, and the drift query still reported both jobs. The script above set all of them back, and both jobs had the same hashes as on the 2025 instance.
The second run isn’t a no-op. On the 2025 instance, `version_number` in `sysjobs` went from 4 to 8 and `date_modified` moved, because the script calls `sp_update_job` and `sp_update_jobstep` every time. The definition didn’t change, but the metadata records an update. If you audit on `date_modified`, compare the definition before you update.
SQL Server Agent was stopped on the 2025 instance, and I left it stopped. The msdb procedures still created and updated the jobs. Several printed “SQLServerAgent is not currently running so it cannot be notified of this action.” and the script still exited with code 0. `sp_start_job` returned the same message as an error and started nothing.
On the 2019 instance I started `sat_deploy_demo` once, and both steps succeeded.
## Job Owners
People leave, and their logins get disabled or removed, so a person’s login is a poor job owner. Use `sa`, or a dedicated login that you create on every instance before the jobs. The job history for my `sa`-owned job said “Executed as user: NT AUTHORITY\NETWORK SERVICE”, which is the Agent service account on that instance. Agent runs the T-SQL steps of a job owned by a sysadmin as the Agent service account, and uses `EXECUTE AS LOGIN` only when the owner isn’t a sysadmin. Why Resource Governor Ignores Your SQL Server Agent Job Owner explains how Agent connects, and why the job owner doesn’t decide which login the session uses.
`sp_add_job` documents that only sysadmin members can set the owner, and that Agent runs T-SQL job steps in the owner’s security context.[7]
* A login name that doesn’t exist, such as `CONTOSO\sat_no_such_user`, raised Msg 14234 (“The specified ‘@owner_login_name’ is invalid”) from both `sp_add_job` and `sp_update_job`. No job was created and the existing owner didn’t change.
* A Windows account that exists in Windows but has no SQL Server login was accepted. The check in `sp_verify_job` rejects a name only when it doesn’t resolve to a SID, or when it’s a Windows group login. When I started that job, it failed before any step ran: “The owner (SQLTEST01\DefaultAccount) of job sat_owner_nologin does not have server access.” I replaced the machine name in that message.
* `DROP LOGIN` on a SQL login that owned a job raised Msg 15170: “This login is the owner of 1 job(s). You must delete or reassign these jobs before the login can be dropped.”
In the second case, the deploy script succeeds, and the job fails the first time it runs.
## Shared Schedules
A shared schedule is one row in `msdb.dbo.sysschedules` attached to more than one job. Changing it changes every job attached to it, and those jobs use the new settings immediately.[8] I changed `sat_nightly_0200` from 02:00 to 03:00 with `sp_update_schedule` on the 2019 instance. The drift query reported both jobs as changed, although only the schedule was edited. Running the deploy script for `sat_deploy_demo` alone set it back for both jobs.
That’s useful when the jobs should move together. It also means two deploy scripts that define the same shared schedule differently can change it back and forth, one deploy at a time. Keep each shared schedule’s definition in one place.
## Finding Drift Between Instances
The query below builds a JSON document from the parts of each job that should match on every instance, and hashes it with SHA-256. It leaves out IDs, dates, `version_number` and the schedule’s `active_start_date`, because those differ by instance. For the same reason it uses the proxy’s name from `msdb.dbo.sysproxies` rather than the step’s `proxy_id`.
Transact-SQL
USE [msdb]; SET NOCOUNT ON; SELECT [j].[name] AS [job_name] , CONVERT(char(64), HASHBYTES('SHA2_256', [d].[definition]), 2) AS [definition_sha256] , [d].[definition] FROM [dbo].[sysjobs] AS [j] CROSS APPLY ( SELECT [definition] = ( SELECT [j].[name] , [j].[enabled] , [j].[description] , [c].[name] AS [category] , SUSER_SNAME([j].[owner_sid]) AS [owner] , [j].[start_step_id] , [j].[notify_level_eventlog] , [j].[notify_level_email] , [j].[delete_level] , [steps] = ( SELECT [js].[step_id] , [js].[step_name] , [js].[subsystem] , [js].[database_name] , [js].[database_user_name] , [js].[command] , [js].[on_success_action] , [js].[on_success_step_id] , [js].[on_fail_action] , [js].[on_fail_step_id] , [js].[retry_attempts] , [js].[retry_interval] , [p].[name] AS [proxy_name] FROM [dbo].[sysjobsteps] AS [js] LEFT OUTER JOIN [dbo].[sysproxies] AS [p] ON [p].[proxy_id] = [js].[proxy_id] WHERE [js].[job_id] = [j].[job_id] ORDER BY [js].[step_id] FOR JSON PATH ) , [schedules] = ( SELECT [ss].[name] , [ss].[enabled] , [ss].[freq_type] , [ss].[freq_interval] , [ss].[freq_subday_type] , [ss].[freq_subday_interval] , [ss].[freq_relative_interval] , [ss].[freq_recurrence_factor] , [ss].[active_start_time] , [ss].[active_end_time] , [ss].[active_end_date] FROM [dbo].[sysjobschedules] AS [jsc] INNER JOIN [dbo].[sysschedules] AS [ss] ON [ss].[schedule_id] = [jsc].[schedule_id] WHERE [jsc].[job_id] = [j].[job_id] ORDER BY [ss].[name] FOR JSON PATH ) , [targets] = ( SELECT COUNT_BIG(1) FROM [dbo].[sysjobservers] AS [jsv] WHERE [jsv].[job_id] = [j].[job_id] ) FROM [dbo].[syscategories] AS [c] WHERE [c].[category_id] = [j].[category_id] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER ) ) AS [d] INNER JOIN [dbo].[syscategories] AS [cat] ON [cat].[category_id] = [j].[category_id] WHERE [cat].[name] = N'sat_Deployed' ORDER BY [j].[name];
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100 | USE [msdb]; SET NOCOUNT ON; SELECT [j].[name] AS [job_name] , CONVERT(char(64), HASHBYTES('SHA2_256', [d].[definition]), 2) AS [definition_sha256] , [d].[definition]FROM [dbo].[sysjobs] AS [j]CROSS APPLY( SELECT [definition] = ( SELECT [j].[name] , [j].[enabled] , [j].[description] , [c].[name] AS [category] , SUSER_SNAME([j].[owner_sid]) AS [owner] , [j].[start_step_id] , [j].[notify_level_eventlog] , [j].[notify_level_email] , [j].[delete_level] , [steps] = ( SELECT [js].[step_id] , [js].[step_name] , [js].[subsystem] , [js].[database_name] , [js].[database_user_name] , [js].[command] , [js].[on_success_action] , [js].[on_success_step_id] , [js].[on_fail_action] , [js].[on_fail_step_id] , [js].[retry_attempts] , [js].[retry_interval] , [p].[name] AS [proxy_name] FROM [dbo].[sysjobsteps] AS [js] LEFT OUTER JOIN [dbo].[sysproxies] AS [p] ON [p].[proxy_id] = [js].[proxy_id] WHERE [js].[job_id] = [j].[job_id] ORDER BY [js].[step_id] FOR JSON PATH ) , [schedules] = ( SELECT [ss].[name] , [ss].[enabled] , [ss].[freq_type] , [ss].[freq_interval] , [ss].[freq_subday_type] , [ss].[freq_subday_interval] , [ss].[freq_relative_interval] , [ss].[freq_recurrence_factor] , [ss].[active_start_time] , [ss].[active_end_time] , [ss].[active_end_date] FROM [dbo].[sysjobschedules] AS [jsc] INNER JOIN [dbo].[sysschedules] AS [ss] ON [ss].[schedule_id] = [jsc].[schedule_id] WHERE [jsc].[job_id] = [j].[job_id] ORDER BY [ss].[name] FOR JSON PATH ) , [targets] = ( SELECT COUNT_BIG(1) FROM [dbo].[sysjobservers] AS [jsv] WHERE [jsv].[job_id] = [j].[job_id] ) FROM [dbo].[syscategories] AS [c] WHERE [c].[category_id] = [j].[category_id] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )) AS [d]INNER JOIN [dbo].[syscategories] AS [cat] ON [cat].[category_id] = [j].[category_id]WHERE [cat].[name] = N'sat_Deployed'ORDER BY [j].[name];
---|---
A short PowerShell script runs the query on each instance and groups the results by job name:
PowerShell
param( [string[]] $Instances = @('SQLPROD01', 'SQLPROD02'), [string] $QueryPath = (Join-Path $PSScriptRoot 'drift-check.sql') ) $query = Get-Content -Path $QueryPath -Raw $rows = foreach ($instance in $Instances) { $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $builder['Data Source'] = $instance $builder['Integrated Security'] = $true $builder['Encrypt'] = $true # Test instances only: skipping certificate validation allows a man-in-the-middle attack. $builder['TrustServerCertificate'] = $true $conn = New-Object System.Data.SqlClient.SqlConnection $builder.ConnectionString $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = $query $reader = $cmd.ExecuteReader() while ($reader.Read()) { [pscustomobject]@{ Instance = $instance JobName = $reader['job_name'] Hash = $reader['definition_sha256'] Definition = $reader['definition'] } } $conn.Close() } $rows | Group-Object JobName | ForEach-Object { $hashes = @($_.Group.Hash | Sort-Object -Unique) [pscustomobject]@{ JobName = $_.Name Instances = $_.Count Hashes = $hashes.Count Status = if ($_.Count -lt $Instances.Count) { 'MISSING' } elseif ($hashes.Count -gt 1) { 'DRIFT' } else { 'OK' } } } | Format-Table -AutoSize $rows | Select-Object Instance, JobName, Hash | Format-Table -AutoSize
12345678910111213141516171819202122232425262728293031323334353637383940 | param( [string[]] $Instances = @('SQLPROD01', 'SQLPROD02'), [string] $QueryPath = (Join-Path $PSScriptRoot 'drift-check.sql')) $query = Get-Content -Path $QueryPath -Raw$rows = foreach ($instance in $Instances) { $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $builder['Data Source'] = $instance $builder['Integrated Security'] = $true $builder['Encrypt'] = $true # Test instances only: skipping certificate validation allows a man-in-the-middle attack. $builder['TrustServerCertificate'] = $true $conn = New-Object System.Data.SqlClient.SqlConnection $builder.ConnectionString $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = $query $reader = $cmd.ExecuteReader() while ($reader.Read()) { [pscustomobject]@{ Instance = $instance JobName = $reader['job_name'] Hash = $reader['definition_sha256'] Definition = $reader['definition'] } } $conn.Close()} $rows | Group-Object JobName | ForEach-Object { $hashes = @($_.Group.Hash | Sort-Object -Unique) [pscustomobject]@{ JobName = $_.Name Instances = $_.Count Hashes = $hashes.Count Status = if ($_.Count -lt $Instances.Count) { 'MISSING' } elseif ($hashes.Count -gt 1) { 'DRIFT' } else { 'OK' } }} | Format-Table -AutoSize $rows | Select-Object Instance, JobName, Hash | Format-Table -AutoSize
---|---
The script builds each connection string with `SqlConnectionStringBuilder`, so an instance name can’t add keywords of its own, and it encrypts the connection. `TrustServerCertificate=True` keeps the encryption but tells the driver not to validate the server’s certificate. Microsoft marks that setting as not recommended, and it’s the gap that strict encryption closes to prevent a man-in-the-middle attack.[9] I set it only because I ran this against local test instances. On a real network, give each server a certificate from a certificate authority your clients trust, and leave `TrustServerCertificate` off.
After both deploy runs, every job had the same hash on both instances. I then changed step 1 of `sat_deploy_demo` by hand on the 2025 instance with `sp_update_jobstep`:
JobName Instances Hashes Status ------- --------- ------ ------ sat_deploy_demo 2 2 DRIFT sat_deploy_demo_b 2 1 OK
1234 | JobName Instances Hashes Status------- --------- ------ ------sat_deploy_demo 2 2 DRIFTsat_deploy_demo_b 2 1 OK
---|---
After one more run of the deploy script on that instance, both jobs reported `OK` again. The `definition` column holds the JSON, so you can compare the two documents to see what differs. Find-and-Replace for SQL Server Agent Jobs changes many jobs on one instance. If you use it, run it on every instance or run the drift check afterward.
## Test Limits
* Two instances on one workstation, T-SQL steps only. No proxies, so the proxy name in the drift query was always NULL. No other subsystems, operators or alerts, and the drift query doesn’t include notification operators.
* The “script as” output came from SMO 17 in PowerShell, not the SSMS dialog.
* No multiserver (MSX/TSX) administration, where `@server_name` names a target server.
* Jobs ran on the 2019 instance only, because Agent was stopped on the 2025 instance.
For an older approach to scripting every job on an instance, see Tool for scripting SQL Server Agent Jobs.
Related: Reading an Inherited SQL Server Agent Job: Step Flow, Retries and History _(available 2026-11-10)_.
If you deploy Agent jobs another way, or you’ve found drift your checks didn’t catch, I’d like to hear about it. Find me on Bluesky or LinkedIn.
## References
1. Overview (SMO) - Microsoft Learn. Describes SMO’s capture of Transact-SQL statements and states that Management Studio uses this capability to script an operation instead of running it. ↩
2. sp_delete_job (Transact-SQL) - Microsoft Learn. States that @delete_history defaults to 1, which deletes the job’s history. ↩
3. sp_add_schedule (Transact-SQL) - Microsoft Learn. Lists @schedule_uid as an OUTPUT parameter described as a unique identifier for the schedule, with no mention of matching an existing schedule on input. ↩
4. sp_add_jobschedule (Transact-SQL) - Microsoft Learn. Describes @schedule_uid as a unique identifier for the schedule, and recommends sp_add_schedule with sp_attach_schedule to add a schedule to a job. ↩
5. sp_attach_schedule (Transact-SQL) - Microsoft Learn. States that the schedule and the job must have the same owner, and that a schedule can be set for more than one job. ↩
6. sp_add_jobserver (Transact-SQL) - Microsoft Learn. States that @server_name can be (LOCAL) for the local server or the name of an existing target server. ↩
7. sp_add_job (Transact-SQL) - Microsoft Learn. States that only sysadmin members can set @owner_login_name, and that SQL Server Agent runs Transact-SQL job steps in the job owner’s security context. ↩
8. sp_update_schedule (Transact-SQL) - Microsoft Learn. States that all jobs that use the schedule immediately use the new settings. ↩
9. TDS 8.0 - Microsoft Learn. Describes TrustServerCertificate=true as not recommended because the driver then doesn’t validate the server TLS/SSL certificate, and states that strict encryption doesn’t allow TrustServerCertificate=true, in order to prevent a man-in-the-middle attack. ↩
### Share this:
* * Share on Bluesky (Opens in new window) Bluesky
* Share on Mastodon (Opens in new window) Mastodon
* Email a link to a friend (Opens in new window) Email
* More
*
* Share on Threads (Opens in new window) Threads
* Share on Reddit (Opens in new window) Reddit
* * Share on WhatsApp (Opens in new window) WhatsApp
* Print (Opens in new window) Print
*
### _Related_