DBA Scripts: Generate Backup Jobs

Peter Whyte

–

Part of the DBA-Tools Project.

A Readable Generator for Full Backups, Log Backups, and Cleanup

If you’re already running Ola Hallengren’s maintenance solution, this won’t replace it, but the approach here might still be useful: a small, readable T-SQL generator that builds a backup job framework as plain sp_add_job / sp_add_jobstep DDL you can review line by line before you ever run it.

This script generates DDL for three jobs: DBA - Backup - FULL (daily full backup of every online, writable user database), DBA - Backup - LOG (log backups on a short interval, skipping SIMPLE-recovery databases automatically), and DBA - Backup - Cleanup (deletes backup files past retention). It only produces text, nothing touches msdb until you review the output and run it yourself.

Why a Generated, Readable Backup Framework Matters

  • A backup framework you can’t read is one you can’t fully trust, and most DBAs run someone else’s backup jobs without ever fully reading what they do
  • The generator separates “decide the parameters” from “run the DDL”: you review the exact sp_add_job calls in your own SSMS window before anything touches msdb
  • Every job step is copy/paste T-SQL or PowerShell, not a black-box procedure call, when something needs troubleshooting mid-incident, you’re reading the actual backup command, not stepping into unfamiliar code
  • Idempotent generation: the script starts with IF EXISTS ... sp_delete_job before recreating each job, so re-running after changing a parameter cleanly replaces the old job rather than leaving duplicates

When to Run This Script

  • Standing up a backup framework on a new instance that has none
  • Replacing an ad-hoc set of backup jobs with a documented, regenerable one
  • Any time a backup parameter (retention, schedule, log interval) needs to change, edit the parameter block and regenerate rather than hand-editing job steps in SSMS
  • Auditing what an existing “DBA -” backup job actually does, by reading the generator that produced it

The Script

