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:
|
1 2 |
@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:
|
1 2 3 4 |
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. |
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:
|
1 2 |
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:
|
1 2 |
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. |
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.
|
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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 |
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; |
- Owner. The job and the schedule both belong to
sa.sp_attach_schedulerequires 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_jobwith 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_idandschedule_uidwere different on each instance (366 and 45 for the same schedule), because the script letssp_add_scheduleassign 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_jobserverwith@server_name = N'(local)'targets the job at the local instance.[6] Without it,sp_start_jobrefuses 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 = 1every 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 fromsp_update_jobif 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 bothsp_add_jobandsp_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_jobrejects 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 LOGINon 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.
|
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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
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:
|
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 38 39 40 |
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:
|
1 2 3 4 |
JobName Instances Hashes Status ------- --------- ------ ------ sat_deploy_demo 2 2 DRIFT sat_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_namenames 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
- 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. ↩
- sp_delete_job (Transact-SQL) - Microsoft Learn. States that @delete_history defaults to 1, which deletes the job’s history. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- sp_update_schedule (Transact-SQL) - Microsoft Learn. States that all jobs that use the schedule immediately use the new settings. ↩
- 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. ↩