Skip to content

July 5, 2016

Copy a Sql Server job via Powershell

by Scott Newman

This function will copy a job from one server to another using powershell. 

function Copy-SqlJob{
        Import-Module SqlPS -DisableNameChecking


            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Destination
            $job = $srv.JobServer.Jobs | where{$ -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"

	        $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 = $ | where{$_.Name -eq $JobName} 
            if($job -eq $null){
                throw "Job $JobName was not found on server $ComputerName."
            $strJob = $scriptr.Script($job) | Out-String
            Invoke-Sqlcmd -ServerInstance $Destination -Database 'msdb' -Query $strJob
            Write-Host "Copying Sql Job $JobName from $ComputerName to $Destination"

Read more from Uncategorized

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: