Restore-DbaDbSnapshot

Synopsis

Restores SQL Server databases from database snapshots, reverting to the snapshot’s point-in-time state

Description

Restores SQL Server databases to their exact state when a database snapshot was created, discarding all changes made since that point. This is particularly useful for quickly reverting development databases after testing, rolling back problematic changes, or returning to a known good state without restoring from backup files.

The function uses SQL Server’s RESTORE DATABASE FROM DATABASE_SNAPSHOT command and automatically handles SQL Server’s requirement that all other snapshots of the same database be dropped before restoration. It also fixes a SQL Server bug where log file growth settings get reset to their defaults during snapshot restoration.

When Force is specified, the command will terminate active connections to both the target database and snapshot to ensure the restore operation completes successfully.

Syntax

Restore-DbaDbSnapshot
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [[-Snapshot] <Object[]>]
    [[-InputObject] <Database[]>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Restore-DbaDbSnapshot -SqlInstance sql2014 -Database HR, Accounting

Restores HR and Accounting databases using the latest snapshot available

Example: 2

PS C:\> Restore-DbaDbSnapshot -SqlInstance sql2014 -Database HR -Force

Restores HR database from latest snapshot and kills any active connections in the database on sql2014.

Example: 3

PS C:\> Get-DbaDbSnapshot -SqlInstance sql2016 -Database HR | Restore-DbaDbSnapshot -Force

Restores HR database from latest snapshot and kills any active connections in the database on sql2016.

Example: 4

PS C:\> Get-DbaDbSnapshot -SqlInstance sql2016 | Out-GridView -PassThru | Restore-DbaDbSnapshot

Allows the selection of snapshots on sql2016 to restore

Example: 5

PS C:\> Restore-DbaDbSnapshot -SqlInstance sql2014 -Snapshot HR_snap_20161201, Accounting_snap_20161101

Restores databases from snapshots named HR_snap_20161201 and Accounting_snap_20161101

Optional Parameters

-SqlInstance

The target SQL Server instance or instances

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies which databases to restore from their most recent snapshots. Accepts multiple database names and wildcards for pattern matching.
Use this when you want to restore specific databases to their snapshot state rather than working with snapshot names directly.

-ExcludeDatabase

Excludes specific databases from being restored when using wildcard patterns or restoring multiple databases.
Helpful when you want to restore most databases from snapshots but skip certain critical production databases.

-Snapshot

Specifies the exact snapshot names to restore from, giving you precise control over which snapshot is used for each database.
Use this when you need to restore from specific snapshots rather than automatically using the most recent ones.

-InputObject

Accepts snapshot objects from other dbatools commands like Get-DbaDbSnapshot through the PowerShell pipeline.
This enables you to filter and select specific snapshots before restoring, such as using Out-GridView for interactive selection.

-Force

Automatically drops other snapshots of the same database that would prevent the restore operation, as required by SQL Server.
Also terminates active connections to both the target database and snapshot to ensure the restore completes successfully.
Required when multiple snapshots exist for the database being restored or when active sessions could block the operation.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run

-Confirm

Prompts for confirmation of every step.