Performance

New-sqmBackupMaintenanceJob

Creates a single SQL Agent job with two PowerShell-subsystem steps: synchronizing the backup exclude table, then running Invoke-sqmUserDatabaseBackup for all user databases. Supports automatic Always On propagation to secondary replicas.

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

Execution Flow

START dbatools available? throw error Resolve ScheduleDays default (per BackupType) via ConvertTo-WeekdayInterval New-DbaAgentJob (sqm-BackupMaintenance-<Type>) Step 1 (PowerShell): Sync-BackupExcludeTable Step 2 (PowerShell): Invoke-sqmUserDatabaseBackup -All -BackupType <Type> sp_add_schedule + sp_attach_schedule -SkipAlwaysOnPropagation? Yes (skip) No, AG present foreach AG secondary replica (if instance is in an AG) Recursive self-call: New-sqmBackupMaintenanceJob -SqlInstance <secondary> Return PSCustomObject (Status=Success) DONE
Default ScheduleDays depends on -BackupType: FULL → Sunday, DIFF → Mon–Sat, LOG → every day. The Always On propagation step recursively calls itself against each secondary replica unless -SkipAlwaysOnPropagation is set.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-JobNameStringOptionalAgent job name. Default: sqm-BackupMaintenance-FULL.
-BackupTypeStringOptionalFULL / DIFF / LOG. Default: FULL.
-BackupPathStringOptionalTarget backup directory.
-ScheduleTimeStringOptionalDaily start time. Default: 20:00.
-ScheduleDaysString[]OptionalDays the job runs. Default depends on -BackupType.
-ScheduleIntervalMinutesIntOptionalRecurrence interval (0–1440), used mainly for LOG backups.
-JobCategoryStringOptionalAgent job category. Default: Database Maintenance.
-UseExcludeTableSwitchOptionalExcludes databases listed in sqm_BackupExclude at runtime.
-CheckPreferredReplicaSwitchOptionalAborts the backup on a non-preferred AG replica.
-IncludeSystemDatabasesSwitchOptionalIncludes system databases in the backup scope.
-MailToStringOptionalRecipient for Database Mail notifications.
-MailProfileStringOptionalDatabase Mail profile name. Default: Default.
-MailOnSuccessSwitchOptionalSends mail on success as well as failure.
-OperatorNameStringOptionalSQL Agent operator for job notifications.
-UpdateSwitchOptionalUpdates an existing job instead of failing.
-SkipAlwaysOnPropagationSwitchOptionalSkips recursive job creation on AG secondary replicas.
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning an error status object.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.

Return Value

Returns a PSCustomObject with: SqlInstance, JobName, BackupType, Step1Command, Step2Command, ScheduleName, ScheduleDays, ScheduleTime, Status, Message.

Examples

Example 1 — Standard FULL backup job

New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType "FULL" -BackupPath "E:\Backup"

Example 2 — LOG backup job with exclude table and mail

New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType "LOG" -UseExcludeTable -MailTo "dba@firma.de"