Test-sqmTdpBackup
Backup
sqmSQLTool v1.9.217+ · Test
✓ -WhatIf supported
Backup age comes from msdb (virtual device backups,
device_type = 7) and is therefore independent of the tracking tables the TDP script writes itself. The limits are derived from the schedule in BackupPlan.json (longest gap between two full days, or between two backup days, plus -MarginHours); without a readable plan the defaults are 8 days and 26 hours.
Without SQL access the function does not abort: it falls back to the file-only check of
Test-sqmTdpConfiguration on the instance's host and adds a Critical finding "SQL Server not reachable". SqlReachable in the result is then $false. -NoFileFallback throws the connection error instead.
Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string[] | Optional | $env:COMPUTERNAME | SQL Server instance(s). Pipeline-capable. |
| -SqlCredential | PSCredential | Optional | , | SQL authentication. |
| -TdpDir | string | Optional | C:\Program Files\Tivoli\TSM\TDPSql | TDP directory on the instance's host. |
| -JobName | string | Optional | TDP_Backup_Daily | Name of the SQL Agent job. |
| -ConfigPath | string | Optional | <TdpDir>\BackupPlan.json | Path of BackupPlan.json. |
| -MaxFullAgeDays | int | Optional | 0 (derive) | Maximum age of the newest TDP full. 0 = from the schedule, fallback 8 days. |
| -MaxAnyAgeHours | int | Optional | 0 (derive) | Maximum age of the newest TDP full or diff. 0 = from the schedule, fallback 26 hours. |
| -MarginHours | int | Optional | 12 | Tolerance added to the derived limits (the backup finishes hours after the job start). |
| -HistoryDays | int | Optional | 35 | Job history and msdb window. |
| -LogFilesToScan | int | Optional | 5 | Number of newest logs scanned. |
| -TestTsmServer | switch | Switch | $false | TCP test of the TSM server from dsm.opt (local computer only). |
| -ExcludeDatabase | string[] | Optional | , | Databases not to check. Wildcards allowed. |
| -NoFileFallback | switch | Switch | $false | Throw when SQL is not reachable instead of falling back to the file-only check. |
| -OutputPath | string | Optional | <OutputPath config>\TdpBackup | Report directory. |
| -NoOpen | switch | Switch | $false | Do not open the HTML report. |
| -NoReport | switch | Switch | $false | Write no files, only return the result object. |
| -ContinueOnError | switch | Switch | $false | Continue with the next instance or computer on error. |
| -EnableException | switch | Switch | $false | Throw exceptions immediately. |
Execution Flow
What it checks
| Area | Checks |
|---|---|
| Agent | Agent service, job exists and is enabled, enabled schedule, CmdExec step, proxy enabled, last run ok, run overdue (daily schedule), run hanging over 12 h, failed runs of the last 7 days |
| Tracking | master tables and procedures exist; BackupLog written by every job run |
| Plan | TDP_BackupPlan filled; every plan database has a BackupStatus row; plan databases that no longer exist; user databases outside the plan; BackupPlan.json against the table |
| Backups | Age of the newest TDP full and diff from msdb; consecutive failures with the last error; SUCCESS logged without a backup in msdb; non-TDP full newer than the TDP full; differential without full; FULL recovery without log backup |
| Files | See Test-sqmTdpConfiguration: BackupPlan.json, tdpsqlc.exe, dsm.opt, tdpsql.cfg, TdpBackup.config, wrapper |
| Logs | Wrapper logs (FAILED, unfinished), per-database TDP logs and tdpsql.log: ANS/ACN/ACO messages, E = Warning, S = Critical |
Return value
One object per instance with Status (OK, Warning, Critical, Error), SqlReachable, the counts, MaxFullAgeHours, MaxAnyAgeHours, LimitSource, Findings (Severity, Category, Database, Message, Hint), DatabaseRows and the report file paths.
| Severity | Meaning |
|---|---|
Critical | The backup is not working or the data is not protected. Changes the overall status to Critical. |
Warning | Needs attention but the backup still runs. Overall status Warning. |
Info | Good to know (for example a step runs without a proxy). Does not change the overall status. |
TDP backups are recognised by device_type 7. Another VDI-based backup product on the same instance would be counted as TDP.
Examples
Check one instance, report opens in the browser
Test-sqmTdpBackup -SqlInstance "SQL01"
Only what needs attention, no files
(Test-sqmTdpBackup -SqlInstance "SQL01" -NoReport).Findings |
Where-Object Severity -ne 'Info' | Format-List Severity, Category, Database, Message, HintNon-default TDP directory and TSM server test
Test-sqmTdpBackup -SqlInstance "SQL01" -TdpDir 'D:\Tivoli\TDPSql' -TestTsmServer
Fixed limits instead of the schedule
Test-sqmTdpBackup -SqlInstance "SQL01" -MaxFullAgeDays 8 -MaxAnyAgeHours 30
Throw instead of falling back when SQL is down
Test-sqmTdpBackup -SqlInstance "SQL01" -NoFileFallback