Part of the DBA-Tools Project.
Recreating Every SQL Agent Job for a Migration, in the Right Order
Moving a SQL Server workload to a new instance means recreating every SQL Agent job, unless you script it, and getting the order wrong (steps before the job header, schedule before the start step) is exactly the kind of easy-to-miss detail manual recreation invites. This script reads the source instance’s own system catalogs and produces sp_add_job / sp_add_jobstep / sp_add_schedule DDL text, reviewed in your own SSMS window, then run against the target once you’re satisfied it’s correct. Nothing touches the target automatically.
Why a Generated Job Recreation Script Matters
- Job, steps, start step, and schedule have a required creation order. Getting it wrong by hand across a maintenance window is exactly when a job silently ends up broken
- Generated DDL means you review the exact
sp_add_jobandsp_add_jobstepcalls before they run, not a black-box migration tool doing it for you - This is a generator, not a full migration platform. It doesn’t move data or orchestrate cutover, it solves the specific problem of recreating job definitions correctly on the target
When to Run This Script
- Migrating databases to new hardware or a new SQL Server version
- Consolidating several instances onto one server
- Standing up a DR or failover target that needs the same job schedule as production
- Building a side-by-side test environment before a cutover
The Script
Produces sp_add_job / sp_add_jobstep / sp_add_schedule DDL for every job on the source instance, in the order needed to recreate it correctly on the target: job header, then steps, then start step, then schedule, then attach to (local).
/*
Script Name : Generate-AgentJobScript
Category : migration
Purpose : Generate sp_add_job DDL to recreate all SQL Agent jobs on the target server.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-generate-agent-job-script/)
Requires : SQLAgentUserRole in msdb (or sysadmin)
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ... per-job cursor assembling job/step/schedule DDL, full script in the repo ...
SELECT @ddl AS ddl;
(The full script is in the repo, link below.)
How To Run From The Repo
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Set the source server for the session:
.\tools\local-sql\Set-SqlConnection.ps1 -ServerInstance PROD01\SQL2019
# Generate the DDL, review the output before running it on the target:
.\powershell\migration\Generate-AgentJobScript.ps1
# Output: output-files\migration\*.sql
This script lives in the repo at:
Verified Against a Real Instance
Ran for real: 895 lines, every real job on the instance, including the disabled maintenance jobs from the maintenance job framework. Took one job’s generated block, created a real test job with a step and an attached schedule, confirmed all three in msdb, then deleted it. No bug turned up in this generator, the creation order in the generated DDL matched what sp_add_job / sp_add_jobstep / sp_add_schedule actually require.
Best Practices
- Always review the generated DDL before running it on the target. Nothing here executes automatically.
- Map any
owner_login_namevalues in the output to real logins that exist on the target before running it, if the source job owner doesn’t exist on the target, job creation fails. - Run this after Generate Login Script, so job owners already resolve on the target.
- Confirm job success after migration with Get Maintenance Job Status, recreated doesn’t automatically mean succeeding on the new schedule.
Related Scripts
You may also find these scripts useful:
- SQL Server Migration Script Generators (hub)
- Generate Login Script
- Get Migration Risk Assessment
- SQL Agent Job Failure Summary
Frequently Asked Questions
What happens if a job’s owner login doesn’t exist on the target?
sp_add_job fails outright. Run Generate Login Script first so every job owner already resolves on the target before recreating jobs.
Does this recreate job history, or just the job definitions?
Just the definitions, job, steps, start step, and schedule. Job history is operational data, not configuration, and isn’t something you’d want carried over from the source instance anyway.
Summary
One generator, one job: recreate every SQL Agent job on the target in the order the pieces actually need to exist, job header, steps, start step, then schedule. Verified with a real test job created from the generated DDL and confirmed in msdb. Run this after logins are recreated, so job owners resolve cleanly.
Leave a Reply