Find SQL Server Agent Jobs That Exist on One Instance and Not on Another Instance

By: Tim Smith   |   Comments (2)   |   Related: > SQL Server Agent

Problem

We have multiple SQL Servers across development, QA and production and we need to quickly determine what SQL Server Agent Jobs exist in one environment, but not the other.  This will help us determine if these jobs are necessary, currently in development, or being tested in development before landing in production.  In this tip we look at how to use PowerShell to compare SQL Server Agent jobs across different SQL Server instances to find jobs that exist on one server and not another.

Solution

We will create a solution that will compare two environments and output jobs that exist (based on job name) in one server and don't exist in another. Our output will look like the below (in this case, the CSV was opened in Excel):

The above image shows how our jobs compare for each environment we checked.  On ServerOne\IOne we have jobs jm_GetInfo, jm_Growth, j_E_Alert, j_L_Alert which don't exist on ServerTwo\ITwo, and on ServerTwo\ITwo we have jobs j_UpdateFinancials, j_AddInflation, and jm_UserJSON that don't exist on ServerOne\IOne.

By outputing the results to a CSV we can keep the results in the CSV file, open it in Excel (very popular), or import into a SQL Server table - all of which can be done easily.

If we wanted to script the jobs, in addition to logging them in a CSV file, we would need to apply some RegEx to how the names of the jobs are saved because a few developers have a tendency to name their jobs anything, like "JobOne\\SV|RemoveLater|" which is incompatible with saving to a file (try it).

For logging to a CSV file, we will keep the name of the job as is; be aware, that an additional step of scripting would involve stripping non alpha and non numeric characters in some cases.

Script to Find Missing SQL Agent Jobs Between Two Servers

Function CompareServers_Jobs ($srvOne, $srvTwo, $smo, $file, $name = $null)
{
    $nl = [Environment]::NewLine
    Add-Type -Path $smo
   
    $s1 = New-Object Microsoft.SqlServer.Management.SMO.Server($srvOne)
    $s2 = New-Object Microsoft.SqlServer.Management.SMO.Server($srvTwo)
 
    if ($name -eq $null)
    {
        $prod_tplt = $s1.JobServer.Jobs
        $cps_tplt = $s2.JobServer.Jobs
    }
    else
    {
        $prod_tplt = $s1.JobServer.Jobs | Where-Object {$_.Name -like "$name*" }
        $cps_tplt = $s2.JobServer.Jobs | Where-Object {$_.Name -like "$name*" }
    }
 
    $y_prod = Compare-Object $prod_tplt.Name $cps_tplt.Name
    $in = $srvOne + "," + $srvTwo
    Add-Content $file $in
 
    foreach ($i in $y_prod)
    {
        if ($i.SideIndicator -eq "<=")
        {
            $v = $i.InputObject + ","
            Add-Content $file $v
        }
        elseif ($i.SideIndicator -eq "=>")
        {
            $v = "," + $i.InputObject
            Add-Content $file $v
        }
    }
}
 
CompareServers_Jobs -srvOne "ServerOne\IOne" -srvTwo "ServerTwo\ITwo" -smo "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" -file "C:\Environments\jobs.csv"

In our function, we pass in the variable for the "standard" server (in most cases, production), $srvOne, which is how each environment should look (the template), the compared "update" server, $srvTwo, the server that may not match the "standard" server, the location to the SMO library, the file location to create the CSV (if the CSV doesn't exist, PowerShell will create it), and a optional parameter $name, which the user can add when calling the function to filter jobs.

I've worked in many environments where each environment had their own set of jobs (for example, DBAs and System Administrators had separate jobs) and we could apply an additional filter on the name of the job using the $name parameter in this case; for instance, if I wanted to only look at DBA jobs, I would add -name "DBA_", assuming that DBA jobs had a leading DBA_ delineating them from other jobs (we could invert the -like to -notlike and ignore jobs with a specific name). The function does not output any job that exists in both environments; for example, if both servers had a backup job, this script would not output it in the CSV file.

Looping Through Multiple SQL Servers To Find Missing Jobs

Suppose we have eleven environments we want to compare. We want to compare each of these environments to what actually exists in production. We can use the same script above, except that we'll loop through each of these environments to compare as shown below:

$sv_prod = "SVPDR001"
$sv_env = ("SVDER001","SVDER002","SVDER007","SVQR001","SVQR002","SVQR007","SVTPDR001","SVTPDR001","SVTPDR002","SVTPDR007","SVTPDR008")

foreach ($srv in $sv_env)
{
    CompareServers_Jobs -srvOne $sv_prod -srvTwo $srv -smo "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" -file "C:\Environments\jobs.csv"
}

Next Steps

• What SQL Server Agent Jobs are you responsible for in your environment, if not all of them?
• How can you use the above to monitor new/existing jobs for each environment, from development to production?
• Further modify this process to also script out the actual Job.
• Add additional filtering options to this process.
• Review these other PowerShell tips.

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips