Performance

Test-sqmBackupIntegrity

Verifies backup file integrity by running Restore-DbaDatabase -VerifyOnly on each backup file found in the configured backup path — reads headers, checksums, and confirms restorability without touching the actual database.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: No
Output: PSCustomObject[]

Execution Flow

START -BackupPath provided? read from module config Path exists and accessible? throw error -FileListOnly? return file list only (no verify) Get-ChildItem -Recurse → collect .bak / .trn / .diff files foreach backup file Read-DbaBackupHeader → read backup metadata Restore-DbaDatabase -VerifyOnly → checksum + page verify Verify succeeded? Status = Failed + message Collect result (file, header, status) Return PSCustomObject[] (one per backup file) DONE
-VerifyOnly does not restore any data — SQL Server reads and validates the backup stream and checksums without writing to any database. If no -BackupPath is provided, the path is read from the sqmSQLTool module configuration (Get-sqmConfiguration -Key BackupPath). Use -FileListOnly to get a quick inventory of all backup files without running the checksum verification.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalSQL Server instance used to run the verify. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-BackupPathString[]OptionalOne or more UNC/local paths containing backup files. Falls back to module config if omitted.
-FileListOnlySwitchOptionalReturn backup file inventory without running -VerifyOnly.
-EnableExceptionSwitchOptionalThrow terminating errors instead of logging warnings.

Return Value

Returns a PSCustomObject[] — one entry per backup file — with: SqlInstance, BackupFile, DatabaseName, BackupType, BackupStart, BackupFinish, Status, Message.

Examples

Example 1 — Verify all backups in configured path

Test-sqmBackupIntegrity

Example 2 — Verify specific backup path

Test-sqmBackupIntegrity -SqlInstance "SQL01" -BackupPath "\\NAS01\Backups\SQL01"

Example 3 — List backup files only (no verify)

Test-sqmBackupIntegrity -BackupPath "\\NAS01\Backups" -FileListOnly | Format-Table -AutoSize

Example 4 — Show only failed verifications

Test-sqmBackupIntegrity | Where-Object Status -eq 'Failed'