This function will copy a job from one server to another using powershell.
function Copy-SqlJob{
[cmdletbinding(SupportsShouldProcess=$true)]
param(
[Parameter(Mandatory=$true)]
$ComputerName,
[Parameter(Mandatory=$true)]
$Destination,
[Parameter(Mandatory=$true)]
$JobName
)
begin{
Import-Module SqlPS -DisableNameChecking
}
process{
if($PSCmdlet.ShouldProcess($ComputerName)){
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Destination
$job = $srv.JobServer.Jobs | where{$_.name -eq $JobName}
if($job -ne $null){
throw "Job $JobName already exist on server $Destination"
}
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Destination
if($srv.JobServer.Jobs | where{$_.Name -eq $JobName}){
throw "Job $JobName already exists on server $Destination"
return;
}
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $ComputerName
$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($srv)
$scriptr.Options.DriAll = $True
$scriptr.Options.IncludeHeaders = $True
$job = $srv.JobServer.jobs | where{$_.Name -eq $JobName}
if($job -eq $null){
throw "Job $JobName was not found on server $ComputerName."
return;
}
$strJob = $scriptr.Script($job) | Out-String
Invoke-Sqlcmd -ServerInstance $Destination -Database 'msdb' -Query $strJob
}
else{
Write-Host "Copying Sql Job $JobName from $ComputerName to $Destination"
}
}
end{
}
}
Leave a comment