Commands / Invoke-sqmRestoreDatabase
Invoke-sqmRestoreDatabase
Backup & Restore sqmSQLTool v1.8.2+ · Invoke ✓ -WhatIf supported
Restores a database from a backup file. Automatically detects AlwaysOn AG membership and removes the database from the AG before the restore (including deletion on all secondary replicas). Exports database users before the restore and re-imports them afterwards. Repairs orphaned users, removes missing Windows logins, and sets the database owner to the sa account (by SID 0x01). Optionally re-joins the AG after the restore.
AlwaysOn: You can run this on any replica, if the database is in an AG, the function automatically detects the primary and routes all AG operations (remove, rejoin) through it. The only hard stop: if the database is in an AG and -KeepAlwaysOn is set, the function aborts, because a restore requires removing the database from the AG first.
Want a copy under a new name? Two ways to get there, same result: -DatabaseName "arena_copy" alone, or -DatabaseName "arena" -NewDatabaseName "arena_copy". Either way, the original "arena" is never touched, there is no "restore over -DatabaseName, then rename" step; the backup is written directly to the target name in one operation. -NewDatabaseName is kept only for backward compatibility / for spelling out "this is a deliberate rename from X" in a script, it does not unlock any behavior -DatabaseName alone can't already do. If given, every pre-restore safety step (AG-membership check, existence check, -BackupBeforeRestore, user export, single-user handling) targets whichever name is the actual restore target, -NewDatabaseName if set, otherwise -DatabaseName. -DatabaseName itself is optional, the name is already inside the backup, so if you omit it, it's read straight from the backup header (RESTORE HEADERONLY) instead of making you type it again.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMETarget instance. Default: local machine.
-SqlCredentialPSCredentialOptional, SQL login credentials.
-BackupFilestring[]Required*, Single .bak file or striped backup. Use instead of -BackupFiles.
-BackupFilesstring[]Required*, Ordered sequence: Full, [Diff], [Log1], [Log2]…
-DatabaseNamestringOptionalread from backup headerName of the database as it appears in the backup. Omit it to auto-detect via RESTORE HEADERONLY. Also the restore target if -NewDatabaseName is omitted.
-NewDatabaseNamestringOptional / deprecated, Restores as a new/different database (a copy). Same result as just putting the copy's name straight in -DatabaseName, see callout below.
-NewDatabaseFilePathstringOptionalInstance default data pathTarget directory for .mdf / .ndf files.
-NewLogFilePathstringOptionalInstance default log pathTarget directory for .ldf file.
-BackupBeforeRestoreswitchSwitch$falseFull backup of existing DB before restore. Runs the same whether the DB is in an AG or not.
-NoUserExportswitchSwitch$falseSkip user export/import. Users are exported by default.
-KeepAlwaysOnswitchSwitch$falseDo NOT remove from AG. Only valid if DB is already outside AG, otherwise aborts.
-WithNoRecoveryswitchSwitch$falseAll restores with NORECOVERY. DB stays in restoring state.
-ContinueWithNoRecoveryswitchSwitch$falseLast restore in sequence also with NORECOVERY.
-ForceSingleUserswitchSwitch$falseForce SINGLE_USER even if no active connections detected.
-NoRejoinAvailabilityGroupswitchSwitch$falseBy default, an AG-managed database is automatically re-added to the AG after restore (Automatic Seeding), no switch needed. Set this to opt out and leave it standalone instead.
-AvailabilityGroupNamestringOptional, Optional: Explicitly declares which AG the database belongs to (or should end up in after the restore), instead of relying solely on live AG-membership/instance detection at the start of the run. Use this when the database was already removed from the AG by a previous, incompletely finished run (so it is no longer auto-detected as an AG member), when restoring a brand-new database straight into an existing AG, or when the instance has more than one AG (auto-detection only works when there is exactly one). When set, the restore is always treated as AG-aware: secondaries are cleaned up and the database is rejoined (with seeding) at the end, exactly as if live detection had found it - regardless of whether the database is currently an AG member. Policy note: even without this parameter, a database that is not currently in any AG will still be added to the instance's AG automatically if the instance has exactly one - restoring a database is not allowed to silently leave it standalone on an AG-capable instance. Use -KeepAlwaysOn to opt out of that auto-join deliberately.
-EnableExceptionswitchSwitch$falseSwitch to allow exceptions to pass through (by default errors are logged and returned as objects).

* Either -BackupFile or -BackupFiles is required (mutually exclusive parameter sets).

Execution Flow

START dbatools installed? NO throw: Install dbatools YES -SqlInstance provided? NO → Default: $env:COMPUTERNAME DB in AlwaysOn AG? YES -KeepAlwaysOn set? YES ABORT Cannot restore AG DB NO Identify Primary replica Remove DB from AG via Primary Delete DB on each secondary replica NO Active connections or -ForceSingleUser? YES SET SINGLE_USER WITH ROLLBACK IMMEDIATE NO -BackupBeforeRestore? (same for AG and non-AG) YES Full Backup TargetName_preRestore_*.bak NO Export-DbaUser → %TEMP% skipped if -NoUserExport RESTORE FILELISTONLY → build FileMapping auto-maps logical names to target paths Restore-DbaDatabase Full → [Diff] → [Log1 … LogN]  |  last step: RECOVERY (or NORECOVERY if -WithNoRecovery) Import users (from export file) Repair-DbaDbOrphanUser  |  Remove Windows logins missing from domain Set DB owner = sa (by SID 0x01, regardless of sa rename) -NoRejoinAvailabilityGroup set? only relevant if DB was in AG NO Set Automatic Seeding on secondaries Add-DbaAgDatabase → sync starts YES DONE (MULTI_USER restored, Policy re-enabled)

Examples

Simple restore, local instance, full backup
Invoke-sqmRestoreDatabase -BackupFile "D:\Backup\AdventureWorks.bak" -DatabaseName "AdventureWorks"
-DatabaseName omitted, read straight from the backup's own header, restored under that same name
Invoke-sqmRestoreDatabase -BackupFile "D:\Backup\AdventureWorks.bak"
AlwaysOn: restore and rejoin AG automatically (no extra switch needed, rejoin is the default)
Invoke-sqmRestoreDatabase `
    -SqlInstance "SQL01" `
    -BackupFile "D:\Backup\MyDB.bak" `
    -DatabaseName "MyDB" `
    -BackupBeforeRestore
Full + Diff + Log restore sequence (point-in-time), restored as a differently-named copy on custom drives
Invoke-sqmRestoreDatabase `
    -SqlInstance "SQL01" `
    -BackupFiles @(
        "D:\Backup\MyDB_Full.bak",
        "D:\Backup\MyDB_Diff.bak",
        "D:\Backup\MyDB_Log1.trn",
        "D:\Backup\MyDB_Log2.trn"
    ) `
    -DatabaseName "MyDB_Test" `
    -NewDatabaseFilePath "E:\SQLData" `
    -NewLogFilePath "F:\SQLLog"
Restore as a copy alongside the original, "arena" stays untouched, "arena_copy" is created new. Shortest form: just put the copy's name in -DatabaseName.
Invoke-sqmRestoreDatabase -SqlInstance "SQL01" -BackupFile "D:\Backup\arena.bak" -DatabaseName "arena_copy"
Equivalent, spelled out with the original name for clarity (same result, older/deprecated form)
Invoke-sqmRestoreDatabase `
    -SqlInstance "SQL01" `
    -BackupFile "D:\Backup\arena.bak" `
    -DatabaseName "arena" `
    -NewDatabaseName "arena_copy"
NORECOVERY, keep DB in restoring state for more logs
Invoke-sqmRestoreDatabase -SqlInstance "SQL01" -BackupFile "D:\Backup\MyDB.bak" -DatabaseName "MyDB" -WithNoRecovery
⬆ Index
All Commands