New-sqmRestoreTestJob

Backup & Restore sqmSQLTool v1.9.23+ · New ✓ -WhatIf supported

Generates a wrapper script in the module's jobs folder and creates a SQL Agent job that runs Invoke-sqmRestoreTest — together with a schedule. Unlike New-sqmRestoreDatabaseJob (an on-demand restore, deliberately unscheduled), a restore test is a recurring obligation, so this job is scheduled: monthly on the 1st at 02:00 by default, matching the usual audit cadence.

No backup path is baked in — that is the point. Without -BackupFile the generated wrapper contains no path, so every run resolves the current backup itself. An Ola environment needs exactly this: every backup file carries a timestamp, so a hardcoded path would work exactly once and then fail with “file not found” — or be gone already via Ola's @CleanupTime.

Before you create the job:

Run this on the target instance. The wrapper is written locally to C:\Program Files\WindowsPowerShell\Modules\sqmSQLTool\jobs\ — where the Agent job later finds it.
Use an elevated PowerShell. Without elevation the write to Program Files fails with “Access to the path … is denied”.
sqmSQLTool and dbatools must be installed on the target instance — the job imports the module.
The SQL Agent service account needs rights to restore and drop, plus read access to the backup files. The job runs under its Windows identity.

Parameters

Parameter	Type	Required	Default	Notes
-SqlInstance	string	Optional	$env:COMPUTERNAME	Instance where the job is created and runs.
-SqlCredential	PSCredential	Optional	—	Only for creating job/step/schedule. Deliberately not embedded into the wrapper.
-DatabaseName	string	Required	—	Source database as it appears in the backup.
-BackupFile	string[]	Optional	resolved per run	Omit for Ola. Only give a path if the same, immutable file must always be tested.
-IncludeChain	switch	Switch	$false	Test the whole chain instead of just the newest full backup.
-BackupRootPath	string	Optional	Config / instance backup dir	Root for the directory-scan fallback (forwarded).
-TestDatabaseName	string	Optional	auto, timestamped	Must start with `RestoreTest_`. Omit it for a recurring job — a fixed name would need `-AllowReplaceExistingTestDatabase` on every run after the first.
-DataFilePath	string	Optional	Instance default data path	Target directory for the test copy's data files.
-LogFilePath	string	Optional	Instance default log path	Target directory for the test copy's log file.
-KeepTestDatabase	switch	Switch	$false	Do not drop the test copy. By default a job run cleans up — see below.
-AllowReplaceExistingTestDatabase	switch	Switch	$false	Forwarded. Only needed together with a fixed `-TestDatabaseName`.
-RetentionMonths	int	Optional	12 (module config)	How long evidence is kept. `0` = forever.
-OutputPath	string	Optional	<OutputPath>\RestoreTest	Directory for the evidence.
-ScheduleType	string	Optional	Monthly	`Monthly`, `Weekly` or `Daily`.
-ScheduleTime	string	Optional	02:00	Start time as `"HH:mm"`.
-ScheduleDayOfMonth	int	Optional	1	Day of month for `Monthly` (1–28). Capped at 28 so the schedule also fires in February.
-ScheduleDays	string[]	Optional	Sunday	Weekdays for `Weekly`.
-NoSchedule	switch	Switch	$false	Create the job without a schedule (manual starts only).
-JobName	string	Optional	sqmRestoreTest_<DB>	Name of the Agent job.
-StepName	string	Optional	RunRestoreTest	Name of the job step.
-Force	switch	Switch	$false	Overwrite an existing job of the same name.
-StartJob	switch	Switch	$false	Start the job right after creation — produces the first evidence immediately.
-EnableException	switch	Switch	$false	Throw instead of returning a failure result object.

Behaviour worth knowing

A job run drops the test copy. That differs from the manual call, where it stays. Reason: an unattended monthly job would otherwise leave a full-size copy behind on every run until the volume fills up. -KeepTestDatabase opts out — only sensible with a long interval and enough free space.

No password in the wrapper. -SqlCredential exists so you can create the job from a workstation; it is never written into the generated script. The job always runs under the SQL Agent service account's Windows identity. A password in a script on disk would be the wrong trade.

A failed job goes red. The step runs with -EnableException, so a failing restore test fails the Agent job visibly. That is deliberate: a restore test that silently does nothing is worse than none — no evidence would be produced while everyone assumes the obligation is covered.

The RestoreTest_ prefix rule is validated at job-creation time, so an invalid -TestDatabaseName fails immediately instead of at the first scheduled run months later.

Examples

Monthly evidence (default) — 1st at 02:00, backup resolved per run, copy cleaned up

New-sqmRestoreTestJob -SqlInstance "SQL01" -DatabaseName "Kunde"

Weekly Sunday 23:00, files onto a dedicated test volume, run once right away

New-sqmRestoreTestJob `
    -SqlInstance "SQL01" `
    -DatabaseName "Kunde" `
    -ScheduleType Weekly `
    -ScheduleDays "Sunday" `
    -ScheduleTime "23:00" `
    -DataFilePath "T:\RestoreTest" `
    -LogFilePath "T:\RestoreTest" `
    -StartJob

Whole chain, monthly on the 15th, evidence kept for 3 years

New-sqmRestoreTestJob `
    -SqlInstance "SQL01" `
    -DatabaseName "Kunde" `
    -IncludeChain `
    -ScheduleDayOfMonth 15 `
    -RetentionMonths 36

Job without a schedule (manual starts only)

New-sqmRestoreTestJob -SqlInstance "SQL01" -DatabaseName "Kunde" -NoSchedule

Check the job afterwards

Get-DbaAgentJobHistory -SqlInstance "SQL01" -Job "sqmRestoreTest_Kunde"
Get-ChildItem C:\System\WinSrvLog\MSSQL\RestoreTest

Output object

Field	Contents
`JobName` / `StepName`	Name of the created Agent job and its step.
`ScheduleName` / `Schedule`	Name of the schedule and a readable description, e.g. `"monatlich am 1. um 02:00"`.
`RemovesTestDatabase`	Does a run clean up the test copy?
`EvidencePath`	Directory the evidence is written to.
`WrapperPath`	Path of the generated wrapper script.
`Started`	Was the job started right away (`-StartJob`)?
`Status` / `Message`	`Success` or `Failed`, with detail.

Parameters

Behaviour worth knowing

Examples

Output object

See also