Home / Guides / Compare SQL Agent jobs between servers

Compare SQL Agent jobs between two SQL Servers

The nightly job ran on the old server and not on the new one. Or it ran on both, but the DR copy still has last year's schedule, or a step that points at a database that no longer exists. Checking that two servers have the same SQL Server Agent jobs by name is easy; checking that the jobs are the same inside is not. This guide covers where Agent keeps its jobs in msdb, the queries that list jobs, steps and schedules, a hash per job that tells you in one pass which jobs differ and in which part, a PowerShell script that compares a reference server with others, what dbatools offers, and where comparing jobs this way stops.

Where Agent keeps its jobs

• msdb.dbo.sysjobs: one row per job, with its name, enabled flag, description, category, owner (owner_sid), start step and notification settings.
• msdb.dbo.sysjobsteps: one row per step, with the subsystem (T-SQL, PowerShell, CmdExec, SSIS ...), the command, the database, what to do on success and on failure, retries and the output file.
• msdb.dbo.sysschedules: the schedules, with frequency, interval and start and end times.
• msdb.dbo.sysjobschedules: which schedule belongs to which job. A schedule can be shared by several jobs.
• msdb.dbo.syscategories, dbo.sysoperators and dbo.sysproxies: the names behind the category, operator and proxy IDs.

Several columns differ between servers even when the jobs are the same: job_id and schedule_id are generated on each server, date_created, date_modified and version_number record each server's own history, and owner_sid is a SID (compare the login name from SUSER_SNAME(owner_sid) instead). Leave those out of any comparison.

Who can read these tables

In the msdb we checked (SQL Server 2025), the SQL Server Agent roles get views and procedures such as sysjobs_view and sp_help_job, not SELECT on sysjobsteps or sysschedules. So run these queries as a member of sysadmin, or grant SELECT on the tables to the account that compares. Microsoft notes that owner_sid shows which login runs the T-SQL steps, so grant that access only to people who need it.

List the jobs, steps and schedules

Run each query on both servers. First the jobs, with how many steps and schedules each has:

USE [msdb];
SELECT j.name                      AS job_name,
       j.enabled,
       c.name                      AS category,
       SUSER_SNAME(j.owner_sid)    AS owner,
       (SELECT COUNT(*) FROM dbo.sysjobsteps s     WHERE s.job_id = j.job_id)  AS steps,
       (SELECT COUNT(*) FROM dbo.sysjobschedules js WHERE js.job_id = j.job_id) AS schedules,
       j.date_modified
FROM dbo.sysjobs AS j
JOIN dbo.syscategories AS c ON c.category_id = j.category_id
ORDER BY j.name;

The steps, in order, with the command text:

USE [msdb];
SELECT j.name AS job_name, s.step_id, s.step_name, s.subsystem, s.database_name,
       s.on_success_action, s.on_fail_action, s.retry_attempts, s.retry_interval,
       px.name AS proxy, s.command
FROM dbo.sysjobs AS j
JOIN dbo.sysjobsteps AS s ON s.job_id = j.job_id
LEFT JOIN dbo.sysproxies AS px ON px.proxy_id = s.proxy_id
ORDER BY j.name, s.step_id;

The schedules of each job. freq_type 4 is daily, 8 weekly, 16 monthly, 32 monthly relative, 64 when Agent starts and 128 when the computer is idle; active_start_time is HHMMSS. The last column shows how many jobs share the schedule:

USE [msdb];
SELECT j.name AS job_name, sc.name AS schedule_name, sc.enabled,
       sc.freq_type, sc.freq_interval, sc.freq_subday_type, sc.freq_subday_interval,
       sc.freq_relative_interval, sc.freq_recurrence_factor,
       sc.active_start_date, sc.active_end_date, sc.active_start_time, sc.active_end_time,
       (SELECT COUNT(*) FROM dbo.sysjobschedules x WHERE x.schedule_id = sc.schedule_id) AS jobs_using_it
FROM dbo.sysjobs AS j
JOIN dbo.sysjobschedules AS js ON js.job_id = j.job_id
JOIN dbo.sysschedules AS sc ON sc.schedule_id = js.schedule_id
ORDER BY j.name, sc.name;

For two servers and a handful of jobs, running these in two SSMS windows and reading them side by side is enough. SSMS can also script a job (right-click it, Script Job as, CREATE To) so you can put the two scripts in a diff tool; the scripts include the job and schedule IDs and the server name, so expect a few lines to differ even for identical jobs.

Hash each job, then compare the hashes

With dozens of jobs, comparing rows by eye stops working. This query builds one text per job for its settings, one for its steps and one for its schedules, from the columns that should match, and returns a SHA-256 hash of each. Run it on two servers: a job whose three hashes are equal on both is the same; a different hash tells you whether the settings, the steps or the schedules changed.

