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;
DECLARE @ddl NVARCHAR(MAX) = N'';
DECLARE @crlf NCHAR(2) = CHAR(13) + CHAR(10);
SET @ddl = @ddl
+ N'-- ================================================================' + @crlf
+ N'-- SQL Agent Job Migration Script' + @crlf
+ N'-- Source : ' + @@SERVERNAME + @crlf
+ N'-- Generated: ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + @crlf
+ N'-- Review owner_login_name values — map to valid logins on target.' + @crlf
+ N'-- ================================================================' + @crlf + @crlf
+ N'USE msdb;' + @crlf + N'GO' + @crlf + @crlf;
-- ── Per job ───────────────────────────────────────────────────────────────────
DECLARE @job_id UNIQUEIDENTIFIER;
DECLARE @job_name NVARCHAR(128);
DECLARE @enabled TINYINT;
DECLARE @desc NVARCHAR(512);
DECLARE @category NVARCHAR(128);
DECLARE @owner NVARCHAR(128);
DECLARE @start_step INT;
DECLARE @nl_email INT; DECLARE @nl_netsend INT; DECLARE @nl_page INT; DECLARE @nl_eventlog INT;
DECLARE @op_email NVARCHAR(128); DECLARE @op_netsend NVARCHAR(128); DECLARE @op_page NVARCHAR(128);
DECLARE job_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT
j.job_id,
j.name,
j.enabled,
ISNULL(j.description, N''),
ISNULL(c.name, N'[Uncategorized (Local)]'),
ISNULL(SUSER_SNAME(j.owner_sid), N'sa'),
j.start_step_id,
j.notify_level_email,
j.notify_level_netsend,
j.notify_level_page,
j.notify_level_eventlog,
ISNULL(CAST(n_email.name AS NVARCHAR(128)), N''),
ISNULL(CAST(n_ns.name AS NVARCHAR(128)), N''),
ISNULL(CAST(n_page.name AS NVARCHAR(128)), N'')
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT JOIN msdb.dbo.sysoperators n_email ON j.notify_email_operator_id = n_email.id
LEFT JOIN msdb.dbo.sysoperators n_ns ON j.notify_netsend_operator_id = n_ns.id
LEFT JOIN msdb.dbo.sysoperators n_page ON j.notify_page_operator_id = n_page.id
ORDER BY j.name;
OPEN job_cur;
FETCH NEXT FROM job_cur INTO
@job_id, @job_name, @enabled, @desc, @category, @owner, @start_step,
@nl_email, @nl_netsend, @nl_page, @nl_eventlog,
@op_email, @op_netsend, @op_page;
WHILE @@FETCH_STATUS = 0
BEGIN
-- ── Job header ────────────────────────────────────────────────────────────
SET @ddl = @ddl
+ N'-- Job: ' + @job_name + @crlf
+ N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N''' + REPLACE(@job_name, N'''', N'''''') + N''')' + @crlf
+ N'BEGIN' + @crlf
+ N' DECLARE @job_id UNIQUEIDENTIFIER;' + @crlf
+ N' EXEC msdb.dbo.sp_add_job' + @crlf
+ N' @job_name = N''' + REPLACE(@job_name, N'''', N'''''') + N''',' + @crlf
+ N' @enabled = ' + CAST(@enabled AS NVARCHAR(1)) + N',' + @crlf
+ N' @description = N''' + REPLACE(@desc, N'''', N'''''') + N''',' + @crlf
+ N' @category_name = N''' + REPLACE(@category, N'''', N'''''') + N''',' + @crlf
+ N' @owner_login_name = N''' + REPLACE(@owner, N'''', N'''''') + N''',' + @crlf
+ N' @notify_level_eventlog = ' + CAST(@nl_eventlog AS NVARCHAR(1)) + N',' + @crlf
+ N' @notify_level_email = ' + CAST(@nl_email AS NVARCHAR(1)) + N',' + @crlf
+ N' @notify_level_netsend = ' + CAST(@nl_netsend AS NVARCHAR(1)) + N',' + @crlf
+ N' @notify_level_page = ' + CAST(@nl_page AS NVARCHAR(1)) + @crlf
+ CASE WHEN @op_email <> N'' THEN N' ,@notify_email_operator_name = N''' + REPLACE(@op_email, N'''',N'''''') + N'''' + @crlf ELSE N'' END
+ CASE WHEN @op_netsend <> N'' THEN N' ,@notify_netsend_operator_name = N''' + REPLACE(@op_netsend, N'''',N'''''') + N'''' + @crlf ELSE N'' END
+ CASE WHEN @op_page <> N'' THEN N' ,@notify_page_operator_name = N''' + REPLACE(@op_page, N'''',N'''''') + N'''' + @crlf ELSE N'' END
+ N' ,@job_id = @job_id OUTPUT;' + @crlf + @crlf;
-- ── Job steps ─────────────────────────────────────────────────────────────
SELECT @ddl = @ddl
+ N' EXEC msdb.dbo.sp_add_jobstep' + @crlf
+ N' @job_name = N''' + REPLACE(@job_name, N'''', N'''''') + N''',' + @crlf
+ N' @step_id = ' + CAST(s.step_id AS NVARCHAR(5)) + N',' + @crlf
+ N' @step_name = N''' + REPLACE(s.step_name, N'''', N'''''') + N''',' + @crlf
+ N' @subsystem = N''' + s.subsystem + N''',' + @crlf
+ N' @command = N''' + REPLACE(ISNULL(s.command, N''), N'''', N'''''') + N''',' + @crlf
+ N' @database_name = N''' + ISNULL(s.database_name, N'master') + N''',' + @crlf
+ N' @on_success_action = ' + CAST(s.on_success_action AS NVARCHAR(1)) + N',' + @crlf
+ N' @on_success_step_id= ' + CAST(s.on_success_step_id AS NVARCHAR(5)) + N',' + @crlf
+ N' @on_fail_action = ' + CAST(s.on_fail_action AS NVARCHAR(1)) + N',' + @crlf
+ N' @on_fail_step_id = ' + CAST(s.on_fail_step_id AS NVARCHAR(5)) + N',' + @crlf
+ N' @retry_attempts = ' + CAST(s.retry_attempts AS NVARCHAR(5)) + N',' + @crlf
+ N' @retry_interval = ' + CAST(s.retry_interval AS NVARCHAR(5)) + N';' + @crlf + @crlf
FROM msdb.dbo.sysjobsteps s
WHERE s.job_id = @job_id
ORDER BY s.step_id;
-- ── Set start step ────────────────────────────────────────────────────────
SET @ddl = @ddl
+ N' EXEC msdb.dbo.sp_update_job @job_name = N''' + REPLACE(@job_name, N'''', N'''''') + N''', @start_step_id = ' + CAST(@start_step AS NVARCHAR(5)) + N';' + @crlf + @crlf;
-- ── Schedules ─────────────────────────────────────────────────────────────
SELECT @ddl = @ddl
+ N' IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysschedules WHERE name = N''' + REPLACE(sc.name, N'''', N'''''') + N''')' + @crlf
+ N' BEGIN' + @crlf
+ N' EXEC msdb.dbo.sp_add_schedule' + @crlf
+ N' @schedule_name = N''' + REPLACE(sc.name, N'''', N'''''') + N''',' + @crlf
+ N' @enabled = ' + CAST(sc.enabled AS NVARCHAR(1)) + N',' + @crlf
+ N' @freq_type = ' + CAST(sc.freq_type AS NVARCHAR(10)) + N',' + @crlf
+ N' @freq_interval = ' + CAST(sc.freq_interval AS NVARCHAR(10)) + N',' + @crlf
+ N' @freq_subday_type = ' + CAST(sc.freq_subday_type AS NVARCHAR(10)) + N',' + @crlf
+ N' @freq_subday_interval= ' + CAST(sc.freq_subday_interval AS NVARCHAR(10))+ N',' + @crlf
+ N' @freq_relative_interval=' + CAST(sc.freq_relative_interval AS NVARCHAR(10))+N',' + @crlf
+ N' @freq_recurrence_factor=' + CAST(sc.freq_recurrence_factor AS NVARCHAR(10))+N',' + @crlf
+ N' @active_start_date = ' + CAST(sc.active_start_date AS NVARCHAR(10)) + N',' + @crlf
+ N' @active_end_date = ' + CAST(sc.active_end_date AS NVARCHAR(10)) + N',' + @crlf
+ N' @active_start_time = ' + CAST(sc.active_start_time AS NVARCHAR(10)) + N',' + @crlf
+ N' @active_end_time = ' + CAST(sc.active_end_time AS NVARCHAR(10)) + N';' + @crlf
+ N' END' + @crlf
+ N' EXEC msdb.dbo.sp_attach_schedule @job_name = N''' + REPLACE(@job_name, N'''', N'''''') + N''', @schedule_name = N''' + REPLACE(sc.name, N'''', N'''''') + N''';' + @crlf + @crlf
FROM msdb.dbo.sysjobschedules js
INNER JOIN msdb.dbo.sysschedules sc ON js.schedule_id = sc.schedule_id
WHERE js.job_id = @job_id;
-- ── Add to local server ───────────────────────────────────────────────────
SET @ddl = @ddl
+ N' EXEC msdb.dbo.sp_add_jobserver @job_name = N''' + REPLACE(@job_name, N'''', N'''''') + N''', @server_name = N''(local)'';' + @crlf
+ N'END' + @crlf + N'GO' + @crlf + @crlf;
FETCH NEXT FROM job_cur INTO
@job_id, @job_name, @enabled, @desc, @category, @owner, @start_step,
@nl_email, @nl_netsend, @nl_page, @nl_eventlog,
@op_email, @op_netsend, @op_page;
END
CLOSE job_cur;
DEALLOCATE job_cur;
SELECT @ddl AS ddl;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
- DBA Scripts: The Complete Guide, the map across every script on this site
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