SQL Server Using Powershell to Move SQL Agent Jobs Across Instances

Migrating jobs from one server to other can be easily done with powershell. Let’s look into the script. At first, include reference to the relevant SMO assemblies and then create the source and destination sql instance objects.

[System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
#get the source server object
$ssrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sourceserver
#get the destination server object
$dsrv = new-object('Microsoft.SqlServer.Management.Smo.Server') $destinationserver

The variable $sourceserver specifies the source server instance where as the variable $destinationserver specifies the destination server.The next step is to create scripting object to script the SQL Agent jobs.

# get the scripter object and set relevant properties
$scripter = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') $ssrv
$scripter.Options.ScriptDrops = $False
$scripter.Options.WithDependencies = $False
$scripter.Options.IncludeHeaders = $True
$scripter.Options.AppendToFile = $True

With this being done, next step is to iterate through all jobs in the source server.

foreach ($job in $ssrv.JobServer.Jobs) {
    $jobname = $job.Name
    # change the owner to sa in case job owner at source instance
    # doesn't exists at destination server.
   if ($job.Owner -ne 'sa') {
        $job.OwnerLoginName = 'sa'
    }
    #script job only if it doesn't exists at desitnation server.
    $djobs=$dsrv.JobServer.Jobs | Where-Object {$_.name -eq $job.name}

The above script iterates through all the jobs in source server; sets the owner of the job to sa if it’s not sa. This is done to avoid error in case the owner (login) at source server doesn’t exist at the destination server. To sync logins between instances you can use script from my other blog. It may be a possibility that some of the job coexist at both source and destination SQL instance. So, to migrate only non-existing job I filtered out the coexisting jobs in object $djobs. The next step is to create and script out the filtered jobs from the above script.

if(!$djobs)
    {
    # specify the script location
    $scripter.Options.FileName = "D:\Ahmad\SQLServerGeeks\Blogs\Powershell\jobs.sql"
    $sc=$scripter.Script($job)
    #execute the job query on destination server.
    $dsrv.ConnectionContext.ExecuteNonQuery($sc[0])

The above script checks if the object $djobs is empty and if it is, then it sets the script location and gets the job script into a stringcollection object $sc. The job script/query is then executed on destination server using the executenonquery method.

The complete script is available at http://sdrv.ms/14VdcKs

 
Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

3 Comments on “SQL Server Using Powershell to Move SQL Agent Jobs Across Instances”

  1. I think this is a great article. I’m very new to PowerShell.

    However, I’m going to use this, as we need to merge two instances into one. If we have two physically separate servers, with only a default instance on each and need to move the databases from one to the other (including agent jobs)… What should the ‘server variables’ look like? In other words, what should I equate $sourceserver to? Is it the //server-name/ ?

Leave a Reply

Your email address will not be published. Required fields are marked *