functions/Restore-DbaDbSnapshot.ps1
|
function Restore-DbaDbSnapshot { <# .SYNOPSIS Restores databases from snapshots .DESCRIPTION Restores the database from the snapshot, discarding every modification made to the database NB: Restoring to a snapshot will result in every other snapshot of the same database to be dropped It also fixes some long-standing bugs in SQL Server when restoring from snapshots .PARAMETER SqlInstance The target SQL Server instance or instances .PARAMETER 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. .PARAMETER Database Restores from the last snapshot databases with this names only. You can pass either Databases or Snapshots .PARAMETER ExcludeDatabase The database(s) to exclude - this list is auto-populated from the server .PARAMETER Snapshot Restores databases from snapshots with this names only. You can pass either Databases or Snapshots .PARAMETER InputObject Allows piping from other Snapshot commands .PARAMETER Force If restoring from a snapshot involves dropping any other snapshot, you need to explicitly use -Force to let this command delete the ones not involved in the restore process. Also, -Force will forcibly kill all running queries that prevent the restore process. .PARAMETER WhatIf Shows what would happen if the command were to run .PARAMETER Confirm Prompts for confirmation of every step. .PARAMETER 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. .NOTES Tags: Snapshot, Backup, Restore, Database Author: Simone Bizzotto (@niphold) Website: https://dbatools.io Copyright: (c) 2018 by dbatools, licensed under MIT License: MIT https://opensource.org/licenses/MIT .LINK https://dbatools.io/Restore-DbaDbSnapshot .EXAMPLE PS C:\> Restore-DbaDbSnapshot -SqlInstance sql2014 -Database HR, Accounting Restores HR and Accounting databases using the latest snapshot available .EXAMPLE 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 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 PS C:\> Get-DbaDbSnapshot -SqlInstance sql2016 | Out-GridView -PassThru | Restore-DbaDbSnapshot Allows the selection of snapshots on sql2016 to restore .EXAMPLE 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 #> [CmdletBinding(SupportsShouldProcess, ConfirmImpact = "Medium")] param ( [DbaInstance[]]$SqlInstance, [PSCredential]$SqlCredential, [object[]]$Database, [object[]]$ExcludeDatabase, [object[]]$Snapshot, [Parameter(ValueFromPipeline)] [Microsoft.SqlServer.Management.Smo.Database[]]$InputObject, [switch]$Force, [switch]$EnableException ) begin { if ($Force) { $ConfirmPreference = 'none' } } process { if (-not $Snapshot -and -not $Database -and -not $ExcludeDatabase -and -not $InputObject) { Stop-Function -Message "You must specify either -Snapshot (to restore from) or -Database/-ExcludeDatabase (to restore to) or pipe in a snapshot" return } foreach ($instance in $SqlInstance) { try { $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $SqlCredential } catch { Stop-Function -Message "Error occurred while establishing connection to $instance" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue } $InputObject += Get-DbaDbSnapshot -SqlInstance $server -Database $Database -ExcludeDatabase $ExcludeDatabase -Snapshot $Snapshot | Sort-Object CreateDate -Descending if ($Snapshot) { # Restore databases from these snapshots Write-Message -Level Verbose -Message "Selected only snapshots" $dbs = $InputObject | Where-Object { $Snapshot -contains $_.Name } $baseDatabases = $dbs | Select-Object -ExpandProperty DatabaseSnapshotBaseName | Get-Unique if ($baseDatabases.Count -ne $Snapshot.Count -and $dbs.Count -ne 0) { Stop-Function -Message "Failure. Multiple snapshots selected for the same database" -Continue } } } foreach ($snap in $InputObject) { # In the event someone passed -Database and it got all the snaps, most of which were dropped by the first if ($snap.Parent) { $server = $snap.Parent if (-not $snap.IsDatabaseSnapshot) { Stop-Function -Continue -Message "$snap on $server is not a valid snapshot" } if (-not ($snap.IsAccessible)) { Stop-Function -Message "Database $snap is not accessible on $($snap.Parent)." -Continue } $othersnaps = $server.Databases | Where-Object { $_.DatabaseSnapshotBaseName -eq $snap.DatabaseSnapshotBaseName -and $_.Name -ne $snap.Name } $db = $server.Databases | Where-Object Name -eq $snap.DatabaseSnapshotBaseName $loginfo = $db.LogFiles | Select-Object Id, Size, Growth, GrowthType if (($snap | Where-Object FileGroupType -eq 'FileStreamDataFileGroup')) { Stop-Function -Message "Database $snap on $server has FileStream group(s). You cannot restore from snapshots" -Continue } if ($othersnaps -and -not $force) { Stop-Function -Message "The restore process for $db from $snap needs to drop other snapshots on $db. Use -Force if you want to drop these snapshots" -Continue } if ($Pscmdlet.ShouldProcess($server, "Remove other db snapshots for $db")) { try { $null = $othersnaps | Remove-DbaDatabase -Confirm:$false -EnableException } catch { Stop-Function -Message "Failed to remove other snapshots for $db on $server" -ErrorRecord $_ -Continue } } # Need a proper restore now if ($Pscmdlet.ShouldProcess($server, "Restore db $db from $snap")) { try { if ($Force) { $null = Stop-DbaProcess -SqlInstance $server -Database $db.Name, $snap.Name -WarningAction SilentlyContinue } $null = $server.Query("USE master; RESTORE DATABASE [$($db.Name)] FROM DATABASE_SNAPSHOT='$($snap.Name)'") } catch { Stop-Function -Message "Failiure attempting to restore $db on $server" -ErrorRecord $_ -Continue } } # Comparing sizes before and after, need to refresh to see if size foreach ($log in $db.LogFiles) { $log.Refresh() } foreach ($log in $db.LogFiles) { $matching = $loginfo | Where-Object ID -eq $log.ID $changeflag = 0 foreach ($prop in @('Size', 'Growth', 'Growth', 'GrowthType')) { if ($matching.$prop -ne $log.$prop) { $changeflag = 1 $log.$prop = $matching.$prop } } if ($changeflag -ne 0) { Write-Message -Level Verbose -Message "Restoring original settings for log file" $log.Alter() } } Write-Message -Level Verbose -Message "Restored. Remember to take a backup now, and also to remove the snapshot if not needed." Get-DbaDatabase -SqlInstance $server -Database $db.Name } } } } |