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.

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]…
-DatabaseNamestringRequired—Name of the database in the backup.
-NewDatabaseNamestringOptional—Rename database after restore.
-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. Skipped if DB is in AG.
-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.
-RejoinAvailabilityGroupswitchSwitch$falseRe-add to AG after restore (Automatic Seeding). Requires Automatic Seeding on the AG.

* 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? (skipped if AG database) YES Full Backup DatabaseName_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) -RejoinAvailabilityGroup? only if DB was in AG YES Set Automatic Seeding on secondaries Add-DbaAgDatabase → sync starts NO DONE (MULTI_USER restored, Policy re-enabled)

Examples

Simple restore — local instance, full backup
Invoke-sqmRestoreDatabase -BackupFile "D:\Backup\AdventureWorks.bak" -DatabaseName "AdventureWorks"
AlwaysOn: restore and rejoin AG automatically
Invoke-sqmRestoreDatabase `
    -SqlInstance "SQL01" `
    -BackupFile "D:\Backup\MyDB.bak" `
    -DatabaseName "MyDB" `
    -BackupBeforeRestore `
    -RejoinAvailabilityGroup
Full + Diff + Log restore sequence (point-in-time)
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" `
    -NewDatabaseName "MyDB_Test" `
    -NewDatabaseFilePath "E:\SQLData" `
    -NewLogFilePath "F:\SQLLog"
NORECOVERY — keep DB in restoring state for more logs
Invoke-sqmRestoreDatabase -SqlInstance "SQL01" -BackupFile "D:\Backup\MyDB.bak" -DatabaseName "MyDB" -WithNoRecovery
⬆ Index
All Commands