SQL Server Agent – Moving Job Categories and Schedules

jobssql serversql-server-agent

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?

Best Answer

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.

Related Question