Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2014-11-24   |   Comments (2)   |   Related Tips: More > 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.


Last Updated: 2014-11-24


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, September 30, 2016 - 8:19:19 AM - Tim Back To Top
Thanks.
 

That error indicates that there's a T-SQL batch failing on line 20, which would be this line from the tip:

 
$y_prod = Compare-Object $prod_tplt.Name $cps_tplt.Name
 
However, in your code, I see:
 
$y_prod = Compare-Object $prod_tplt $cps_tplt
 
It's important to understand that this is comparing the job names on one Server to another Server, so when using Compare-Object, we have to pass in a value, such as the name property of the object, to identify if one server has something the other doesn't have.

Thursday, September 29, 2016 - 10:24:00 AM - shureche Back To Top

Hi, 

I've executed our script and i keep getting the same error, powershell 2.0 and later.     

The following exception occurred while trying to enumerate the collection: "An exception occurred while executing a Transact-SQL 

statement or batch.".

C:\Users\Documents\Test 101 agent.ps1:20 char:5

+     $y_prod = Compare-Object $prod_tplt $cps_tplt

+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException

    + FullyQualifiedErrorId : ExceptionInGetEnumerator

 script ....

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 $cps_tplt

    $in = $srvOne.Name + "," + $srvTwo.Name

    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

        }

    }

}

 

$sv_prod = "HPTZ8123\HPTZ8123"

$sv_env = ("SKTZ8123\SKTZ8123","TKTZ8123\TKTZ8123")

 

foreach ($srv in $sv_env)

 

{

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

}


Learn more about SQL Server tools