Script and Migrate Agent Jobs between Servers using PowerShell

I'm nearing the end of a SQL Server upgrade project, migrating from SQL Server 2000 to SQL Server 2008 R2. The company uses SQL Server Agent to run about 150 jobs which execute DTS packages to do the work of moving data around. Now, there are any number of ways to move these jobs from one server to the other, but I wanted to be able to exclude certain jobs, make some minor changes before loading them onto the new server, that kind of thing. PowerShell makes that relatively easy to do.

First, most of the jobs were owned by some login, many of which represent people no longer with the company. This happens over time, but should be cleaned up. To quickly modify the jobs to all be owned by 'sa', it's just a few lines of code:

# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst

# Iterate through the jobs to reset the job owner
foreach ($job in $s.JobServer.Jobs) {
	if ($job.Owner -ne 'sa') {
		$job.OwnerLoginName = 'sa'
		$job.Alter()
		}
	}

My scripts usually pass in $inst as the SQL Server instance name as a command-line argument, so I can use the script on any server. Once this script was done all the jobs are owned by sa, and I don't have to worry about enabling old logins on the new server. Next thing is to script out the jobs into .sql files, one for each job. In this case I pass in a base directory path as a second command-line argument. Then I use PowerShell to create the Jobs directory under that (note that if you're using a named instance, I'd recommend using the encode-sqlname cmdlet to turn the $inst variable into an encoded name to prevent the backslash from creating problems):

# Create the Database root directory if it doesn't exist
if (!(Test-Path -path "$base\$inst\Jobs\"))
	{
	New-Item "$base\$inst\Jobs\" -type directory | out-null
	}
$homedir = "$base\$inst\Jobs\"

The next step is to set the properties for the script files:

# Instantiate the Scripter object and set the base properties
$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.ScriptDrops = $False
$scrp.Options.WithDependencies = $False
$scrp.Options.IncludeHeaders = $True
$scrp.Options.AppendToFile = $False
$scrp.Options.ToFileOnly = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.Indexes = $True
$scrp.Options.Triggers = $True

Then we can cycle through the jobs and create scripts for each job on the server.

# Create the script file for each job
foreach ($job in $s.JobServer.Jobs) {
	$jobname = $job.Name
	$scrp.Options.FileName = "$homedir\$jobname.sql"
	$scrp.Script($job)
	}

There are some jobs on the original server that won't move - things like maintenance plan jobs, which I'm replacing with more efficient methods, so having individual script files allows me to simply delete the script files for the jobs not moving to the new server. Once I've done that I have another script which will load the script file into a PowerShell variable, and using invoke-sqlcmd, create the job on the new server. Now, each script file ends with a 'GO' statement, and invoke-sqlcmd doesn't like that, so I want to strip it out, which is easy enough to do with the Replace method. The problem is that it also contains a GOTO command to bypass the error notification. I use the Replace() method to get around that by first replacing GOTO with OTOG, then replacing GO with an empty string, then replacing OTOG again with GOTO.

The other 'tricky' thing I had to do was to convert the script file to a PowerShell string. I use the Get-Content cmdlet to pull the script into a PowerShell object. I then use the here-string construct with the PowerShell object inside the here-string, and that converts it from an object to a System.String object for me, and then I can easily supply that string as the query for invoke-sqlcmd.

# Get all the files with a .sql extension in the directory and cycle through them
$files = get-childitem $basedir | where {$_.Name -like '*.sql'}
foreach ($file in $files) {
	$fname = $file.Name
	$sql = get-content "$basedir$fname"
	$qry = @"
$sql
"@
	$qry = $qry.Replace('GOTO','OTOG')
	$qry = $qry.Replace('GO','')
	$qry = $qry.Replace('OTOG','GOTO')
	invoke-sqlcmd -ServerInstance $inst -Database master -Query $qry
	}

Now the jobs are loaded on the new server, but of course the problem is that they're ACTIVE. Until we go live we don't want them running, so we need to disable them en masse. That's pretty simple as well.

$jobs = $s.JobServer.Jobs 
foreach ($job in $jobs) {$job.IsEnabled = $False; $job.Alter()}

And now the jobs are all in place, and ready to be enabled once the green light is given on the migration.

Allen