Performance

Invoke-sqmUserDatabaseBackup

Full backup of all or selected user databases on a SQL Server instance. AG-aware via preferred replica check, supports an exclusion table (sqm_BackupExclude), and sends a Database Mail report on completion. Target path must end with \User-Db.

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

Execution Flow

START dbatools available? No throw error Yes Resolve BackupPath (Get-sqmServerSetting BackupDirectory) Path ends \User-Db? No → throw path error OK -CheckPreferredReplica? fn_hadr_backup_is_preferred Not preferred Status=AbortedNotPreferredReplica Get-DbaDatabase (ExcludeSystem, filter by -Database) -UseExcludeTable? Yes Read sqm_BackupExclude (IsActive=1, IsOrphaned=0) → filter DBs foreach database ShouldProcess? WhatIf Skip Backup-DbaDatabase -Type Full → \User-Db\{db}_{ts}.bak Status=Failed -MailTo set? Yes sp_send_dbmail (on error / -MailOnSuccess) DONE
The backup path must end with \User-Db — this is enforced to prevent accidental writes to incorrect directories. If -CheckPreferredReplica is set and the instance is not the preferred AG backup node, the entire job aborts immediately with Status=AbortedNotPreferredReplica and no databases are backed up.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL Server instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credentials.
-DatabaseString[]OptionalSpecific database names. Ignored when -All is set.
-AllSwitchOptionalBack up all accessible user databases.
-BackupPathStringOptionalOverride backup directory. Must end with \User-Db. Defaults to server's BackupDirectory property.
-UseExcludeTableSwitchOptionalRead master.dbo.sqm_BackupExclude and skip databases where IsActive=1 and IsOrphaned=0.
-CheckPreferredReplicaSwitchOptionalAbort if this instance is not the preferred AG backup replica (fn_hadr_backup_is_preferred_replica).
-MailToStringOptionalEmail address for the backup report via Database Mail. Sent on failure by default.
-MailProfileStringOptionalDatabase Mail profile name. Default: Default.
-MailOnSuccessSwitchOptionalAlso send mail on full success (not only on errors).
-EnableExceptionSwitchOptionalThrow exceptions immediately.

Return Value

Returns PSCustomObject[] — one entry per database: SqlInstance, DatabaseName, BackupFile, Status (Success / Failed / WhatIfSkipped / AbortedNotPreferredReplica / NoDatabasesFound), Message.

Examples

Example 1 — All user databases, local instance

Invoke-sqmUserDatabaseBackup -All

Example 2 — Specific databases on remote server

Invoke-sqmUserDatabaseBackup -SqlInstance "SQL01" -Database "SalesDB", "InventoryDB"

Example 3 — AG-aware backup with exclude table and mail report

Invoke-sqmUserDatabaseBackup -SqlInstance "SQL01" -All -UseExcludeTable `
    -CheckPreferredReplica -MailTo "dba@example.com" -MailOnSuccess

Example 4 — Custom path

Invoke-sqmUserDatabaseBackup -All -BackupPath "D:\Backup\User-Db"