Performance

New-sqmOlaUsrDbBackupJob

Creates separate SQL Agent jobs per backup type (FULL/DIFF/LOG) using Ola Hallengren's DatabaseBackup, with auto-detection of FITS-default schedules from existing jobs and live exclude-list support via sqm_BackupExclude. Includes Always On propagation to secondary replicas.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes
Output: PSCustomObject[]

Execution Flow

START dbatools available? throw error None of -Full/-Diff/-Log set? throw error At least one set -UseExcludeTable? No → FITS defaults Read existing FULL/LOG job schedule as template, else apply FITS defaults (auto-disable DIFF job) foreach selected backup type (Full / Diff / Log) New-DbaAgentJob + DatabaseBackup step → ...\Usr-db -UseExcludeTable? No Dynamic SQL step reads sqm_BackupExclude live sp_add_schedule + sp_attach_schedule Add result to output array (per backup type) -SkipAlwaysOnPropagation? Yes (skip) Recursive self-call per AG secondary replica Return PSCustomObject[] (one per job type) DONE
FITS defaults: -Full Sunday 20:00, -Diff Mon–Sat 20:00, -Log every day, every 15 minutes. When -UseExcludeTable is combined with auto-detection, an existing FULL/LOG job's schedule (if present) is reused, otherwise the default 21:15 daily FULL / 15-min LOG cadence is applied and the DIFF job is auto-disabled. Secondary-replica propagation reuses this same function recursively, tagging results Secondary_<status>.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-Full / -Diff / -LogSwitchAt least one requiredWhich backup type job(s) to create.
-BackupDirectoryStringOptionalBackup root path; target is <path>\Usr-db.
-UseExcludeTableSwitchOptionalReads sqm_BackupExclude at job runtime via dynamic SQL, and triggers schedule auto-detection from existing jobs.
-JobCategoryStringOptionalDefault: Database Maintenance.
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning an error status object.
-SkipAlwaysOnPropagationSwitchOptionalSkips recursive job creation on AG secondary replicas.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.

Return Value

Returns an array of PSCustomObject, one per created backup type, each with: SqlInstance, BackupType, JobName, BackupDirectory, Databases, ScheduleTime, ScheduleDays, JobStatus, OverallStatus, Message.

Examples

Example 1 — Create FULL + LOG jobs with exclude table

New-sqmOlaUsrDbBackupJob -SqlInstance "SQL01" -Full -Log -UseExcludeTable

Example 2 — All three types, explicit backup path

New-sqmOlaUsrDbBackupJob -SqlInstance "SQL01" -Full -Diff -Log -BackupDirectory "E:\Backup"