DBA Scripts: Generate Backup Jobs

Peter Whyte

–

,
🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Maintenance & Automation › Maintenance Job Framework

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';
-- ─────────────────────────────────────────────────────────────────────────────

IF RIGHT(@BackupRootPath, 1) = N'\'
    SET @BackupRootPath = LEFT(@BackupRootPath, LEN(@BackupRootPath) - 1);

DECLARE @q              nchar(1)      = NCHAR(39);
DECLARE @crlf           nvarchar(2)   = CHAR(13) + CHAR(10);
DECLARE @ddl            nvarchar(max) = N'';
DECLARE @fullScheduleTS int           = @FullScheduleHour * 10000;
DECLARE @logRetentionDays int         = CEILING(CAST(@LogRetentionHours AS float) / 24.0);

-- ── Step command: FULL backup ─────────────────────────────────────────────────
-- Cursor over all online, writeable, non-snapshot user databases.
-- Uses NCHAR(39) inside the step for path quoting — avoids nested string escaping.
DECLARE @fullCmd nvarchar(max) = REPLACE(
N'SET NOCOUNT ON;
DECLARE @db sysname, @path nvarchar(500), @sql nvarchar(max), @q nchar(1);
SET @q = NCHAR(39);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE database_id > 4 AND state_desc = N|ONLINE|
      AND is_read_only = 0 AND source_database_id IS NULL
    ORDER BY name;
OPEN c;
FETCH NEXT FROM c INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @path = N|<<ROOT>>\| + @db + N|_FULL_|
        + REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(20), GETDATE(), 120),
          N|-|, N||), N| |, N|_|), N|:|, N||) + N|.bak|;
    SET @sql = N|BACKUP DATABASE [| + @db + N|] TO DISK = |
        + @q + @path + @q + N| WITH COMPRESSION, CHECKSUM, INIT, STATS = 10;|;
    EXEC sp_executesql @sql;
    FETCH NEXT FROM c INTO @db;
END
CLOSE c;
DEALLOCATE c;'
, N'|', NCHAR(39));
SET @fullCmd = REPLACE(@fullCmd, N'<<ROOT>>', @BackupRootPath);

-- ── Step command: LOG backup ──────────────────────────────────────────────────
DECLARE @logCmd nvarchar(max) = REPLACE(
N'SET NOCOUNT ON;
DECLARE @db sysname, @path nvarchar(500), @sql nvarchar(max), @q nchar(1);
SET @q = NCHAR(39);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE database_id > 4 AND state_desc = N|ONLINE|
      AND is_read_only = 0 AND source_database_id IS NULL
      AND recovery_model_desc IN (N|FULL|, N|BULK_LOGGED|)
    ORDER BY name;
OPEN c;
FETCH NEXT FROM c INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @path = N|<<ROOT>>\| + @db + N|_LOG_|
        + REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(20), GETDATE(), 120),
          N|-|, N||), N| |, N|_|), N|:|, N||) + N|.trn|;
    SET @sql = N|BACKUP LOG [| + @db + N|] TO DISK = |
        + @q + @path + @q + N| WITH COMPRESSION, CHECKSUM, NOINIT, STATS = 10;|;
    EXEC sp_executesql @sql;
    FETCH NEXT FROM c INTO @db;
END
CLOSE c;
DEALLOCATE c;'
, N'|', NCHAR(39));
SET @logCmd = REPLACE(@logCmd, N'<<ROOT>>', @BackupRootPath);

-- ── Step command: Cleanup (PowerShell subsystem) ──────────────────────────────
-- Runs as a real PowerShell script (not CmdExec), so multi-line commands and
-- pipes execute exactly as written — no shell-quoting/chaining pitfalls to
-- work around. (CmdExec + forfiles.exe was tried first: SQL Agent's CmdExec
-- subsystem does not wrap the command in a shell, so forfiles' own multi-line
-- form only ran its first line, and neither redirection (2>nul) nor chaining
-- (&) were interpreted — both were passed through as literal, invalid
-- forfiles arguments. PowerShell avoids the whole class of problem.)
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';

-- ═══════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════
SET @ddl =
    N'-- =================================================================' + @crlf +
    N'-- Generated by Generate-BackupJobs.sql' + @crlf +
    N'-- Server       : ' + @@SERVERNAME + @crlf +
    N'-- Backup root  : ' + @BackupRootPath + @crlf +
    N'-- Full backup  : daily at ' + CAST(@FullScheduleHour AS nvarchar(2)) + N':00, kept '
        + CAST(@FullRetentionDays AS nvarchar(5)) + N' days' + @crlf +
    N'-- Log backup   : every ' + CAST(@LogIntervalMins AS nvarchar(5)) + N' min, kept '
        + CAST(@LogRetentionHours AS nvarchar(5)) + N' hours' + @crlf +
    N'-- Generated    : ' + CONVERT(nvarchar(20), GETDATE(), 120) + @crlf +
    N'-- =================================================================' + @crlf +
    @crlf +
    N'USE msdb;' + @crlf +
    N'GO' + @crlf;

