© purestorace SQL Server Always On Availability Groups Refresh and Restore Solutions with FlashArray Generated On Thu, 28 Sep 2023 12:11:51 GMT Confidential - Under NDARefreshing a Test/Dev Database using a Crash Consistent Snapshot Creating a New Test/Dev Database with an Application Consistent Snapshot of Production Prerequisites: Using SSMS and the Pure SSMS Extension with the PowerShell Backup SDK: Using PowerShell and the Pure Storage PowerShell Backup SDK: Refreshing a Test/Dev Database with a SQL Server Backup of Production Using the SQL Server and DBATools PowerShell Modules: Restoring a Production Replica from a AG Backup Snapshot Using PowerShell, the Pure Storage PowerShell Backup SDK, and DBATools: This article provides recovery solutions for Microsoft SQL Server Always On availability groups using the Pure Storage SQL Server Management Studio (SSMS) extension, the Pure Storage PowerShell Backup and FlashArray SDKs, and the third-party DBATools module. The scenarios and solutions shown in this article are outlined in this blog post. DBAToOs is avallable at DA Tools, ° It is recommended best practice that any primary database in an AG should be placed ona 7 separate FlashArray volume other than the volume that contains the default SQL Server master, model, and msdb databases. Variations and updates of the scripts provided in this document are available in our SQL Scripts GitHub Repository. The scripts provided in this document and in the GitHub repository are intended to be used to assist you in the process of generating your own scripts to provide the solution for your environment. We cannot possibly cover all scenarios, environments, and possibilities. They are not intended to be used as-is in a production environment. All scripts should be tested in a non-production environment before moving to production. Refreshing a Test/Dev Database using a Crash Consistent Snapshot This example PowerShell script gives the ability to overwrite or refresh multiple test or development databases from a Crash Consistent Snapshot of the Primary AG Database. I <# Use crash consistent snapshots from a FlashArray to overwrite (refresh) a TestDev environment. #> https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves.### Parameters SfaEndpoint = "10.10.1.1" #The array FQDN or IP address (preferred) SsourceVolumeName = *sourceDBvol" #The volume name of the source DB StargetServert = "devservert” #The first target server FQDN or IP address (preferred) StargetServer? = "devserver2" #The second target server FQDN or IP address (preferred). If no second target server, do not use, StargetVolume #The target volume on target server 1 for the DB StargetVolume: #The target volume on target server 2 for the DB. If no second target server, do not use, ‘tit Inputs # Modify for your environment SfaUser = "pureuser" SkeyLocation = "C\keystore\a.aes" SpassLocation = "C:\SecurePassStore\fa. txt” itt Build Array of servers and volumes StargetServers = StargetServert ,StargetServer2 StargetVolumes = StargetVolumet StargetVolume2 itiit Check for SDK Module. If not present, install it. if ((Get-nstalledModule -Name "PureStoragePowerShellSDK" -MinimumVersion 1.17.11.0 -ErrorAction SilentlyContinue) -eq $null){ Install-Module -Name PureStoragePowerShellSDK Import-Module PureStoragePowershellSDK } st Credentials # Modify this as you see fit for your security posture. # Retrieve Credentials for FA SunsecurePass = Get-Content SpassLocation SfaPass = ConvertTo-SecureString SunsecurePass -AsPlainText Force SfaCreds = New-Object System Management. Automation. PSCredential (SfaUser $faPass) -ErrorAction stop # Connect to FA SfaArray New-PfaArray -EndPoint $faEndpoint -Credentials $faCreds -IgnoreCertificateError # Create Snap SsuffixWithGUID = -join ((0x30..0x39) + ( Ox61..0x7A) | Get-Random -Count 10 | % {{char]$_}) +"-AG-Clone- Automation” Write-Host "Performing Snap of $sourceVolumeName" -ForegroundColor green SsourceSnap = New-PfaVolumeSnapshots -Array $faArray -Sources $sourceVolumeName -Sulfix SsuffixWithGUID Write-Host "Snap created by name $($sourceSnap.name)" -ForegroundColor green # Stop SQL Service ‘Write-Host "Attempting stop of SQL services on $targotServers" -ForegroundColor green foreach(Sserver in StargetServers) { Sservice = get-service -ComputerName Sserver Name MSSQL* $service.Stop() dof Sservice = Get-Service -ComputerName Sserver -Name MSSQL* https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. 3Write-Host "Service $(Sservice.cisplayname) on $server is in state $(Sservice.status), we will retry continously if not Stopped” -ForegroundColor yellow Start-Sleep 5 while(Sservice,status -ne "Stopped”) } # Offline disk on Target 1 Write-Host "Starting offne appropriate disks on $targetServert” -ForegroundColor green StargetVolume1SN = Get-PfaVolume -Array $faArray -name StargetVolumet | Select-Object -ExpandProperty “serial” StargetDisk = Get-Ciminstance Win32_DiskDrive -ComputerName StargetServert | 2{$_.serialnumber -eq StargetVolume1SN}] Select-Object * ‘Write-Host “Invoking Remote call to $targetServer' to offline disk with Serial Number $(StargetDisk SerialNumber)" -ForegroundColor Green Sresutts = Invoke-Command -ArgumentList StargetDisk -ComputerName StargetServer' -ScriptBlock{ StargetDisk = Sargs{0] Get-Diskjwhere-object {S_SerialNumber -eq StargetDisk.SerialNumber}| Set-Disk -1sOfline $true|Out-Null “rescan’ |diskpart|Out-Null StheDisk = Get-Disk|where-object {$_.SerialNumber -eq StargetDisk. SerialNumber} New-Object -TypeName PSCustomObject -Property @{DiskStatus=StheDisk.OperationalStatus) } if(Sresults.DiskStatus -ne “Oftine") { Write-Error “Did not successfully offline disk on StargetServert with remote call. Exiting” exit? } else ( ‘Write-Host “Offline success of appropriate disks on StargetServert" -ForegroundColor Green } # Offline disk on Target 2 ‘Write-Host “Starting offline appropriate disks on $targetServer2" -ForegroundColor green StargelVolume2SN = Gel-PfaVolume -Array $faArray -name StargelVolume? | Select-Object -ExpandProperty “serial” StargelDisk = Get-Ciminstance Win32_DiskDrive -ComputerName $targetServer? | 2($_serialnumber -eq StargetVolume2SN}] Select-Object * ‘Write-Host “Invoking Remote call to $targetServer? to offline disk with Serial Number $(StargetDisk SerialNumber)" -ForegroundColor Green Sresults = Invoke-Command -ArgumentList StargetDisk -ComputerName StargetServer?2 -ScriptBlock{ StargetDisk = Sargs{0] Get-Disk | where-object {S_.SerialNumber -eq StargetDisk SerialNumber] Set-l “rescan'|diskpart|OutNull StheDisk = Get-Diskjwhere-object {$_.SerialNumber -0q StargetDisk SerialNumber) New-Object -TypeName PSCustomObject -Property @{DiskStatus=StheDisk.OperationalStalus} -IsOfine Strue|Out-Null if(Sresults.DiskStatus -ne “Offline") { Write-Error "Did not successfully offiine disk on StargetServer?2 with remote call. Exiting” exit 1 } else { Write-Host "Offline success of appropriate disks on $targetServer2" -ForegroundColor Green } https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. 4# Overwrite Target Volumes Foreach($volume in $targetVolumes) { Write-Host "Overwriting Volume $volume with Snap $(SsourceSnap.name}" -ForegroundColor green New-PfaVolume -Array SfaArray -VolumeName $volume -Source $sourceSnap.name -Overwrite } # Online disk on Target 1 Write-Host "Starting Online appropriate disks on StargetServert" -ForegroundColor green StargetVolume1SN = Get-PfaVolume -Array $faArray -name StargetVolume'|Select-Object -ExpandProperty “serial” StargetDisk = Get-Ciminstance Win32_DiskDrive -ComputerName StargetServert |?(S_.serialnumber -eq StargetVolume1SN}] Select-Object * ‘Write-host “Invoking Remote call to StargetServert to online disk with Serial Number $(StargetDisk SerialNumber)" -ForegroundColor Green Sresutts = Invoke-Command -ArgumentList StargetDisk -ComputerName StargetServert -ScriptBlock{ diskpart|Out-Null StargetDisk = Sargs{0] get-diskjwhere-object {S_SerialNumber -eq StargetDisk SerialNumber}| set-disk -IsOffline $false “rescan’ |diskpart|Out-Null StheDisk = get-disk|where-object {S_.SerialNumber -eq StargetDisk SerialNumber} New-Object -TypeName PSCustomObject -Property @{DiskStatus=StheDisk.OperationalStatus} } if($results.DiskStatus -ne “Online { Write-Error “Did not successfully Online disk on StargetServert with remote call. Exiting” exit? } else ( write-host “Online success of appropriate disks on $targetServert” -ForegroundGolor Green } # Online disk on Target 2 ‘Write-Host "Starting online appropriate disks on $targetServer2" -ForegroundColor green StargelVolume2SN = Gel-PfaVolume -Array SfaArray -name StargetVolume2|Select-Object -ExpandProperty "serial StargetDisk = Get-Ciminstance Win32_DiskDrive -ComputerName $targetServer? [2(S_.serialnumber -0q StargetVolume2SN}] Select-Object * Write-Host “Invoking Remote call to StargetServer2 to online disk with Serial Number $(StargetDisk. SerialNumber)" -ForegroundColor Green Sresults = Invoke-Command -ArgumentList StargetDisk -ComputerName $targetServer? -ScriptBlock{ diskpart|Out-Null StargetDisk = Sargs(0] gel-disk}where-object {S_.SerialNumber -eq StargetDisk SerialNumber}| set-disk -IsOffline $false “rescan’|diskpart|Out-Null StheDisk = get-disklwhere-object {$_SerialNumber -oq StargetDisk SerialNumber} New-Object -TypeName PSCustomObject -Property @{DiskStalus=SthoDisk. OperationalStalus} } if(Sresults.DiskStatus -ne "Ontine") { Write-Error “Did not successfully Online disk on StargetServer2 with remote call. Exiting” exit? } else { https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. 5Write-Host "Online success of appropriate disks on $targetServer2" -ForegroundColor Green } # Start SAL Service Write-Host “Attempting start of SQL services on StargetServers* -ForegroundColor green foreach($server in StargetServers) { \Write-Host "Attempting start of SQL services on $server now’ -ForegroundColor green $service = Get-Service -ComputerName $server -Name MSSQL* Ssorvice.Start() dof $sorvice = Get-Service -ComputerName $server -Name MSSQL* \Write-Host "Service $(Sservice.cisplayname) on $server isin state S($service.status), we will retry continously if not Running" -ForegroundColar yellow Start-Sleep 5 }while($service status -ne "Running") } # Cleanup snapshot \Write-Host "Removing snapshot $(SsourceSnap.name) from Array $(SfaArray.Endpoint)" -ForegroundColor green Remove-PfaVolumeOrSnapshot -Array SfaArray -Name $sourceSnap name | OutNull Write-Host "Script Complete” -ForegroundColor green #END Creating a New Test/Dev Database with an Application Consistent Snapshot of Production Prerequisites: + Ensure that SQL Management Studio version 18.5 or later is installed on the management machine. + If not already installed, install the Pure Storage SSMS Extension using these instructions, This will install the ‘SSMS extension, The Pure Storage PowerShell Backup SDK module, and the Pure Storage Volume Shadow copy Service (VSS) components. + Ensure the Databases and associated primary and secondary AG replicas are properly synchronized. Refer to this Microsoft article for more information on AG synchronization states. + Ensure that PowerShell Remoting is enabled on the SQL Servers that the scripts will be run against. This document shows how to enable this PowerShell feature. https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. 6Using SSMS and the Pure SSMS Extension with the PowerShell Backup SDK: 1. In SSMS on the management machine, connect to the primary AG replica source server and the intended test/dev target server. 2. In the Pure Storage SSMS Extension, create a backup configuration for the primary database. Ensure that "Copy-only’ Is selected. ccna ea rewwy [RL] @ el 3. Backup the secondary database using the SSMS extension. https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves.4, Mount the secondary backup on the test/dev target SQL server using the Pure Storage SSMS extension. cootguaton oamtace | SoueComputr | owerDve | Veer | SouceviName MouneCarpute | Mau oe i] @ MotB Scan =a x — C— en 88 rent ts Mount Oi s Wow Pombelscat || Wevat | | Conc 5. You now have the option to copy the database files to a different volume, or use it as-is in a mounted drive. 6. Doan Attach in SSMS to add the database to the Test/Dev target SQL Server. if the database name already exists on the server, you must choose a new name for the database. Using PowerShell and the Pure Storage PowerShell Backup SDK: This script loads the SQLServer and Pure Storage PowerShell Backup SDK modules on startup. Ensure that these modules are installed before executing this script. # Run from a SQL management machine with SSMS and the PureStorage SSMS extension installed. # Begin script # PowerShell remoting must also be enabled on the source and target servers https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. 8Import-Module PureStorageBackupSDk [Net.ServicePointManager}::SecurityProtocol = [NetSecurityProtocolType}:TIs12 if ((Get-InstalledModule -Name "SQLServer" -ErrorAction SilentlyContinue) -eq Snul { Install-Module Name SQLServer Import-Module SQLServer } SsourceServer = ‘servert' # Change to your source server StargetServer = ‘server?’ # Change to your target server SsourcePSSession = New-PSSession -ComputerName SsourceServer StargetPSSession = New-PSSession -ComputerName $targetServer # Enter remoting session on source server Enter-PSSession $sourcePSSession # Check for SDK Module on source server. Ifnot present, install it [Net.ServicePointManager|::SecurityProtocol = [Net.SecurityProtocolType}:TIs12 if ((Get-nstalledModule -Name "SQLServer" -ErrorAction SilentlyContinue) -eq Snull) { Install-Module Name SQLServer Import-Module SQLServer } 4 Backup the secondary with Copy Only 4 Change parameters to match your environment ‘Add-PfaBackup.Job -ConfigName "snapAG” -Component "newdb: ray" -MetadataDir "C:\Usersiadministrator\AppDatalRoamin Invoke-PfaBackupJob -ConfigName "snapAG' Remove-PfaBackupJob -ConfigName "snapAG’ -ComputerName "server" -FAName CopyOnly # Retrieve the snapshot and mount itto the target as S: # Change DB name & drive letter if necessary $gelSnapshot = Get-PfaBackupHislory | Where-Object component -eq newdb2 | Sort-Object Historyld - Descending Mount-PfaBackupJob -Historyid $getSnapshot|0 historyid -driveletter -mountcompulter StargetServer # Enter remoting session on targer server Enter-PSSession StargetPSSession # Attach the mounted DB to the target SQL instance # Change DB name & drive letter if necessary SattachDB = @" USE [master] Go CREATE DATABASE [testdevDB] ON (FILENAME = 's:\newdb2.md?’),(FILENAME = 's:\newdb2_log ld’ for ATTACH co '@ # Change server name [Net.ServicePointManager}::SecurityProtocol = [NetSecurityProtocolType}:TIs12 if ((Get-InstalledModule Name "SQLServer" -ErrorAction SilentlyContinue) -eq Snul { Install-Module Name SQLServer Import-Module SQLServer https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. 9Invoke-Sqlemd SattachDB -QueryTimeout 3600 -Serverlnstance ‘server2' -erroraction 'silentiycontinue! Exit-PSSession Remove-PSSession $targetPSSession Remove-PSSession $sourcePSSession Refreshing a Test/Dev Database with a SQL Server Backup of Production In this scenario, the Test/Dev SQL Servers also have a Availal ity Group configured (good practice!) and need to get a refresh of the database from the Production AG primary replica, function RefreshProdTodev { [CmdletBinding()] PARAM ( [Parameter(Mandatory = Strue)] [string] SAg, [Parameter(Mandatory = Strue)] [string] SprodServer, [Parameter(Mandatory = Strue)] [string] SprodBackupfolder, [Parameter(Mandatory = Strue)] [string] SdevServer, [Parameter(Mandatory = Strue)] {string} Sdovinstance, [Parameter(Mandatory = Strue)] {string} $dovDatabase, {Parameter(Mandatory = Strue)] [string] $Primary, {Parameter(Mandatory = Strue)] {stringf https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. un$Secondarios ) Begin{ <# Check for DBATools and SQLServer. If DBATools or SQLServer modules are not present, install and import them. #> [Net.ServicePointManager|::SecurityProtocol = [Net.SecurityProtocolType}:TIs12 if ((Get-InstalledModule -Name "DBATools" -ErrorAction SilentlyContinue) -eq Snull) { Install-Module -Name DBATooIs -Force Import-Module DBATooIs, ) if ((Get-InstalledModule Name "SQLServer" -ErrorAction SilentlyContinue) -eq Srl { Install-Module Name SQLServer -Force Import-Module SQLServer ) ) Process { Try{ Sexists = Get-DbaDatabase -Sqllnstance $primary -Database $devDatabase; if Gexists) ( <<# Remove the database to be restored from the Dev Availability Group #> Remove-DbaAgDatabase -Sallnstance $Primary -Database SdevDatabase -Ag $Ag -Confirm:Sfalse;, <# Drop the database from all secondary replicas #> Remove-DbaDatabase -Sqllnstance $Secondaries -Dalabase $devDatabase -ConfirmSfalse; d Get-DbaDatabase -Sqlinstance Sdevinstance -Dalabase $devDatabase | Sel-DbaDbState -SingleUser - Force; <<# Restore the database to the primary replica #> Restore-DbaDatabase -Sqlinstance SdevServer -Path \\$prodServer\SprodBackupfolder - DatabaseName $devDatabase -NoRecovery -DestinationDataDirectory SdevFolder; # Set to MultiUser Get-DbaDatabase -Sqlinstance $Primary -Database $devDatabase | Sel-DbaDbState -MultiUser -Force; <# Automatic seeding to initialize the secondaries. #> Add-DbaAgDatabase -Sqlinstance $Primary -Ag SAg -Database $devDatabase -SeedingMode Automatic; <# Sync the AG properties to secondaries #> Syno-DbaAvailabiltyGroup Primary $devServer -AvailabilityGroup SAg } Catch { "Something went wrong, $, Break } https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. 2End { (87) Write-Host "Completed Restore.” } } } Restoring a Production Replica from a AG Backup Snapshot When you have restored a database into production, you can also use that backup to create the secondary replicas which will be pre-seeded with data, with the dependence on how much time has transpired between restores. This will allow for Disaster Risk Reduction (ORR). When you have Automatic Seeding enabled on the AG, the seeding of the restored database is automated and fully supported. Using PowerShell, the Pure Storage PowerShell Backup SDK, and DBATools: <# SYNOPSIS This script is will restore the latest SQL Server database that was backed up via an application consistent snapshot using the Pure Storage Backup SSMS extension and add it to an Availability Group. DESCRIPTION This script is will restore the latest SQL Server database application consistent snapshot from a Pure Storage FlashArray and add it to an Availability Group. If the database exists in the Availability Group, it will be removed from the Availability Group and dropped on all secondaries. Once the database is restored on the primary replica, it will be added to the secondaries. Automatic seeding will be used to initialize the secondaries. PARAMETER Ag The Availability Group the database will be restored to. PARAMETER Mountserver The database server to mount the restored snapshot to. PowerShell Remoting must be enabled on this server for drive mounting to work. PARAMETER Database The database to be restored. PARAMETER Driveletter https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. BThe drive letter to use to mount the database to the primary, Eg, "S:" (colon must be present). PARAMETER Primary The primary replica for the Availability Group. PARAMETER Secondaries ‘The secondary replica(s) for the Availability Group. Ifthere is more than one secondary, define parameter values as an array, Example - $secondaries = @("sql2019vm2" 'sq12019vm3"); NOTES Modified for the Pure Storage FlashArray and SSMS Extension. Thanks Frank Gill. EXAMPLE PSRestoreAgDatabase -Ag MyAgName -Mountserver MyServer -Database MyDatabase -BackupConfigname MyBackupContig -Primary MyPrimary -Secondaries "Secondary1","Secondary2"; > Function Restore-AgDatabase( [CmdletBinding()] PARAM ( [Parameter(Mandatory=$true)] istring] SAg, [Parameter(Mandatory=$true)] istring] $Mountserver, [Parameter(Mandatory=Strve)] istring] $Database, [Parameter(Mandatory=Strve)] istring] $Primary, [Parameter(Mandatory=Strue)] {string} $Secondaries, (Parameter(Mandatory=Strue)] (string) $Driveletter, [Parameter(Mandatory=Strue)] istring] $BackupConfighle ) Begin{ Write-Host "Start PSRestore-AgDatabase function. <# Check for DBATools and Pure Storage Backup SDK modules. if DBATooIs is not present, install it. If Pure Storage BackupSDK is not present, stop. If not loaded, import it. #> [Net.ServicePointManager}::SecurityProtocol = [Net.SecurityProtocolType}:TIs12 if ((Get-nstalledModule Name "DBATools" -ErrorAction SilentlyContinue) -eq Snull) { https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. 4Install-Module Name DBATools -Force Import-Module DBATools } if ((Get-InstalledModule -Name "PureStorageBackupSDk" -ErrorAction SilentlyContinue) -eq $nul) { ‘Write-Host "The Pure Storage SSMS extension, which includes the Backup SDK, must be installed to use this script." ‘Write-Host "Download the latest from https:/igithub.com/PureStorage-Connect/FlashArray-SSMS- Extension/releases" Break else { Import-Module PureStorageBackupSDK ) } } Process{ Try ‘Write-Host "Restoring and mounting last available snapshot for primary database from FlashArray* $getSnapshot = Get-PfaBackupHistory | Where-Object component -eq $BackupConfigname | Sort-Object Historyld -Descending Mount-PfaBackupJob -Historyld $getSnapshot(0] historyid -DriveLetter $Driveletter -MountComputer $Mountserver; STestPSSession = New-PSSession -ComputerName $Mountserver Enter-PSSession $TestPSSession Sbackupexists = Test-Path -Path $Driveletter, i{Sbackupexists -ne Strue) { throw "The snapshot did not restore. Please verify the snapshot in the FlashArray Ul and try again.” else { Remove-PSSession y Sreplicas = @(); Sreplicas += $Primary; Sreplicas += $Secondaries; Sversion = Invoke-DbaQuery -Sqlinstance $Primary -Database master -Query "SELECT SERVERPROPERTY(productversion’)"; Smajorversion = $version.Columnt Substring(0,2); Sprimaryinfo = Get-DbaAgReplica -Salinstance $Primary -AvailabilityGroup $Ag -Replica $Primary; Srole = Sprimaryinfo.Role; if(Srole «ne "Primary") throw "$Primary was entered as the primary replica and itis Srole"; } if(Smajorversion -it 13) { throw "SQL version is less than 2016 and this script cannot be used."; Break else. https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. 15{ foreach($replica in Sreplicas) { $services = Get-DbaService -Computer Sreplica; $serviceacct = $services | Select-Object ServiceName, StartName | Where-Object ServiceName - eq MSSQLSERVER; $sqlacct = $serviceacct.StartName; } } ) Sexists = Get-DbaDatabase -Sqllnstance $Primary -Database $Database; if(Sexists) { < Remove-DbaAgDatabase -Sqlinstance $Primary -Database $Database -AvailabilityGroup $Ag - Confirm:Sralse; Remove-DbaDatabase -Sglinstance $Secondaries -Database $Database -Confirm:Stalse; } <<# Attach the database to the primary replica #> Mount-DbaDatabase -Salinstance $Primary -Path $Driveletter -DatabaseName $Database; <# Automatic seeding to initialize the secondaries. #> ‘Add-DbaAgDatabase -Salinstance $Primary -AvailabilityGroup $Ag -Database $Database - SeedingMode Automatic; } Cateh{ "Something went wrong. $_* Break } } Enaf WS? Write-Host "Completed Restore.” } } } https:f/support purestorage.com/Salutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/Recovery. Solutions. with. Updated: Thu, 28 Sep 2023 12:11:51 GMT ©2023 Copyright Pure Storage. All rights reserves. 16