Copy a Sql Server job via Powershell

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{

    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: