Commands / New-sqmRestoreDatabaseJob
New-sqmRestoreDatabaseJob
Backup & Restore sqmSQLTool v1.9.x · New ✓ -WhatIf supported
Generates a wrapper script in the module's jobs folder and creates a SQL Agent job that runs Invoke-sqmRestoreDatabase with the restore parameters you specify. Unlike New-sqmRestoreTestJob, this job is created without a schedule — a productive restore is an on-demand operation, not recurring — meant to be started manually (Start-DbaAgentJob) or right away with -StartJob.
Before you create the job:
  1. 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.
  2. Use an elevated PowerShell. Without elevation the write to Program Files fails with “Access to the path … is denied”.
  3. sqmSQLTool and dbatools must be installed on the target instance, the job imports the module.
  4. The SQL Agent service account needs sysadmin on the target instance and, for an AlwaysOn database, on all replicas — the same requirement Invoke-sqmRestoreDatabase itself documents. The job runs under its Windows identity.
No password in the wrapper. The generated script never embeds a SQL credential. When the job runs, powershell.exe executes as the SQL Agent service account and Invoke-sqmRestoreDatabase connects via that account's Windows identity — the same auth model as New-sqmAlwaysOnRepairJob and New-sqmRestoreTestJob.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMEInstance where the job is created and runs. Also baked in as Invoke-sqmRestoreDatabase's -SqlInstance.
-DatabaseNamestringRequired, Database to restore, as it appears in the backup file.
-BackupFilestring[]Required*, Full backup path, or an array for a striped backup. Must be readable from the target instance. Mutually exclusive with -BackupFiles.
-BackupFilesstring[]Required*, Full + Diff + Logs sequence, in order. Alternative to -BackupFile.
-NewDatabaseNamestringOptional, Restore under a new name (forwarded).
-NewDatabaseFilePathstringOptional, Target directory for the data files (forwarded).
-NewLogFilePathstringOptional, Target directory for the log file (forwarded).
-BackupBeforeRestoreswitchSwitch$falseBack up the existing database first (forwarded).
-NoUserExportswitchSwitch$falseSkip the database-user export (forwarded).
-KeepAlwaysOnswitchSwitch$falseForwarded — see Invoke-sqmRestoreDatabase.
-AvailabilityGroupNamestringOptional, Forwarded — forces AG-aware handling / selects the AG explicitly.
-WithNoRecoveryswitchSwitch$falseForwarded — leaves the database in RESTORING state.
-ContinueWithNoRecoveryswitchSwitch$falseForwarded — the last restore step also uses NORECOVERY.
-ForceSingleUserswitchSwitch$falseForwarded — force single-user mode before the restore.
-NoRejoinAvailabilityGroupswitchSwitch$falseForwarded — do AG detection/cleanup but skip the actual (re)join.
-JobNamestringOptionalsqmRestore_<DB>Name of the Agent job.
-StepNamestringOptionalRunRestoreName of the single job step.
-ForceswitchSwitch$falseOverwrite an existing job of the same name.
-StartJobswitchSwitch$falseStart the job immediately after creation.
-EnableExceptionswitchSwitch$falseThrow instead of returning a failure result object.

Execution Flow

START dbatools installed? NO throw: Install dbatools YES -JobName given? NO → default: sqmRestore_<DatabaseName> -BackupFile vs. -BackupFiles: which parameter set was used? determines whether the wrapper calls with -BackupFile or -BackupFiles Ensure C:\System\WinSrvLog\MSSQL exists icacls grant: NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT Ensure module jobs\ folder exists (creates it if missing) Build the Invoke-sqmRestoreDatabase argument line (single-quoted, escaped) -SqlInstance, -DatabaseName, -BackupFile(s) always included Forward NewDatabaseName/FilePath/LogFilePath, AvailabilityGroupName if set Forward every switch that was passed: BackupBeforeRestore, NoUserExport, KeepAlwaysOn, WithNoRecovery, ContinueWithNoRecovery, ForceSingleUser, NoRejoinAvailabilityGroup Always appended: -Confirm:$false -EnableException ShouldProcess → write wrapper script (UTF-8 BOM) ...\jobs\Restore-Job_<DatabaseName>.ps1 Import-Module sqmSQLTool -Force; Invoke-sqmRestoreDatabase <args>; exit 0 Job of this name already exists? NO YES -Force set? NO throw job exists, no -Force YES Remove-DbaAgentJob (ShouldProcess) ShouldProcess → New-DbaAgentJob + New-DbaAgentJobStep CmdExec: powershell.exe -NoProfile -ExecutionPolicy Bypass -File <wrapper> OnFailAction = QuitWithFailure No schedule is created — on-demand only, by design -StartJob set? YES Start-DbaAgentJob restore runs right away NO Build [PSCustomObject] result JobName, StepName, WrapperPath, Started, Status Any error above → Status=Failed, or throw with -EnableException DONE — result object returned

Output object

FieldContents
JobName / StepNameName of the created Agent job and its step.
DatabaseNameThe database the job restores.
WrapperPathPath of the generated wrapper script.
StartedWas the job started right away (-StartJob)?
Status / MessageSuccess or Failed, with detail.

Examples

Create the job, leave it for the DBA to start later
New-sqmRestoreDatabaseJob -SqlInstance "SQL01" -BackupFile "D:\Backup\AdventureWorks.bak" -DatabaseName "AdventureWorks"
Full + Diff + Logs sequence, create and run right away
$seq = @("D:\Backup\App_Full.bak", "D:\Backup\App_Diff.bak", "D:\Backup\App_Log1.trn")
New-sqmRestoreDatabaseJob -SqlInstance "SQL01" -BackupFiles $seq -DatabaseName "App" -StartJob
Recreate an existing job of the same name
New-sqmRestoreDatabaseJob -SqlInstance "SQL01" -BackupFile "D:\Backup\App.bak" -DatabaseName "App" -Force

See also

Invoke-sqmRestoreDatabase, the restore itself, with the full AG-aware safety model.
New-sqmRestoreTestJob, the recurring, scheduled counterpart for audit evidence.

⬆ Index
All Commands