Commands / Invoke-sqmRestoreTest
Invoke-sqmRestoreTest
Backup & Restore sqmSQLTool v1.9.23+ · Invoke ✓ -WhatIf supported
Performs an auditable restore test: restores a backup into a new test database, never over the original, and measures the restore. Writes the evidence as TXT and HTML, documenting whether the restore succeeded, how much data was restored, how long it took, and which throughput was achieved. Intended for recurring restore-test obligations, where success, data volume, data throughput and duration must be proven.
Not a replacement for Invoke-sqmRestoreDatabase: that one is the productive, AlwaysOn-aware restore, it deliberately overwrites an existing database and re-joins it to the availability group. This function is the opposite: it creates a throwaway copy, never overwrites unasked, and does not touch AlwaysOn at all. A test copy must never be joined to an availability group.
Why RESTORE VERIFYONLY is not enough: Test-sqmBackupIntegrity only checks readability and checksums of the backup file. It proves nothing about an actual restore, and reports no data volume, throughput or duration.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMETarget instance. Default: local machine.
-SqlCredentialPSCredentialOptional, SQL login credentials.
-DatabaseNamestringOptional*read from backup headerName of the source database as it appears in the backup. Required only when -BackupFile is omitted, there it's the lookup key into the msdb backup history. Given -BackupFile, an omitted name is read straight from the backup itself (RESTORE HEADERONLY), same as Invoke-sqmRestoreDatabase.
-BackupFilestring[]Optionalauto-detectedSingle .bak, striped set, or a full chain. Omit it and the newest full backup is determined automatically, the normal case with Ola Hallengren.
-IncludeChainswitchSwitch$falseRestore the whole chain (last full + diff + subsequent logs) instead of just the newest full. Requires the msdb history.
-BackupRootPathstringOptionalConfig / instance backup dirRoot for the directory-scan fallback.
-TestDatabaseNamestringOptionalRestoreTest_<DB>_<timestamp>Name of the test copy. Must start with RestoreTest_.
-DataFilePathstringOptionalInstance default data pathTarget directory for .mdf / .ndf files.
-LogFilePathstringOptionalInstance default log pathTarget directory for the .ldf file.
-RemoveTestDatabaseswitchSwitch$falseDrop the test copy after the measurement. By default it is kept, so you can test against it.
-AllowReplaceExistingTestDatabaseswitchSwitch$falseOverwrite an existing test copy. Only ever applies to RestoreTest_ names.
-RetentionMonthsintOptional12 (module config)How long evidence is kept. 0 = keep forever.
-OutputPathstringOptional<OutputPath>\RestoreTestDirectory for the TXT/HTML evidence.
-OutputHtmlswitchSwitch$trueAlso write the HTML evidence.
-NoOpenswitchSwitch$falseDo not open the report afterwards. Always set inside an Agent job.
-EnableExceptionswitchSwitch$falseThrow instead of returning a failure result object.

Execution Flow

START dbatools installed? NO throw: Install dbatools YES -DatabaseName given? YES NO -BackupFile given? NO Rejected -DatabaseName required (no -BackupFile to read it from) YES RESTORE HEADERONLY read DatabaseName from the file itself on failure → Rejected / throw -TestDatabaseName given? NO → default: RestoreTest_<DatabaseName>_<yyyyMMdd_HHmm> Guard 1: starts with 'RestoreTest_'? NO Rejected wrong prefix YES Guard 2: target = source name? YES Rejected target = source NO Connect-DbaInstance Guard 3: target DB already exists? NO (WithReplace = $false) YES -AllowReplace ExistingTestDatabase? NO Aborted existing test DB, no change YES WithReplace = $true -BackupFile given? YES use as given NO Get-sqmRestoreTestBackupSource 1) msdb backup history (Last / LastFull) 2) directory scan fallback (full backup only) neither finds anything → throw Restore-DbaDatabase ReplaceDbNameInFile=true  |  WithReplace per Guard 3  |  wall-clock timed Collect metrics BackupSize/CompressedBackupSize from the result, else RESTORE HEADERONLY restoreComplete = every restored part succeeded -RemoveTestDatabase AND restoreComplete? YES Remove-DbaDatabase prefix re-checked before drop NO Build result object Success/Status, BackupSize, Duration, Throughput, BackupSource ... Write TXT + HTML evidence → -OutputPath Apply retention: delete own reports older than -RetentionMonths Open report, unless -NoOpen Any error above (outside the Guards) → Status=Failed, or throw with -EnableException DONE — result object returned
The -DatabaseName resolution at the top runs before the target-name guards, so a name read from the backup header is validated exactly like one typed explicitly, it can still be rejected as a bad prefix or as identical to the target, same as normal.

Safety rules

A restore test must never destroy existing data. Several independent guards enforce that, if one fails, the next still holds.

RuleBehaviour
Name prefixThe target database must start with RestoreTest_. Anything else is rejected before a connection is even opened. The prefix is a code constant, not a config key, a settable guard is no guard.
Not the sourceThe target name must differ from the source database name.
Target existsIf the target database already exists, the run aborts without any change, unless -AllowReplaceExistingTestDatabase is given explicitly.
No REPLACEIf the target does not exist, the restore runs without WITH REPLACE, so SQL Server itself refuses to overwrite should the name unexpectedly be taken.
Files renamedPhysical files are renamed to the test name, so the restore can never write over the source database's data files.
Guarded dropThe optional cleanup re-checks the prefix independently. A DROP can never hit a non-test database.