-- Job category
SET @ddl +=
    @crlf +
    N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories' + @crlf +
    N'               WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
    N'    EXEC msdb.dbo.sp_add_category' + @crlf +
    N'        @class = N' + @q + N'JOB' + @q + N',' + @crlf +
    N'        @type  = N' + @q + N'LOCAL' + @q + N',' + @crlf +
    N'        @name  = N' + @q + @CategoryName + @q + N';' + @crlf +
    N'GO' + @crlf;

-- ── Job 1: DBA - Backup - FULL ────────────────────────────────────────────────
SET @ddl +=
    @crlf +
    N'-- ==================================================================' + @crlf +
    N'-- Job: DBA - Backup - FULL' + @crlf +
    N'-- ==================================================================' + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + N'DBA - Backup - FULL' + @q + N')' + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job' + @crlf +
    N'        @job_name              = N' + @q + N'DBA - Backup - FULL' + @q + N',' + @crlf +
    N'        @delete_unused_schedule = 1;' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_job' + @crlf +
    N'    @job_name          = N' + @q + N'DBA - Backup - FULL' + @q + N',' + @crlf +
    N'    @enabled           = 1,' + @crlf +
    N'    @owner_login_name  = N' + @q + @JobOwner + @q + N',' + @crlf +
    N'    @category_name     = N' + @q + @CategoryName + @q + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_jobstep' + @crlf +
    N'    @job_name          = N' + @q + N'DBA - Backup - FULL' + @q + N',' + @crlf +
    N'    @step_id           = 1,' + @crlf +
    N'    @step_name         = N' + @q + N'Back up all user databases' + @q + N',' + @crlf +
    N'    @subsystem         = N' + @q + N'TSQL' + @q + N',' + @crlf +
    N'    @database_name     = N' + @q + N'master' + @q + N',' + @crlf +
    N'    @command           = N' + @q + REPLACE(@fullCmd, @q, @q + @q) + @q + N',' + @crlf +
    N'    @retry_attempts    = 1,' + @crlf +
    N'    @retry_interval    = 5,' + @crlf +
    N'    @on_success_action = 1,' + @crlf +
    N'    @on_fail_action    = 2;' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_schedule' + @crlf +
    N'    @schedule_name        = N' + @q + N'DBA - Full Backup Daily '
        + CAST(@FullScheduleHour AS nvarchar(2)) + N':00' + @q + N',' + @crlf +
    N'    @freq_type            = 4,' + @crlf +
    N'    @freq_interval        = 1,' + @crlf +
    N'    @freq_subday_type     = 1,' + @crlf +
    N'    @freq_subday_interval = 0,' + @crlf +
    N'    @active_start_time    = ' + CAST(@fullScheduleTS AS nvarchar(10)) + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
    N'    @job_name      = N' + @q + N'DBA - Backup - FULL' + @q + N',' + @crlf +
    N'    @schedule_name = N' + @q + N'DBA - Full Backup Daily '
        + CAST(@FullScheduleHour AS nvarchar(2)) + N':00' + @q + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + N'DBA - Backup - FULL' + @q + N';' + @crlf +
    N'GO' + @crlf;

-- ── Job 2: DBA - Backup - LOG ─────────────────────────────────────────────────
SET @ddl +=
    @crlf +
    N'-- ==================================================================' + @crlf +
    N'-- Job: DBA - Backup - LOG  (every ' + CAST(@LogIntervalMins AS nvarchar(5)) + N' minutes)' + @crlf +
    N'-- ==================================================================' + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + N'DBA - Backup - LOG' + @q + N')' + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job' + @crlf +
    N'        @job_name              = N' + @q + N'DBA - Backup - LOG' + @q + N',' + @crlf +
    N'        @delete_unused_schedule = 1;' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_job' + @crlf +
    N'    @job_name          = N' + @q + N'DBA - Backup - LOG' + @q + N',' + @crlf +
    N'    @enabled           = 1,' + @crlf +
    N'    @owner_login_name  = N' + @q + @JobOwner + @q + N',' + @crlf +
    N'    @category_name     = N' + @q + @CategoryName + @q + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_jobstep' + @crlf +
    N'    @job_name          = N' + @q + N'DBA - Backup - LOG' + @q + N',' + @crlf +
    N'    @step_id           = 1,' + @crlf +
    N'    @step_name         = N' + @q + N'Back up transaction logs (FULL and BULK_LOGGED only)' + @q + N',' + @crlf +
    N'    @subsystem         = N' + @q + N'TSQL' + @q + N',' + @crlf +
    N'    @database_name     = N' + @q + N'master' + @q + N',' + @crlf +
    N'    @command           = N' + @q + REPLACE(@logCmd, @q, @q + @q) + @q + N',' + @crlf +
    N'    @retry_attempts    = 1,' + @crlf +
    N'    @retry_interval    = 2,' + @crlf +
    N'    @on_success_action = 1,' + @crlf +
    N'    @on_fail_action    = 2;' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_schedule' + @crlf +
    N'    @schedule_name        = N' + @q + N'DBA - Log Backup Every '
        + CAST(@LogIntervalMins AS nvarchar(5)) + N' Min' + @q + N',' + @crlf +
    N'    @freq_type            = 4,' + @crlf +
    N'    @freq_interval        = 1,' + @crlf +
    N'    @freq_subday_type     = 4,' + @crlf +
    N'    @freq_subday_interval = ' + CAST(@LogIntervalMins AS nvarchar(5)) + N',' + @crlf +
    N'    @active_start_time    = 0;' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
    N'    @job_name      = N' + @q + N'DBA - Backup - LOG' + @q + N',' + @crlf +
    N'    @schedule_name = N' + @q + N'DBA - Log Backup Every '
        + CAST(@LogIntervalMins AS nvarchar(5)) + N' Min' + @q + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + N'DBA - Backup - LOG' + @q + N';' + @crlf +
    N'GO' + @crlf;

