· Uwe Janke
Commands / Invoke-sqmUserDatabaseBackup
Performance

Invoke-sqmUserDatabaseBackup

Full backup of all or selected user databases on a SQL Server instance. AG-aware (AG databases are backed up on the primary only), 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 AG database on a secondary? Yes → skip DB SkippedAgSecondary 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. Availability Group databases are backed up only and always on the current primary: on a secondary they are skipped with Status=SkippedAgSecondary, regardless of the AG backup preference. Databases outside an AG are not affected (since 1.9.166.0).

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.
-CheckPreferredReplicaSwitchOptionalObsolete since 1.9.166.0, no effect (logs a warning). AG databases are always backed up on the primary only.
-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 / SkippedAgSecondary / 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, Backup with exclude table and mail report

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

Example 4, Custom path

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