/*
Script Name : Generate-BackupJobs
Category    : maintenance
Purpose     : Generates SQL Agent DDL to create three scheduled maintenance jobs:
              DBA - Backup - FULL     daily full backup of all online user databases
              DBA - Backup - LOG      transaction log backups on a short interval (default 15 min)
              DBA - Backup - Cleanup  removes old backup files based on retention policy
              Edit the parameters section, review the output, then run on the target instance.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-backup-jobs/)
Requires    : VIEW ANY DATABASE
Notes       : Output requires sysadmin or SQLAgentOperatorRole on the target instance.
              The backup root path must exist on the SQL Server host before jobs first run.
              Log backup job skips SIMPLE recovery model databases automatically.
              Cleanup step uses the PowerShell subsystem — SQL Agent service account needs
              delete rights on the backup folder.
              On AGs: use @FullBackupPreference to avoid full backups on the wrong replica;
              this script backs up whatever instance it runs on.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @BackupRootPath    nvarchar(260) = N'D:\SQLBackups';
DECLARE @FullRetentionDays int           = 14;      -- full backup files older than this are deleted
DECLARE @LogRetentionHours int           = 48;      -- log backup files older than this are deleted
DECLARE @FullScheduleHour  tinyint       = 2;       -- 0-23 — hour for the daily full backup
DECLARE @LogIntervalMins   tinyint       = 15;      -- log backup frequency in minutes
DECLARE @JobOwner          sysname       = N'sa';
DECLARE @CategoryName      nvarchar(128) = N'Database Maintenance';
-- ─────────────────────────────────────────────────────────────────────────────

(The full script, including the DDL-assembly section, is in the repo, link below.)

How To Run From The Repo

Clone DBA Tools, initialize and run the generator, review the DDL it produces, then execute it against the target instance:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Generate the DDL — review the output before running it:
.\run.ps1 Generate-BackupJobs

# To generate against a remote server:
.\run.ps1 Generate-BackupJobs -ServerInstance SQLSERVER01

This script lives in the repo at:

The Bug: SQL Agent’s CmdExec Subsystem Does Not Wrap Your Command in a Shell

DBA - Backup - Cleanup (originally built on forfiles.exe via the CmdExec subsystem) took three attempts to actually fix, because each fix revealed a deeper layer of the same misconception, tested to destruction against a real local SQL Server 2025 instance rather than trusted on read-through alone.

  1. First failure: forfiles: Invalid argument/option - '2>nul'. The command tried to redirect forfiles‘ “nothing matched” noise with 2>nul, assuming CmdExec runs inside a shell that interprets redirection. It doesn’t, 2>nul gets passed to forfiles.exe as a literal argument.
  2. Second failure, after removing the redirection: No files found with the specified search criteria. Process Exit Code 1. A trailing exit 0 on its own line was meant to mask forfiles‘ own benign non-zero exit code when nothing matched, but CmdExec only ever runs the first line of a multi-line command string, so the exit 0 never executed at all.
  3. Third failure, after chaining everything onto one line with &: forfiles: Invalid argument/option - '&'. Same root cause as the first failure. CmdExec passes the command directly to CreateProcess, with no shell in between, so & chaining is just as invalid as 2>nul redirection.

The actual fix wasn’t another layer of shell-escaping, it was recognizing that forfiles + CmdExec was the wrong tool for a multi-step cleanup command. The rewritten step uses SQL Agent’s PowerShell subsystem instead, which runs the command text as a real PowerShell script rather than a raw process invocation, so multi-line commands and pipes just work:

DECLARE @cleanCmd nvarchar(max) =
    N'Get-ChildItem -Path ' + @q + @BackupRootPath + @q + N' -Filter ' + @q + N'*_FULL_*.bak' + @q
    + N' -ErrorAction SilentlyContinue | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-'
    + CAST(@FullRetentionDays AS nvarchar(5)) + N') } | Remove-Item -Force' + @crlf
    + N'Get-ChildItem -Path ' + @q + @BackupRootPath + @q + N' -Filter ' + @q + N'*_LOG_*.trn' + @q
    + N' -ErrorAction SilentlyContinue | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-'
    + CAST(@logRetentionDays AS nvarchar(5)) + N') } | Remove-Item -Force';

Confirmed against the real job history after the fix:

job_name               : DBA - Backup - Cleanup
run_status             : 1 (Succeeded)
message                : Executed as user: NT Service\SQLSERVERAGENT.
                         The step did not generate any output. Process Exit Code 0.
                         The step succeeded.

Example Output — Verified Job Runs

Real results from msdb.dbo.sysjobhistory after applying the fix above, run against a local SQL Server 2025 instance:

Job run_status Duration Message
DBA - Backup - Cleanup Succeeded 4-5s Process Exit Code 0. The step succeeded.

Understanding the Results

  • run_status = 1 in sysjobhistory means the step succeeded, check this before trusting a job’s schedule to mean it’s actually working, exactly the gap covered in Get Maintenance Job Status
  • The Cleanup job’s success message being generic (Process Exit Code 0) is expected for the PowerShell subsystem when nothing is deleted, Remove-Item doesn’t report a count of what it removed unless asked to
  • A CmdExec step failing with the raw tool’s own “invalid argument” error, not a SQL Server error, is the tell that the command assumed shell features CmdExec doesn’t provide

Best Practices

  • Review the generated DDL before running it. The script only produces text; nothing touches msdb until you execute the output yourself.
  • Regenerate rather than hand-edit: change a parameter at the top of the script and re-run it. The IF EXISTS ... sp_delete_job pattern means the old job is cleanly replaced, not duplicated.
  • Prefer the PowerShell subsystem over CmdExec for any multi-step or multi-line command, CmdExec passes commands straight to CreateProcess with no shell in between.
  • On Availability Groups, set @FullBackupPreference deliberately, this script backs up whatever instance it runs on, not necessarily the preferred replica.
  • Confirm job success with Get Maintenance Job Status after first deploying this framework, and periodically afterward.

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

Why does the cleanup job use PowerShell instead of a simpler CmdExec command?

Because CmdExec runs the command text as a direct CreateProcess call with no shell in between, it can’t interpret redirection (2>nul), command chaining (&), or more than the first line of a multi-line command. PowerShell runs the same text as a real script, so Get-ChildItem | Where-Object | Remove-Item pipelines just work.

Does this replace Ola Hallengren’s maintenance solution?

No. If you’re already running it, keep running it. This generator exists for DBAs who want to understand and own every line of what their backup jobs do, at the cost of the polish and edge-case handling a mature, widely-used solution already provides.

Summary

DBA - Backup - FULL, DBA - Backup - LOG, and DBA - Backup - Cleanup come out of one generator script you can read end to end before running. The one real bug found testing this against a live instance, a CmdExec step assuming shell features it doesn’t have, is fixed and verified against real job history. Regenerate, review, and run, then confirm ongoing success with Get Maintenance Job Status.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *