One of the most common tasks when we are migrating SQL Server instances is to move all jobs from the old instance to the new one. Sometimes we have to face migrations that have involved hundreds of jobs. In order to automatize this task we can use PowerShell. I have been migrating several SQL Server 2000 instances to a new SQL Server 2008R2 recently and this script has been really useful for me. We are talking about 200 jobs in the new instance. Now we are going to see a smaller example with my local instances.

Let’s say that we are working with 2 instances. The first one is a SQL Server 2008 Instance (unnamed). The instance has 30 jobs. We can see all the jobs at the local instance in the following image:

Now we want to move all those jobs that we are using to the new one. A new SQL Server 2008R2 instance called SQL2008R2_1. The new instance is empty, it has no jobs at the beginning (only the syspolicy_purge_history).

As we said before, we are going to move all jobs by using PowerShell. Here is the script that is going to automatize this task. Basically this script will create a t-sql script that will create all jobs in the new instance. Here is the PowerShell code:

param([string]$serverName,[string]$outputPath)

function script-SQLJobs([string]$server,[string]$outputfile)

{

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server("$server")

$db = New-Object Microsoft.SqlServer.Management.Smo.Database

$scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv)

$scrp.Options.ScriptDrops = $FALSE

$scrp.Options.WithDependencies = $TRUE

$jobs = $srv.JobServer.get_Jobs()

$jobs=$jobs | Where-Object {$_.Name -notlike "sys*"}

foreach($job in $jobs)

{

$script=$job.Script()

$script >> $outputfile

"GO" >> $outputfile

}

}

script-SQLJobs $serverName $outputPath

 

As we can see in the code, we are using a function that will create the script wherever the $outputfile variable will be pointing to. Notice that we are filtering all jobs that starts with “sys*” because we don’t want to include syspolicy_purge_history.

Once we have the script there are only two steps left. The first step will be to run the script and generate the *.sql file. And the second one will be to execute the script that we just generated during the first step.

So let’s run the script:

The script has been generated at C:Test and the file name is jobs.sql. If we open the file at SSMS:

Here we can see a piece of code generated. Now if we run this code in the new instance we will create the 30 jobs that we had at the beginning in the old one:

Moving all these jobs took only a few seconds. Now that we have implemented this task with powershell we will be able to apply it in more than one server. In my case, in order to consolidate all jobs in the new instance I had to run it in more than 10 servers and it was really fast too.

Happy Migration!

Cheers!