USE [msdb];
WITH steps AS (
    SELECT s.job_id,
           STRING_AGG(CAST(CONCAT(s.step_id, N'|', s.step_name, N'|', s.subsystem, N'|', s.database_name, N'|',
                                  s.database_user_name, N'|', s.on_success_action, N'|', s.on_success_step_id, N'|',
                                  s.on_fail_action, N'|', s.on_fail_step_id, N'|', s.retry_attempts, N'|',
                                  s.retry_interval, N'|', s.output_file_name, N'|', px.name, N'|', s.command)
                       AS nvarchar(max)), N'#') WITHIN GROUP (ORDER BY s.step_id) AS txt
    FROM dbo.sysjobsteps AS s
    LEFT JOIN dbo.sysproxies AS px ON px.proxy_id = s.proxy_id
    GROUP BY s.job_id
), schedules AS (
    SELECT js.job_id,
           STRING_AGG(CAST(CONCAT(sc.name, N'|', sc.enabled, N'|', sc.freq_type, N'|', sc.freq_interval, N'|',
                                  sc.freq_subday_type, N'|', sc.freq_subday_interval, N'|', sc.freq_relative_interval, N'|',
                                  sc.freq_recurrence_factor, N'|', sc.active_start_date, N'|', sc.active_end_date, N'|',
                                  sc.active_start_time, N'|', sc.active_end_time)
                       AS nvarchar(max)), N'#') WITHIN GROUP (ORDER BY sc.name) AS txt
    FROM dbo.sysjobschedules AS js
    JOIN dbo.sysschedules AS sc ON sc.schedule_id = js.schedule_id
    GROUP BY js.job_id
)
SELECT j.name AS job_name,
       CONVERT(char(64), HASHBYTES('SHA2_256',
           CONCAT(N'', j.enabled, N'|', j.description, N'|', c.name, N'|', SUSER_SNAME(j.owner_sid), N'|',
                  j.start_step_id, N'|', j.notify_level_email, N'|', o.name, N'|', j.notify_level_eventlog, N'|',
                  j.delete_level)), 2)                                   AS header_hash,
       CONVERT(char(64), HASHBYTES('SHA2_256', ISNULL(st.txt, N'')), 2) AS steps_hash,
       CONVERT(char(64), HASHBYTES('SHA2_256', ISNULL(sh.txt, N'')), 2) AS schedules_hash
FROM dbo.sysjobs AS j
JOIN dbo.syscategories AS c ON c.category_id = j.category_id
LEFT JOIN dbo.sysoperators AS o ON o.id = j.notify_email_operator_id
LEFT JOIN steps AS st ON st.job_id = j.job_id
LEFT JOIN schedules AS sh ON sh.job_id = j.job_id
ORDER BY j.name;

• Settings: enabled, description, category, owner name, start step, the email operator and when it is notified, Windows event log notification and the delete level.
• Steps: number, name, subsystem, database, database user, on success and on failure actions and target steps, retries, output file, proxy name and the command.
• Schedules: by name, with enabled and every frequency and active date and time column.

The query needs SQL Server 2017 or later (STRING_AGG). Every part is cast to nvarchar(max) so long commands are not cut off; HASHBYTES has no input size limit from SQL Server 2016 on. CONCAT treats NULL as an empty string, so a NULL and an empty description count as the same. If you want a job's enabled flag ignored (for example jobs that are disabled on purpose on a secondary), remove j.enabled from the settings part.

Compare a reference server with others in PowerShell

Save the hash query above as C:\Scripts\AgentJobHashes.sql. This script runs it on the reference server and on every server you compare with, matches the jobs by name and reports each job as Same, Missing on the other server, Only on the other server, or Different with the parts that differ. It uses Invoke-Sqlcmd from Microsoft's SqlServer module (Install-Module SqlServer once). A server that cannot be read is reported and skipped.

param(
    [string]$Reference = 'SQL01',                    # the server whose jobs are "right"
    [string[]]$Compare = @('SQL02', 'SQL03\DR'),     # the servers to check against it
    [string]$QueryFile = 'C:\Scripts\AgentJobHashes.sql',
    [string]$CsvPath   = "$env:USERPROFILE\Desktop\AgentJobCompare.csv"
)
Import-Module SqlServer -ErrorAction Stop
$query = Get-Content -Path $QueryFile -Raw

function Get-JobHashes([string]$Instance) {
    $map = @{}                                       # job name -> its hashes (names compare case-insensitively)
    foreach ($row in Invoke-Sqlcmd -ServerInstance $Instance -Database msdb -Query $query -QueryTimeout 120 -ErrorAction Stop) {
        $map[$row.job_name] = $row
    }
    $map
}

$ref = Get-JobHashes $Reference
$result = foreach ($server in $Compare) {
    try   { $other = Get-JobHashes $server }
    catch { Write-Warning "${server}: $($_.Exception.Message)"; continue }

    foreach ($name in (@($ref.Keys) + @($other.Keys) | Sort-Object -Unique)) {
        $a = $ref[$name]; $b = $other[$name]
        $status = if (-not $b) { "Missing on $server" }
                  elseif (-not $a) { "Only on $server" }
                  else {
                      $diff = @()
                      if ($a.header_hash    -ne $b.header_hash)    { $diff += 'job settings' }
                      if ($a.steps_hash     -ne $b.steps_hash)     { $diff += 'steps' }
                      if ($a.schedules_hash -ne $b.schedules_hash) { $diff += 'schedules' }
                      if ($diff) { 'Different: ' + ($diff -join ', ') } else { 'Same' }
                  }
        [pscustomobject]@{ Job = $name; Reference = $Reference; Server = $server; Status = $status }
    }
}

