I'm trying to query one the source instance and copy across any missing jobs into the destination instance. Everything seems to work except passing the name into -job. Can anyone suggest a fix or point me in the right direction?

$dest = "Dest\Instance"
$Source = "Source\Instance"
$MissingJob = Invoke-DbaQuery -SqlInstance $Source -ReadOnly -Query "
use msdb

select name
from dbo.sysjobs
where name COLLATE Latin1_General_CI_AS not in (select name
from [Dest\Instance].[msdb].[dbo].[sysjobs]

foreach ($Name in $MissingJob){
Copy-DbaAgentJob -Source NCCSQLDEVW2K162\NCCSQL2017DBA01 -Destination $dest 
-Job $Name -SourceSqlCredential $cred -Force 

No need to reinvent the wheel with the linked server query. dbatools also provides the Get-DbaAgentJob function to enumerate jobs on both agents. As well, the $Job parameter accepts an array as input, meaning you don't need the for-loop. Assuming the job name is the keying attribute as in your sample, you could try something like the following.

$sourceJobs = Get-DbaAgentJob -SqlInstance '[Source\Instance]'
$destJobs   = Get-DbaAgentJob -SqlInstance '[Dest\Instance]'

$missingJobs = $sourceJobs | Where {
    $_.Name -notin $destJobs.Name

Copy-DbaAgentJob `
    -Source '[Source\Instance]' `
    -Destination '[Dest\Instance]' `
    -Job $missingJobs

Although strictly speaking, Copy-DbaAgentJob already checks for duplicates, so you could slim it down even further by just omitting the -Job parameter entirely and just run something like...

Copy-DbaAgentJob `
    -Source '[Source\Instance]' `
    -Destination '[Dest\Instance]' `

...which will show you which jobs were skipped because they already exist.

powershell session showing Copy-DbaAgentJob output

  • thanks man, it was just my lack experience with powershell and dbtools. So I kind of fudging it. But that works way better thank you so much :-) Apr 8 '20 at 11:16
  • 2
    🙂 i recommend the dbatools slack channel as well for a really helpful positive place to ask questions about the module day-to-day Apr 8 '20 at 11:50
  • Cheers mate i will Apr 9 '20 at 7:32

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.