Get-sqmBackupExcludeReport
BackupsqmSQLTool v1.9.173+ · New🔓 SELECT on sqm_BackupExclude(_History), read on msdb.dbo.backupset
master.dbo.sqm_BackupExclude and its change log sqm_BackupExclude_History and joins them with sys.databases and the msdb backup history. Read-only; editing is done in Show-sqmBackupExcludeForm.Statuses: Excluded (
IsActive = 0, skipped by the backup jobs with -UseExcludeTable), Active (backed up), Orphaned (database no longer exists, the row is kept), NotInTable (database exists but has no row yet, so it is backed up; the next sync or the DDL trigger adds it).Excluded by / since comes from the change log: the last change of
IsActive to 0. The columns ExcludedBy/ExcludedAt of the main table only record when the row was inserted, usually by the sync job or the DDL trigger, not when someone excluded the database. Without change log the report says so instead of showing a wrong date.Hints per excluded database: no reason entered; FULL or BULK_LOGGED recovery (nobody backs up the log, so it is never truncated and grows until the disk is full); a regular full backup after the exclusion (someone backs it up anyway).
Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string[] | Optional | $env:COMPUTERNAME | Target instance(s), pipeline-capable. |
| -SqlCredential | PSCredential | Optional | , | SQL or Windows credential. |
| -Status | string | Optional | All | All, Excluded, Active, Orphaned, NotInTable. The counters always cover all rows. |
| -IncludeHistory | switch | Switch | $false | Add the change log (excluded, backed up again, newly registered, orphaned). |
| -HistoryDays | int | Optional | 90 | Days of change log for -IncludeHistory. |
| -OutputPath | string | Optional | <config>\BackupExclude | Directory for TXT, CSV and HTML. |
| -NoOpen | switch | Switch | $false | Do not open the report after creation. |
| -ContinueOnError | switch | Switch | $false | Continue with the next instance on error. |
| -EnableException | switch | Switch | $false | Throw exceptions immediately. |
Return Value
PSCustomObject per instance: SqlInstance, Status (OK, NoTable or Error), TablePresent, HistoryPresent, DdlTriggerPresent, ExcludedCount, ActiveCount, OrphanedCount, NotInTableCount, DetailRows (Database, Status, Reason, ExcludedBy, ExcludedAt, RowAddedBy, RowAddedAt, DatabaseExists, State, RecoveryModel, SizeMB, AvailabilityGroup, LastFullBackup, LastLogBackup, Hints), History (ChangedAt, Database, Change, ChangedBy), TxtFile, CsvFile, HtmlFile.
Examples
The whole table
Get-sqmBackupExcludeReport -SqlInstance "SQL01"
Only the excluded databases, with change log of the last 180 days
Get-sqmBackupExcludeReport -SqlInstance "SQL01" -Status Excluded -IncludeHistory -HistoryDays 180
Excluded databases whose log keeps growing
(Get-sqmBackupExcludeReport -SqlInstance "SQL01" -Status Excluded -NoOpen).DetailRows | Where-Object Hints -match 'Recovery FULL'
Compare all AG replicas
Get-sqmBackupExcludeReport -SqlInstance "SQLNODE1", "SQLNODE2" -Status Excluded -NoOpen | Select-Object SqlInstance, ExcludedCount