Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?

share|improve this question
    
Isn't it just a matter of comparing job name to job name? –  billinkc Feb 5 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 at 14:44
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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