Moving SQL Server Agent Job Categories and Schedules

Question

I want to copy a set of Job Categories and Schedules between MS SQL Servers (2012/2014).

select * FROM msdb.dbo.sysschedules

Returns all schedules.

select * FROM msdb.dbo.syscategories
WHERE category_id IN (SELECT DISTINCT category_id FROM msdb.dbo.sysjobs)

Returns all my job categories.

To add them i could use the stored procedures:

sp_add_category
sp_add_jobschedule

I can get it done with this information, but it would almost be as tedious as re-doing them manually.

How can i do this in a more effective way?

Answer 1

You can use PowerShell to script out all the SQL Agent jobs on a server.

$output = "d:\ScheduledJobs.sql"
Remove-Item $output             
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\JobServer\Jobs
# loop through jobs appending output
dir | %{$_.script() + "`n`nGO`n`n" | out-file -append $output}

This script does include the job schedules that were set up on the origin server.

From the scripted output you can pick out the SQL Agent jobs that you want to duplicate and create them on the other server.

Answer 2

Another cool powershell utility that I use everyday for server migrations is Start-SqlServerMigration.ps1 written by Chrissy LeMaire

It is very adaptive with different options.

Eg. In your scenario for migrating jobs with schedule, you can use

.PARAMETER MigrateJobServer

Migrates all job server objects, including proxy accounts, job schedules, shared schedules, alert system, job categories, operator categories, alert categories, alerts, target server groups, target servers, operators, and jobs. Existing objects will not be deleted, and no -force option is available.

e.g. The PS below code will move all the databases (overwrite if the same name databases exists since it uses force parameter) from Source sqlserver\instance to Destination sqlcluster. It will exclude Northwind, pubs databases. It will migrate all the logins except nwuser, pubsuser, "corp\domain admins", will migrate all the sql agent jobs, will export the sp_configure options from source to destination server.

.\Start-SqlServerMigration.ps1 -Source sqlserver\instance -Destination sqlcluster -AllUserDBs -ExcludeDBs Northwind, pubs -IncludeSupportDBs -force -AllLogins -ExcludeLogins nwuser, pubsuser, "corp\domain admins"  -MigrateJobServer -ExportSPconfigure -UseSqlLoginSource -UseSqlLoginDestination

Answer 3

Script SQL Config can do that for you. It creates a script for each SQL Agent job, which also contains the creation of the categories needed for each job.