Performance

Register-sqmBackupExcludeTrigger

Creates a server-level DDL trigger (trg_sqm_BackupExclude_AutoSync) that keeps master.dbo.sqm_BackupExclude current automatically on CREATE_DATABASE and DROP_DATABASE events — eliminating the need for manual Sync-sqmBackupExcludeTable runs after database changes.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes (Medium)
Output: PSCustomObject[]

Execution Flow

START dbatools available? throw error Check: trigger exists on instance? -Remove switch? Yes → -Remove path DROP TRIGGER ON ALL SERVER No → CREATE path sqm_BackupExclude table exists? Error: run Sync first DDL trigger body (CREATE TRIGGER ON ALL SERVER) CREATE_DATABASE → INSERT/reactive; DROP_DATABASE → IsOrphaned=1 Action = Created (or AlreadyExists / WhatIfSkipped) -SkipAlwaysOnPropagation? Yes (skip) foreach AG secondary replica Recursive self-call: Register-sqmBackupExcludeTrigger -SkipAlwaysOnPropagation Return PSCustomObject[] (one per instance) DONE
Requires master.dbo.sqm_BackupExclude to already exist (run Sync-sqmBackupExcludeTable first). Trigger errors never abort CREATE DATABASE or DROP DATABASE — failures are written to the SQL Server error log only. To update the trigger, use -Remove then call again without it.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-IncludeSystemDatabasesSwitchOptionalAlso auto-tracks master, model, msdb (not recommended for standard installs).
-RemoveSwitchOptionalDrops the trigger instead of creating it (propagated to secondaries).
-SkipAlwaysOnPropagationSwitchOptionalSkips recursive registration on AG secondary replicas.
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning an error status object.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.

Return Value

Returns an array of PSCustomObject (one per instance processed), each with: SqlInstance, TriggerName, Action (Created / Removed / AlreadyExists / NotFound / WhatIfSkipped / Error / Secondary_*), Message.

Examples

Example 1 — Register trigger on local instance

Register-sqmBackupExcludeTrigger -SqlInstance "SQL01"

Example 2 — Remove and re-register (update trigger)

Register-sqmBackupExcludeTrigger -SqlInstance "SQL01" -Remove
Register-sqmBackupExcludeTrigger -SqlInstance "SQL01"