-- ── Job 3: DBA - Backup - Cleanup ─────────────────────────────────────────────
SET @ddl +=
    @crlf +
    N'-- ==================================================================' + @crlf +
    N'-- Job: DBA - Backup - Cleanup' + @crlf +
    N'-- Requires: SQL Agent service account has delete access to backup folder.' + @crlf +
    N'-- ==================================================================' + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + N'DBA - Backup - Cleanup' + @q + N')' + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job' + @crlf +
    N'        @job_name              = N' + @q + N'DBA - Backup - Cleanup' + @q + N',' + @crlf +
    N'        @delete_unused_schedule = 1;' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_job' + @crlf +
    N'    @job_name          = N' + @q + N'DBA - Backup - Cleanup' + @q + N',' + @crlf +
    N'    @enabled           = 1,' + @crlf +
    N'    @owner_login_name  = N' + @q + @JobOwner + @q + N',' + @crlf +
    N'    @category_name     = N' + @q + @CategoryName + @q + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_jobstep' + @crlf +
    N'    @job_name          = N' + @q + N'DBA - Backup - Cleanup' + @q + N',' + @crlf +
    N'    @step_id           = 1,' + @crlf +
    N'    @step_name         = N' + @q + N'Delete old backup files' + @q + N',' + @crlf +
    N'    @subsystem         = N' + @q + N'PowerShell' + @q + N',' + @crlf +
    N'    @command           = N' + @q + REPLACE(@cleanCmd, @q, @q + @q) + @q + N',' + @crlf +
    N'    @on_success_action = 1,' + @crlf +
    N'    @on_fail_action    = 2;' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_schedule' + @crlf +
    N'    @schedule_name        = N' + @q + N'DBA - Backup Cleanup Daily '
        + CAST((@FullScheduleHour + 1) % 24 AS nvarchar(2)) + N':00' + @q + N',' + @crlf +
    N'    @freq_type            = 4,' + @crlf +
    N'    @freq_interval        = 1,' + @crlf +
    N'    @freq_subday_type     = 1,' + @crlf +
    N'    @freq_subday_interval = 0,' + @crlf +
    N'    @active_start_time    = ' + CAST(((@FullScheduleHour + 1) % 24) * 10000 AS nvarchar(10)) + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
    N'    @job_name      = N' + @q + N'DBA - Backup - Cleanup' + @q + N',' + @crlf +
    N'    @schedule_name = N' + @q + N'DBA - Backup Cleanup Daily '
        + CAST((@FullScheduleHour + 1) % 24 AS nvarchar(2)) + N':00' + @q + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + N'DBA - Backup - Cleanup' + @q + N';' + @crlf +
    N'GO' + @crlf;

SELECT @ddl AS ddl;

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:

Why CmdExec Steps Get No Shell, and What That Breaks

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.

Start Here

The Pillars

Blog Categories

Latest Posts

Blog Tags

Access (4) Always On Availability Groups (4) Auditing (7) Backups (6) Blocking (4) Blocking Locking (6) Collectors (7) Database Backups & Recovery (8) Database Backups and Recovery (5) Database Maintenance (7) DBA Tools (24) Disk Space (5) Error Log (3) Extended Events (8) Features (6) High Availability (6) Indexes (7) Installation (7) Inventory (8) Maintenance (6) Migration (14) Monitoring (27) Performance (21) Performance Troubleshooting (9) PowerShell (4) Query Behaviour (5) Security (10) SQL Agent (5) SQL Scripts (17) SQL Server Administration (10) SQL Server Configuration (7) SQL Server Errors (5) SQL Server High Availability (4) SQL Server Internals (3) SQL Server Management Studio (SSMS) (6) SQL Server Monitoring (7) SQL Server Networking (9) SQL Server Security (7) Storage and Capacity (7) T-SQL (10) Temp DB (3) Tracing (7) Transaction Logs (4) Troubleshooting (24) Windows Server (4)

Comments

Leave a Reply

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