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