-
Notifications
You must be signed in to change notification settings - Fork 9
/
Point in Time Recovery.ps1
206 lines (116 loc) · 7.4 KB
/
Point in Time Recovery.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
##############################################################################################################################
# Point In Time Recovery - Using SQL Server 2022's T-SQL Snapshot Backup feature
#
# Scenario:
# Perform a point in time restore using SQL Server 2022's T-SQL Snapshot Backup
# feature. This uses a FlashArray snapshot as the base of the restore, then restores
# a log backup.
#
# Prerequisites:
# 1. A SQL Server running SQL Server 2022 with a database having data files and a log file on two volumes that are each on different FlashArrays.
#
# Usage Notes:
# Each section of the script is meant to be run one after the other.
# The script is not meant to be executed all at once.
#
# Disclaimer:
# This example script is provided AS-IS and is meant to be a building
# block to be adapted to fit an individual organization's
# infrastructure.
##############################################################################################################################
# Import powershell modules
Import-Module dbatools
Import-Module PureStoragePowerShellSDK2
# Let's initalize some variables we'll use for connections to our SQL Server and it's base OS
$TargetSQLServer = 'SqlServer1' # SQL Server Name
$ArrayName = 'flasharray1.example.com' # FlashArray
$DbName = 'Database1' # Name of database
$BackupShare = '\\FileServer1\SHARE\BACKUP' # File system location to write the backup metadata file
$PGroupName = 'SqlServer1_Pg' # Name of the Protection Group on FlashArray1
$FlashArrayDbVol = 'Fa_Sql_Volume_1' # Volume name on FlashArray containing database files
$TargetDisk = '6000c29240f79ca82ef017e1fdc000a7' # The serial number if the Windows volume containing database files
# Build a PowerShell Remoting Session to the Server
$SqlServerSession = New-PSSession -ComputerName $TargetSQLServer
# Build a persistent SMO connection
$SqlInstance = Connect-DbaInstance -SqlInstance $TargetSQLServer -TrustServerCertificate -NonPooledConnection
# Let's get some information about our database, take note of the size
Get-DbaDatabase -SqlInstance $SqlInstance -Database $DbName |
Select-Object Name, SizeMB
# Connect to the FlashArray's REST API
$Credential = Get-Credential
$FlashArray = Connect-Pfa2Array –EndPoint $ArrayName -Credential $Credential -IgnoreCertificateError
# Freeze the database
$Query = "ALTER DATABASE $DbName SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
# Take a snapshot of the Protection Group while the database is frozen
$Snapshot = New-Pfa2ProtectionGroupSnapshot -Array $FlashArray -SourceName $PGroupName
$Snapshot
# Take a metadata backup of the database, this will automatically unfreeze if successful
# We'll use MEDIADESCRIPTION to hold some information about our snapshot and the flasharray its held on
$BackupFile = "$BackupShare\$DbName_$(Get-Date -Format FileDateTime).bkm"
$Query = "BACKUP DATABASE $DbName
TO DISK='$BackupFile'
WITH METADATA_ONLY, MEDIADESCRIPTION='$($Snapshot.Name)|$($FlashArray.ArrayName)'"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
# Let's check out the error log to see what SQL Server thinks happened
Get-DbaErrorLog -SqlInstance $SqlInstance -LogNumber 0 | Format-Table
# The backup is recorded in MSDB as a Full backup with snapshot
$BackupHistory = Get-DbaDbBackupHistory -SqlInstance $SqlInstance -Database $DbName -Last
$BackupHistory
# Let's explore the stuff in the backup header...
# Remember, VDI is just a contract saying what's in the backup matches what SQL Server thinks is in the backup.
Read-DbaBackupHeader -SqlInstance $SqlInstance -Path $BackupFile
# Let's take a log backup
$LogBackup = Backup-DbaDatabase -SqlInstance $SqlInstance -Database $DbName -Type Log -Path $BackupShare -CompressBackup
# Delete a table...I should update my resume, right? :P
Invoke-DbaQuery -SqlInstance $SqlInstance -Database FT_Demo -Query "DROP TABLE customer"
# Let's check out the state of the database, size, last full and last log
Get-DbaDatabase -SqlInstance $SqlInstance -Database $DbName |
Select-Object Name, Size, LastFullBackup, LastLogBackup
# Offline the database, which we'd have to do anyway if we were restoring a full backup
$Query = "ALTER DATABASE $DbName SET OFFLINE WITH ROLLBACK IMMEDIATE"
Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query
# Offline the volume
Invoke-Command -Session $SqlServerSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $using:TargetDisk } | Set-Disk -IsOffline $True }
# We can get the snapshot name from the $Snapshot variable above, but what if we didn't know this ahead of time?
# We can also get the snapshot name from the MEDIADESCRIPTION in the backup file.
$Query = "RESTORE LABELONLY FROM DISK = '$BackupFile'"
$Labels = Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
$SnapshotName = (($Labels | Select-Object MediaDescription -ExpandProperty MediaDescription).Split('|'))[0]
$ArrayName = (($Labels | Select-Object MediaDescription -ExpandProperty MediaDescription).Split('|'))[1]
$SnapshotName
$ArrayName
# Restore the snapshot over the volume
New-Pfa2Volume -Array $FlashArray -Name $FlashArrayDbVol -SourceName ($SnapshotName + ".$FlashArrayDbVol") -Overwrite $true
# Online the volume
Invoke-Command -Session $SqlServerSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $using:TargetDisk} | Set-Disk -IsOffline $False }
# Restore the database with no recovery, which means we can restore LOG native SQL Server backups
$Query = "RESTORE DATABASE $DbName FROM DISK = '$BackupFile' WITH METADATA_ONLY, REPLACE, NORECOVERY"
Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query -Verbose
# Let's check the current state of the database...its RESTORING
Get-DbaDbState -SqlInstance $SqlInstance -Database $DbName
# Restore the log backup.
Restore-DbaDatabase -SqlInstance $SqlInstance -Database $DbName -Path $LogBackup.BackupPath -NoRecovery -Continue
# Online the database
$Query = "RESTORE DATABASE $DbName WITH RECOVERY"
Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query
# Let's see if our table is back in our database...
# whew...we don't have to tell anybody since our restore was so fast :P
Get-DbaDbTable -SqlInstance $SqlInstance -Database $DbName -Table 'Customer' | Format-Table
# How long does this process take, this demo usually takes 450ms?
$Start = (Get-Date)
# Freeze the database
$Query = "ALTER DATABASE $DbName SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
#Take a snapshot of the Protection Group while the database is frozen
$Snapshot = New-Pfa2ProtectionGroupSnapshot -Array $FlashArray -SourceName $PGroupName
$Snapshot
#Take a metadata backup of the database, this will automatically unfreeze if successful
#We'll use MEDIADESCRIPTION to hold some information about our snapshot
$BackupFile = "$BackupShare\$DbName_$(Get-Date -Format FileDateTime).bkm"
$Query = "BACKUP DATABASE $DbName
TO DISK='$BackupFile'
WITH METADATA_ONLY, MEDIADESCRIPTION='$($Snapshot.Name)|$($FlashArray.ArrayName)'"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
$Stop = (Get-Date)
Write-Output "The snapshot time takes...$(($Stop - $Start).Milliseconds)ms!"