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

Parameter	Type	Required	Default	Notes
-SqlInstance	string	Optional	$env:COMPUTERNAME	Target instance. Default: local machine.
-SqlCredential	PSCredential	Optional	—	SQL login credentials.
-DatabaseName	string	Required	—	Name of the source database as it appears in the backup.
-BackupFile	string[]	Optional	auto-detected	Single .bak, striped set, or a full chain. Omit it and the newest full backup is determined automatically — the normal case with Ola Hallengren.
-IncludeChain	switch	Switch	$false	Restore the whole chain (last full + diff + subsequent logs) instead of just the newest full. Requires the msdb history.
-BackupRootPath	string	Optional	Config / instance backup dir	Root for the directory-scan fallback.
-TestDatabaseName	string	Optional	RestoreTest_<DB>_<timestamp>	Name of the test copy. Must start with `RestoreTest_`.
-DataFilePath	string	Optional	Instance default data path	Target directory for .mdf / .ndf files.
-LogFilePath	string	Optional	Instance default log path	Target directory for the .ldf file.
-RemoveTestDatabase	switch	Switch	$false	Drop the test copy after the measurement. By default it is kept, so you can test against it.
-AllowReplaceExistingTestDatabase	switch	Switch	$false	Overwrite an existing test copy. Only ever applies to `RestoreTest_` names.
-RetentionMonths	int	Optional	12 (module config)	How long evidence is kept. `0` = keep forever.
-OutputPath	string	Optional	<OutputPath>\RestoreTest	Directory for the TXT/HTML evidence.
-OutputHtml	switch	Switch	$true	Also write the HTML evidence.
-NoOpen	switch	Switch	$false	Do not open the report afterwards. Always set inside an Agent job.
-EnableException	switch	Switch	$false	Throw instead of returning a failure result object.

Safety rules

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

Rule	Behaviour
Name prefix	The 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 source	The target name must differ from the source database name.
Target exists	If the target database already exists, the run aborts without any change — unless `-AllowReplaceExistingTestDatabase` is given explicitly.
No REPLACE	If 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 renamed	Physical files are renamed to the test name, so the restore can never write over the source database's data files.
Guarded drop	The 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:

Source	When it applies
1. msdb history	The normal case. Reports the path SQL Server actually wrote — independent of how Ola's `@DirectoryStructure` and `@FileName` are configured.
2. Directory scan	Fallback 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

Field	Contents
`Success` / `Status`	`$true`/`$false`; `Success`, `Failed`, `Rejected`, `Aborted`, `Skipped`
`BackupSize`	Logical data volume, formatted (MB/GB/TB). Raw in `BackupSizeBytes`.
`CompressedBackupSize`	Physically read volume. Equal to `BackupSize` means uncompressed.
`Duration` / `DurationSeconds`	Measured duration, formatted / exact in seconds.
`Throughput`	Data throughput, e.g. `"73,56 MB/s"`. Raw in `ThroughputBytesPerSecond`.
`SqlReportedRestoreTime`	Duration as reported by SQL Server (whole seconds only, for comparison).
`BackupSource`	Where the backup came from: `BackupHistory`, `DirectoryScan` or `Parameter`.
`TestDatabaseRemoved`	Was the test copy cleaned up?
`RemovedReports`	Number of old evidence files removed by the retention rule.
`TxtReport` / `HtmlReport`	Paths 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

Parameters

Safety rules

How the backup is determined

Measurement

Evidence

Output object

Examples

See also