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:


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?

3 Answers 3


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.

  • 1
    This is interesting as it actually works outside of SQL and could be part of a deployment task!
    – Viggos
    Jan 11, 2016 at 13:59

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
  • Have you used this in conjunction with DSC deployment?
    – Viggos
    Jan 12, 2016 at 11:20
  • I haven't used with DSC, but should be straight forward with some tweaks.
    – Kin Shah
    Jan 12, 2016 at 13:54

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.