$result | Where-Object Status -ne 'Same' | Format-Table -AutoSize
$result | Export-Csv -Path $CsvPath -NoTypeInformation -Encoding UTF8
"{0} jobs compared, {1} not the same; all rows in {2}" -f @($result).Count, @($result | Where-Object Status -ne 'Same').Count, $CsvPath

Save it as C:\Scripts\Compare-AgentJobs.ps1 and run it:

C:\Scripts\Compare-AgentJobs.ps1 -Reference SQL01 -Compare SQL02, 'SQL03\DR'

# Example output
Job            Reference Server Status
---            --------- ------ ------
Index maint    SQL01     SQL02  Different: steps, schedules
Purge history  SQL01     SQL02  Different: job settings
Weekly report  SQL01     SQL02  Missing on SQL02
Local cleanup  SQL01     SQL02  Only on SQL02

For a job that comes back Different: steps, run the steps query on both servers for that job and compare the two results; the hash tells you where to look, not what changed.

Tested on Windows 11 with SQL Server 2025 LocalDB (17.0.4025.3), read-only: the job, step, schedule and hash queries ran as shown against its msdb (which has no jobs, but seven system schedules, so the schedule aggregation ran on real rows), and the hashing expressions were run on made-up rows for two servers, which gave equal hashes for equal jobs (including a 9,000-character command) and a different hash for a changed step. We read the msdb grants there too. The PowerShell script was run with Invoke-Sqlcmd replaced by a stub that returned hashes for two servers and an unreachable third one: it found the missing, extra and changed jobs, matched job names regardless of case, and reported the unreachable server. It was not run against real servers.

dbatools: Copy-DbaAgentJob -WhatIf

The free, community-run dbatools module has no command made only for comparing jobs that we could find, but its copy command can tell you what it would do without doing it:

# Install-Module dbatools -Scope CurrentUser    (once)

# Which jobs from SQL01 are missing on SQL02 (existing names are skipped)
Copy-DbaAgentJob -Source SQL01 -Destination SQL02 -WhatIf

# Which jobs differ in their definition (steps, schedules, properties), and what it would do about them
Copy-DbaAgentJob -Source SQL01 -Destination SQL02 -UseLastModified -WhatIf

# Availability groups: jobs that exist on some replicas only
Compare-DbaAvailabilityGroup -SqlInstance SQL01 -AvailabilityGroup AG1 -Type AgentJob

• Without -UseLastModified, a job whose name already exists on the destination is skipped (or dropped and recreated with -Force), so -WhatIf gives you the jobs that are missing, not the ones that changed.
• With -UseLastModified, according to the dbatools documentation, it compares the job definitions (properties, enabled state, steps and schedules, leaving out IDs, timestamps and version numbers), skips identical jobs, and recreates a job that differs only when the source is newer; when the destination is newer it skips the job with a warning. With -WhatIf that becomes a list of jobs that differ.
• For availability groups, Compare-DbaAvailabilityGroup -Type AgentJob reports jobs present on some replicas and missing on others, with an optional modified-date comparison (-IncludeModifiedDate). It does not say which step or schedule changed.

We did not run dbatools for this guide. The behaviour above is from the dbatools help for Copy-DbaAgentJob and Compare-DbaAvailabilityGroup; check it against the version you have installed (Get-Help Copy-DbaAgentJob -Full), and remember that without -WhatIf these commands change the destination server.

Where comparing jobs this way stops

• Some differences are on purpose. A step that backs up to \\SQL01\Backups, an output file on a local drive, or a command with the server's own name differs between servers by design. The hash cannot tell a planned difference from drift, so you review those jobs by hand every time.
• Names are the key. A job renamed on one server shows up as one job missing and another one extra. Schedules are matched by name within a job, and a schedule shared by several jobs is compared once per job.
• Not everything a job needs is in its rows. The query compares the names of the operator and proxy, not the operator's email address or the proxy's credential. Alerts, credentials, SSIS packages and maintenance plans, the databases and logins a step uses, and whether SQL Server Agent itself is running are all separate checks.
• A snapshot. The script tells you what differs now. It does not run on its own, keep a history, or fix anything; copying the right version to the other server is a second job, done with care.
• Permissions. Reading sysjobsteps and sysschedules needs sysadmin or explicit grants on every server.

Questions

Related

• Sync SQL Server logins and Agent jobs across availability group replicas
• Transfer SQL Server logins with their SID and password
• SQLParity: keep your SQL Servers the same
• Remote SQL Server DBA services
• All guides

Sources (Microsoft Learn): dbo.sysjobs, dbo.sysjobsteps, dbo.sysschedules, dbo.sysjobschedules, SQL Server Agent fixed database roles, HASHBYTES, STRING_AGG, CONCAT, Invoke-Sqlcmd. dbatools: Copy-DbaAgentJob, Compare-DbaAvailabilityGroup.