functions/Restore-DbaFromDatabaseSnapshot.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
Function Restore-DbaFromDatabaseSnapshot
{
<#
.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 SQL Server that you're connecting to
 
.PARAMETER Credential
Credential object used to connect to the SQL Server as a different user
 
.PARAMETER Databases
Restores from the last snapshot databases with this names only
NB: you can pass either Databases or Snapshots
 
.PARAMETER Snapshots
Restores databases from snapshots with this names only
NB: you can pass either Databases or Snapshots
 
.PARAMETER WhatIf
Shows what would happen if the command were to run. No actions are actually performed.
 
.PARAMETER Confirm
Prompts you for confirmation before executing any changing operations within the command.
 
.PARAMETER Force
If restoring from a snapshot involves dropping any other shapshot, you need to explicitly
use -Force to let this command delete the ones not involved in the restore process.
 
.NOTES
Tags: DisasterRecovery, Snapshot, Backup, Restore
Author: niphlod
 
dbatools PowerShell module (https://dbatools.io)
Copyright (C) 2016 Chrissy LeMaire
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/.
 
.LINK
 https://dbatools.io/Restore-DbaFromDatabaseSnapshot
 
.EXAMPLE
Restore-DbaFromDatabaseSnapshot -SqlServer sqlserver2014a -Databases HR, Accounting
 
Restores HR and Accounting databases using the latest snapshot available
 
.EXAMPLE
Restore-DbaFromDatabaseSnapshot -SqlServer sqlserver2014a -Snapshots HR_snap_20161201, Accounting_snap_20161101
 
Restores databases from snapshots named HR_snap_20161201 and Accounting_snap_20161101
 
#>

[CmdletBinding(SupportsShouldProcess = $true)]
Param (
[parameter(Mandatory = $true, ValueFromPipeline = $true)]
[Alias("ServerInstance", "SqlServer")]
[string[]]$SqlInstance,
[PsCredential]$Credential,
[switch]$Force
)

DynamicParam
{
if ($SqlInstance)
{
Get-ParamSqlSnapshotsAndDatabases -SqlServer $SqlInstance[0] -SqlCredential $Credential
}
}

BEGIN
{
$databases = $psboundparameters.Databases
$snapshots = $psboundparameters.Snapshots
}

PROCESS
{
if ($snapshots.count -eq 0 -and $databases.count -eq 0)
{
Write-Warning "You must specify either -Snapshots (to restore from) or -Databases (to restore to)"
return
}

foreach ($instance in $SqlInstance)
{
Write-Verbose "Connecting to $instance"
try
{
$server = Connect-SqlServer -SqlServer $instance -SqlCredential $Credential
}
catch
{
Write-Warning "Can't connect to $instance"
Continue
}

$all_dbs = $server.Databases

# vault to hold all programmed operations from --> to
$operations = @()

if ($snapshots.count -eq 0 -and $databases.count -eq 0)
{
# Restore all databases from the latest snapshot
Write-Verbose "Selected all databases"
$dbs = $all_dbs | Where-Object IsDatabaseSnapshot -eq $true
}
elseif ($databases.count -gt 0)
{
# Restore only these databases from their latest snapshot
Write-Verbose "Selected only databases"
$dbs = $all_dbs | Where-Object { $databases -contains $_.DatabaseSnapshotBaseName }
}
elseif ($snapshots.count -gt 0)
{
# Restore databases from these snapshots
Write-Verbose "Selected only snapshots"
$dbs = $all_dbs | Where-Object { $snapshots -contains $_.Name }
$base_databases = $dbs | Select-Object -ExpandProperty DatabaseSnapshotBaseName | Get-Unique
if($base_databases.count -ne $snapshots.count)
{
Write-Warning "Multiple snapshots selected for the same database, skipping"
Continue
}
}

$opshash = @{}

foreach($db in $dbs)
{
if($db.DatabaseSnapshotBaseName -notin $opshash.Keys) {
if($snapshots.count -gt 0)
{
# just in the need to drop every other snapshot
$todrop = $all_dbs | Where-Object {$_.DatabaseSnapshotBaseName -eq $db.DatabaseSnapshotBaseName}
$todrop = $todrop | Select-Object Name
$opshash[$db.DatabaseSnapshotBaseName] = @{
'from' = $db | Select-Object Name, DatabaseSnapshotBaseName, CreateDate
'drop' = $todrop
}
}
else
{
$opshash[$db.DatabaseSnapshotBaseName] = @{
'from' = $db
'drop' = @()
}
}
}
else
{
# store each older snapshot in the drop list while enumerating
if($db.createDate -gt $opshash[$db.DatabaseSnapshotBaseName]['from'].CreateDate) {
$prev = $opshash[$db.DatabaseSnapshotBaseName]['from']
$opshash[$db.DatabaseSnapshotBaseName]['from'] = $db | Select-Object Name, DatabaseSnapshotBaseName, CreateDate
$opshash[$db.DatabaseSnapshotBaseName]['drop'] += $prev
}
}
}
foreach($dbname in $opshash.Keys)
{
$drop = @()
foreach($todrop in $opshash[$dbname]['drop'])
{
$drop += $todrop.Name
}
$operations += @{
'from' = $opshash[$dbname]['from'].Name;
'to'   = $dbname;
'drop' = $drop
}
}

foreach($op in $operations)
{
                # Check if there are FS, because then a restore is not possible
                $all_FS = $server.Databases[$op['to']].FileGroups | Where-Object FileGroupType -eq 'FileStreamDataFileGroup'
                if($all_FS.Count -gt 0)
                {
                    Write-Warning "Database '$($op['to'])' has FileStream group(s). You cannot restore from snapshots"
                    [PSCustomObject]@{
Server   = $Server.Name
Database = $op['to']
Status   = 'Error'
Notes    = "Database '$($op['to'])' has FileStream group(s). You cannot restore from snapshots"
}
                    break
                }
# Get log size and autogrowth
$orig_logproperties = $server.Databases[$op['to']].LogFiles | Select-Object id, size
# Drop what needs to be dropped
$operror = $false

if($op['drop'].count -gt 1 -and $Force -eq $false)
{
Write-Warning "The restore process for '$($op['to'])' from '$($op['from'])' needs to drop the following:"
foreach($db in $op['drop']) {
Write-Warning $db
}
Write-Warning "Use -Force if you really want to drop these snapshots."
break
}
foreach($drop in $op['drop'])
{
If ($Pscmdlet.ShouldProcess($server.name, "Remove db snapshot $drop"))
{
# SKIP IT IF IT'S THE SAME NAME
if ($drop -ne $($op['from']))
{
$dropped = Remove-SqlDatabase -SqlServer $server -DBName $drop -SqlCredential $Credential
if ($dropped -notmatch "Success")
{
Write-Warning $dropped
$operror = $true
break
}
}
}
}
if($operror)
{
Write-Warning "Errors trying to restore '$($op['to'])' from '$($op['from'])'"
[PSCustomObject]@{
Server   = $Server.Name
Database = $op['to']
Status   = 'Error'
Notes    = "Failed to drop some snapshots"
}
break
}

# Need a proper restore now
If ($Pscmdlet.ShouldProcess($server.name, "Restore db '$($op['to'])' from '$($op['from'])'"))
{
$query = "RESTORE DATABASE [$($op['to'])] FROM DATABASE_SNAPSHOT='$($op['from'])'"
try
{
$server.KillAllProcesses($op['to'])
$server.ConnectionContext.ExecuteScalar($query)
}
catch
{
$operror = $true
Write-Exception $_
$inner = $_.Exception.Message
Write-Warning "Original exception: $inner, Query issued: $query"
}
}
if($operror)
{
Write-Warning "Errors trying to restore '$($op['to'])' from '$($op['from'])'"
[PSCustomObject]@{
Server   = $Server.Name
Database = $op['to']
Status   = 'Error'
Notes    = ''
}
break
}
# Comparing sizes before and after, need to reconnect to see if size
# changed
$server =  Connect-SqlServer -SqlServer $instance -SqlCredential $Credential
foreach($log in $server.Databases[$op['to']].LogFiles)
{
$matching = $orig_logproperties | Where-Object ID -eq $log.ID
if($matching.Size -ne $orig_logproperties.Size)
{
Write-Verbose "Resizing log to the original value"
$log.Size = $matching.Size
$log.Alter()
}
}
[PSCustomObject]@{
Server   = $Server.Name
Database = $op['to']
Status   = 'Restored'
Notes    = 'Remember to take a backup now, and also to remove the snapshot if not needed'
}
}
}
}
}