A reusable T-SQL template for creating SQL Agent jobs the same way every time

#Why script jobs

• The same job on every server, built the same way.
• Source control shows who changed what.
• Rebuilding a server or AG replica takes minutes.

#The template

USE msdb;
SET XACT_ABORT ON;
BEGIN TRAN;

DECLARE @JobName      sysname       = N'Maintenance - Update Statistics',
        @Category     sysname       = N'Database Maintenance',
        @Owner        sysname       = N'sa',
        @Description  nvarchar(512) = N'Nightly statistics update. Owner: DBA team. Ref: STD-012',
        @Operator     sysname       = N'DBA Team',
        @Database     sysname       = N'master',
        @Command      nvarchar(max) = N'EXEC dbo.usp_UpdateStats @Mode = ''sampled'';',
        @StartTime    int           = 013000,       -- HHMMSS
        @job_id       uniqueidentifier;

-- Category
IF NOT EXISTS (SELECT 1 FROM dbo.syscategories WHERE name = @Category AND category_class = 1)
  EXEC dbo.sp_add_category @class = N'JOB', @type = N'LOCAL', @name = @Category;

-- Drop and recreate (keeps the script re-runnable)
SELECT @job_id = job_id FROM dbo.sysjobs WHERE name = @JobName;
IF @job_id IS NOT NULL
  EXEC dbo.sp_delete_job @job_id = @job_id, @delete_unused_schedule = 1;

EXEC dbo.sp_add_job
     @job_name = @JobName, @enabled = 1, @description = @Description,
     @category_name = @Category, @owner_login_name = @Owner,
     @notify_level_email = 2, @notify_email_operator_name = @Operator,
     @notify_level_eventlog = 2,
     @job_id = @job_id OUTPUT;

EXEC dbo.sp_add_jobstep
     @job_id = @job_id, @step_id = 1, @step_name = N'Update statistics',
     @subsystem = N'TSQL', @database_name = @Database, @command = @Command,
     @retry_attempts = 1, @retry_interval = 5,
     @on_success_action = 1, @on_fail_action = 2,
     @flags = 4;   -- include step output in history

EXEC dbo.sp_add_jobschedule
     @job_id = @job_id, @name = N'Daily 01:30',
     @enabled = 1, @freq_type = 4, @freq_interval = 1,
     @active_start_time = @StartTime;

EXEC dbo.sp_add_jobserver @job_id = @job_id, @server_name = N'(local)';

COMMIT;

#Notes on the choices

#Keeping history: update instead of recreate

If the job exists, update in place:

IF @job_id IS NOT NULL
BEGIN
  EXEC dbo.sp_update_job @job_id = @job_id, @description = @Description, @category_name = @Category;
  EXEC dbo.sp_update_jobstep @job_id = @job_id, @step_id = 1, @command = @Command;
END

Choose one style per team and stick to it.

#Multiple steps

Add more sp_add_jobstep calls with @step_id = 2, 3 .... Set @on_success_action = 3 (go to next step) on every step except the last.

#Deploying to many servers

$servers = 'SQL01','SQL02','SQL03'
foreach ($s in $servers) {
  Invoke-Sqlcmd -ServerInstance $s -InputFile .\jobs\maintenance-update-statistics.sql -TrustServerCertificate
}

For AGs, deploy to every replica and add a primary-replica check in the step if the job should run only on the primary.

Tip

Start from SSMS's "Script Job as → CREATE" output for complex jobs, then trim it to this template. SSMS output is verbose but always correct.

#Checklist

• One script per job in source control
• Category, description, non-personal owner
• Failure notification + event log
• Targeted to local server
• Re-runnable without errors