Commands / New-sqmOlaUsrDbBackupJob
Performance

New-sqmOlaUsrDbBackupJob

Creates separate SQL Agent jobs per backup type (FULL/DIFF/LOG) using Ola Hallengren's DatabaseBackup, with auto-detection of standard-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 → standard defaults Read existing FULL/LOG job schedule as template, else apply standard 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
Standard 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.
-CheckSumstringOptionalChecksum calculation. Default: 'Y'.
-CleanupTimeintOptionalAge in hours after which backup files are deleted. Default: 48. 0 = no cleanup.
-CompressstringOptionalBackup compression. Default: 'Y'.
-ContinueOnErrorswitchOptionalContinue with remaining jobs if one job fails.
-CreateSyncJobboolOptionalWhen -UseExcludeTable is set, automatically creates a SQL Agent job that runs Sync-sqmBackupExcludeTable every 30 minutes via pwsh CmdExec step. Ensures IsActive changes (made via Show-sqmBackupExcludeForm) are propagated to all AG secondaries without manual intervention. Default: $true. Set to $false to suppress job creation.
-DatabasesstringOptionalDatabase filter for Ola. E.g. 'USER_DATABASES', 'ALL_DATABASES', or comma-separated DB names like 'DB1,DB2'. Default: 'USER_DATABASES'.
-DiffswitchOptionalCreates a DIFF backup job.
-DiffJobNamestringOptionalOverrides the job name for DIFF read from the configuration.
-DiffScheduleDaysstring[]OptionalDays of the week for the DIFF job. Default: @('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday').
-DiffScheduleIntervalMinutesintOptionalRepeat interval for the DIFF job in minutes. 0 = once. Default: 0.
-DiffScheduleTimestringOptionalStart time of the DIFF job in format 'HH:mm'. Default: '20:00'.
-FullJobNamestringOptionalOverrides the job name for FULL read from the configuration.
-FullScheduleDaysstring[]OptionalDays of the week for the FULL job as an array. Valid values: 'Monday'..'Sunday', 'Weekdays', 'Weekend', 'EveryDay'. Multiple days: @('Monday','Wednesday','Friday'). Default: @('Sunday').
-FullScheduleIntervalMinutesintOptionalRepeat interval for the FULL job in minutes (e.g. 60 = hourly). 0 = no interval, job runs once at FullScheduleTime. Default: 0.
-FullScheduleTimestringOptionalStart time of the FULL job in format 'HH:mm'. Default: '20:00'.
-LogswitchOptionalCreates a LOG backup job.
-LogJobNamestringOptionalOverrides the job name for LOG read from the configuration.
-LogScheduleDaysstring[]OptionalDays of the week for the LOG job. Default: @('EveryDay').
-LogScheduleIntervalMinutesintOptionalRepeat interval for the LOG job in minutes (e.g. 15 = every 15 minutes). 0 = once at LogScheduleTime. Default: 0.
-LogScheduleTimestringOptionalStart time of the LOG job in format 'HH:mm'. Default: '00:00'.
-LogToTablestringOptionalOla internal logging to CommandLog table. Default: 'Y'.
-OperatorNamestringOptionalSQL Agent operator for email notification on failure.
-UpdateswitchOptionalReplace existing jobs with the same name.
-VerifystringOptionalBackup verification. Default: 'Y'.

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"
⬆ Index
All Commands