2

I'm currently building a process in Powershell where we can copy SQL Server Agent jobs from one server to another server just by entering the server names. The script uses the SMO library to obtain access to both job servers, scripts the jobs that don't exist on the "other" server, then runs those scripts (under a SqlConnection and SqlCommand). The issue is how to check if a job exists on ServerOne compared to ServerTwo.

I've tried four different approaches: build an array with the jobs from ServerTwo (the compare server) and see if that array contains a job from ServerOne; I also tried the like operator with the same logic; and, using arrays, I've used Compare-Object after loading two arrays with job names from both servers and measuring which jobs don't exist in the other (produces everything too). Finally, I looped through each job on ServerTwo and looped through each job on ServerOne to see if they equal each other (which is the below code). This will produce everything because each job doesn't equal each job:

$ServerName = "S1\I"
$ComServer = "S2\I"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
$com = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ComServer


foreach ($a in $com.JobServer.Jobs)
{
    $a = $a.Name.ToString() 
    foreach ($j in $srv.JobServer.Jobs) 
    { 
        $j = $j.Name.ToString()

        if ($a -like $j)
        {
            Write-Host $j
        }
    }   
}

Is there another approach to this where I can check if a job exists on one server vs. another (as a note, I already know how to script the job, execute it and so on; the part of making sure the job doesn't already exist is the problem here)?

  • Isn't it just a matter of comparing job name to job name? – billinkc Feb 5 '14 at 14:42
  • @billinkc Yeah, that's what I tried to do with Compare-Object, but it produced all jobs, which is incorrect. – Question3CPO Feb 5 '14 at 14:44
5

I see from the comments that you've already given Compare-Object a try, but that really should be working. Below is a working example showing this:

$SqlServer1Name = "SQL1"
$SqlServer2Name = "SQL2"

$SqlServer1 = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer1Name)
$SqlServer2 = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer2Name)

$SqlServer1JobListing = $SqlServer1.JobServer.Jobs | 
    Select-Object -ExpandProperty Name

$SqlServer2JobListing = $SqlServer2.JobServer.Jobs |
    Select-Object -ExpandProperty Name

Compare-Object $SqlServer1JobListing $SqlServer2JobListing |
    Where-Object {$_.SideIndicator -eq "<="} |
    Select-Object -ExpandProperty InputObject

This will output all of the jobs that live on SQL1 and not on SQL2. This comparison is by name only, not taking into consideration any other SQL Server Agent Job attributes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.