How the backup is determined

With Ola Hallengren every backup file carries a timestamp in its name. A fixed path would therefore be stale after the next backup run, and with Ola's @CleanupTime the file may already be gone. Omit -BackupFile and the backup is resolved on every run, from two sources:

SourceWhen it applies
1. msdb historyThe normal case. Reports the path SQL Server actually wrote, independent of how Ola's @DirectoryStructure and @FileName are configured.
2. Directory scanFallback when the history yields nothing: Ola's own sp_delete_backuphistory job purged it, or the test runs on a different instance than the backup. Ordered by the timestamp in the file name.
Which backup was used and how it was found is recorded in the evidence (BackupSource: BackupHistory, DirectoryScan or Parameter), an auditor must be able to see what the measurement refers to.
-IncludeChain only works via msdb. Which backups belong together by LSN cannot be read off file names. Without the history the function logs a warning and restores the full backup only, rather than silently pretending a chain was tested.

Measurement

Duration is measured, not taken from SQL Server. dbatools' DatabaseRestoreTime has whole-second resolution only. In testing, SQL Server reported 1 second for a restore that actually took 2.42 s, which would have documented 228 MB/s instead of the true 94 MB/s. Duration and throughput are therefore measured as wall-clock time around the restore; the SQL-reported value is still carried as SqlReportedRestoreTime for reference.

Data volume is reported as BackupSize (the logical size), formatted automatically as MB/GB/TB. For compressed backups CompressedBackupSize, the physically read bytes, is reported alongside, since the two differ substantially and the distinction matters when the throughput figure is questioned. Compression is derived, not assumed: if both are equal the evidence says unkomprimiert.

Evidence

TXT and HTML are written to <OutputPath>\RestoreTest (default C:\System\WinSrvLog\MSSQL\RestoreTest) and kept for 12 months by default (module config key RestoreTestRetentionMonths). The cleanup only ever touches files matching this function's own naming pattern, name starts with RestoreTest_ and extension is .txt or .html, so pointing -OutputPath at a shared directory cannot delete unrelated files.

Ergebnis            : ERFOLGREICH
Quelldatenbank      : AdventureWorks
Testdatenbank       : RestoreTest_AdventureWorks_20260715_1142

Datenmenge (Backup) : 213,08 MB
davon physisch      : 213,08 MB (unkomprimiert)
Dauer               : 3s (2,90 s)
Datendurchsatz      : 73,56 MB/s

Aufgeraeumt         : Nein - Test-Datenbank bleibt erhalten
Aufbewahrung        : 12 Monate

Ermittelt ueber     : msdb-Sicherungshistorie (1x Full)
Backupquelle        : ...\Backup\AdventureWorks\FULL\dev01_AdventureWorks_FULL_20260715_081217.bak

Output object

FieldContents
Success / Status$true/$false; Success, Failed, Rejected, Aborted, Skipped
BackupSizeLogical data volume, formatted (MB/GB/TB). Raw in BackupSizeBytes.
CompressedBackupSizePhysically read volume. Equal to BackupSize means uncompressed.
Duration / DurationSecondsMeasured duration, formatted / exact in seconds.
ThroughputData throughput, e.g. "73,56 MB/s". Raw in ThroughputBytesPerSecond.
SqlReportedRestoreTimeDuration as reported by SQL Server (whole seconds only, for comparison).
BackupSourceWhere the backup came from: BackupHistory, DirectoryScan or Parameter.
TestDatabaseRemovedWas the test copy cleaned up?
RemovedReportsNumber of old evidence files removed by the retention rule.
TxtReport / HtmlReportPaths to the generated evidence files.

Examples

Ola environment, backup is determined automatically (the normal case)
Invoke-sqmRestoreTest -SqlInstance "SQL01" -DatabaseName "Kunde"
Restore test with cleanup, evidence to a share, no auto-open
Invoke-sqmRestoreTest `
    -SqlInstance "SQL01" `
    -DatabaseName "Kunde" `
    -RemoveTestDatabase `
    -OutputPath "\\srv\Nachweise" `
    -NoOpen
Whole chain (full + diff + subsequent logs), files onto a test volume
Invoke-sqmRestoreTest `
    -SqlInstance "SQL01" `
    -DatabaseName "Kunde" `
    -IncludeChain `
    -DataFilePath "T:\RestoreTest" `
    -LogFilePath "T:\RestoreTest"
A specific backup file, no lookup
Invoke-sqmRestoreTest -SqlInstance "SQL01" -DatabaseName "Kunde" -BackupFile "D:\Backup\Kunde_Full.bak"
Repeat into the same test database (allowed, name carries the prefix)
Invoke-sqmRestoreTest `
    -SqlInstance "SQL01" `
    -DatabaseName "Kunde" `
    -TestDatabaseName "RestoreTest_Kunde_Woche27" `
    -AllowReplaceExistingTestDatabase

See also

New-sqmRestoreTestJob, schedules this test as a SQL Agent job for the recurring evidence.
Invoke-sqmRestoreDatabase, the productive, AlwaysOn-aware restore.
Test-sqmBackupIntegrity, RESTORE VERIFYONLY check (no actual restore).

⬆ Index
All Commands