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. Then brings the database to standard via Invoke-sqmDatabaseStandardization: orphaned users re-mapped, users without a login removed, compatibility level raised to the server level, TARGET_RECOVERY_TIME 60 seconds, owner sa (by SID 0x01). Optionally re-joins the AG after the restore.

Give neither -BackupFile nor -BackupFiles, just -DatabaseName, and the most recent backup chain (latest full plus any diff and log backups since) is looked up in the instance's backup history and restored to the latest possible point.

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 "SalesDB_copy" alone, or -DatabaseName "SalesDB" -NewDatabaseName "SalesDB_copy". Either way, the original "SalesDB" 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

Parameter	Type	Required	Default	Notes
-SqlInstance	string	Optional	$env:COMPUTERNAME	Target instance. Default: local machine.
-SqlCredential	PSCredential	Optional	,	SQL login credentials.
-BackupFile	string[]	Optional*	,	Single .bak file or striped backup. Use instead of -BackupFiles.
-BackupFiles	string[]	Optional*	,	Ordered sequence: Full, [Diff], [Log1], [Log2]…
-DatabaseName	string	Optional	read from backup header	Name 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. Required when no backup file is given: then it is the key for the backup-history lookup.
-NewDatabaseName	string	Optional / deprecated	,	Restores as a new/different database (a copy). Same result as just putting the copy's name straight in `-DatabaseName`, see callout below.
-NewDatabaseFilePath	string	Optional	Instance default data path	Target directory for .mdf / .ndf files.
-NewLogFilePath	string	Optional	Instance default log path	Target directory for .ldf file.
-BackupBeforeRestore	switch	Switch	$false	Full backup of existing DB before restore. Runs the same whether the DB is in an AG or not.
-NoUserExport	switch	Switch	$false	Skip user export/import. Users are exported by default.
-KeepAlwaysOn	switch	Switch	$false	Do NOT remove from AG. Only valid if DB is already outside AG, otherwise aborts.
-WithNoRecovery	switch	Switch	$false	All restores with NORECOVERY. DB stays in restoring state.
-ContinueWithNoRecovery	switch	Switch	$false	Last restore in sequence also with NORECOVERY.
-ForceSingleUser	switch	Switch	$false	Force SINGLE_USER even if no active connections detected.
-NoRejoinAvailabilityGroup	switch	Switch	$false	By 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.
-AvailabilityGroupName	string	Optional	,	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.
-KeepCompatibilityLevel	switch	Switch	$false	Leave the compatibility level as it came out of the backup instead of raising it to the server level. Use it when the application is not yet certified for the new level, since a new level can change execution plans. (v1.9.149+)
-EnableException	switch	Switch	$false	Switch to allow exceptions to pass through (by default errors are logged and returned as objects).

* -BackupFile and -BackupFiles are mutually exclusive. Give neither and -DatabaseName alone restores the latest backup chain from the instance's backup history (msdb.dbo.backupset). Before v1.9.149 this call form failed at parameter binding with "missing mandatory parameter BackupFile".

Execution Flow

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, "SalesDB" stays untouched, "SalesDB_copy" is created new. Shortest form: just put the copy's name in -DatabaseName.

Invoke-sqmRestoreDatabase -SqlInstance "SQL01" -BackupFile "D:\Backup\SalesDB.bak" -DatabaseName "SalesDB_copy"

Equivalent, spelled out with the original name for clarity (same result, older/deprecated form)

Invoke-sqmRestoreDatabase `
    -SqlInstance "SQL01" `
    -BackupFile "D:\Backup\SalesDB.bak" `
    -DatabaseName "SalesDB" `
    -NewDatabaseName "SalesDB_copy"

No file path at all: restore the latest backup chain from the instance's backup history

Invoke-sqmRestoreDatabase -SqlInstance "SQL01" -DatabaseName "AdventureWorks"

Keep the compatibility level from the backup (application not yet certified for the new level)

Invoke-sqmRestoreDatabase -SqlInstance "SQL01" -BackupFile "D:\Backup\MyDB.bak" -KeepCompatibilityLevel

NORECOVERY, keep DB in restoring state for more logs

Invoke-sqmRestoreDatabase -SqlInstance "SQL01" -BackupFile "D:\Backup\MyDB.bak" -DatabaseName "MyDB" -WithNoRecovery

⬆ Index

All Commands

🧹 Runs afterwards

Invoke-sqmDatabaseStandardization