Test-sqmBackupStatus
BackupsqmSQLTool v1.9.172+ · New🔓 read on msdb.dbo.backupset, VIEW SERVER STATE for the AG role
-MaxFullAgeHours, default 26), the newest full alone (-MaxFullOnlyAgeDays, default 8) and the newest log backup for FULL and BULK_LOGGED databases (-MaxLogAgeMinutes, default 60).Exclusions: databases marked
IsActive = 0 in master.dbo.sqm_BackupExclude are reported as Excluded with reason, who and since when, not as an alarm. It is the same condition the backup jobs of New-sqmOlaUsrDbBackupJob and New-sqmBackupMaintenanceJob use with -UseExcludeTable, so the check and the job agree. The table itself is listed by Get-sqmBackupExcludeReport.New databases: a database created after the last backup run (the newest regular full of any user database) is flagged and listed in the report header. While it is younger than
-MaxFullAgeHours it is a Warning, the next run should pick it up; after that it is Critical.Only backups taken after the database's
create_date count, so a database that was recreated or restored under the same name does not inherit the history of its predecessor. COPY_ONLY fulls do not count. AG databases on a secondary are checked against the primary's msdb, because the backup jobs only back them up there. All ages use the server clock.Statuses: OK, Warning, Critical, Excluded, Skipped (offline, restoring, AG primary not reachable). The same check runs as section Backups in Invoke-sqmHealthReport.
Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string[] | Optional | $env:COMPUTERNAME | Target instance(s), pipeline-capable. |
| -SqlCredential | PSCredential | Optional | , | Also used for the connection to an AG primary. |
| -MaxFullAgeHours | int | Optional | 26 | Maximum age of the newest full or diff. |
| -MaxFullOnlyAgeDays | int | Optional | 8 | Maximum age of the newest full alone. |
| -MaxLogAgeMinutes | int | Optional | 60 | Maximum log backup age. 0 = not checked. |
| -ExcludeDatabase | string[] | Optional | , | Databases not to check (wildcards). Does not shift the last backup run. |
| -IgnoreExcludeTable | switch | Switch | $false | Evaluate databases excluded in sqm_BackupExclude like all others. |
| -IncludeSystemDatabases | switch | Switch | $false | Also check master, model, msdb. |
| -OutputPath | string | Optional | <config>\BackupStatus | 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. |
Execution Flow
Return Value
PSCustomObject per instance: SqlInstance, Status (Critical, Warning or OK), LastBackupRun, ExcludeTablePresent, OkCount, WarningCount, CriticalCount, ExcludedCount, SkippedCount, NewSinceLastBackup (database names), DetailRows (Database, Status, RecoveryModel, CreateDate, NewSinceLastBackup, LastFullBackup, LastDiffBackup, LastLogBackup, LastCopyOnlyFull, FullAgeHours, LogAgeMinutes, ExcludedFromBackup, ExcludeReason, AvailabilityGroup, ReplicaRole, HistorySource, Notes), TxtFile, CsvFile, HtmlFile.
Examples
Daily full, log every 15 minutes (defaults)
Test-sqmBackupStatus -SqlInstance "SQL01"
Weekly full, daily diff, log every 15 minutes
Test-sqmBackupStatus -SqlInstance "SQL01" -MaxFullOnlyAgeDays 8 -MaxFullAgeHours 26 -MaxLogAgeMinutes 30
Only the problem cases
(Test-sqmBackupStatus -SqlInstance "SQL01" -NoOpen).DetailRows | Where-Object Status -in 'Warning','Critical'
Which databases were created after the last backup run?
(Test-sqmBackupStatus -SqlInstance "SQL01" -NoOpen).NewSinceLastBackup