RestoreSQLFromAzureRecoveryServiceVault.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
<#PSScriptInfo
 
.VERSION 1.0.0
 
.GUID 672ab748-e5b6-4d14-bbc7-b19f8582db42
 
.AUTHOR Trey Troegel
 
.PROJECTURI https://github.com/treytro/Samples
 
.TAGS Azure Recovery Services Vault SQL Restore
 
#>


<#
.SYNOPSIS
    Restores a SQL backup stored in Azure Recovery Services vault to another SQL instance.
 
.DESCRIPTION
    This runbook demonstrates how to find the most recent full backup for a particular database
    and restore it to a SQL VM that has been registered with the vault.
 
    It has a dependency on Az.Accounts and Az.RecoveryServices powershell modules. These
    modules must be imported into the Automation account prior to execution.
 
.PARAMETER VaultName
    String name of the Azure Recovery Services Vault
 
.PARAMETER ResGroup
    String name of the Resource Group within which the vault resides
 
.PARAMETER SourceBackupServerFQDN
    String FQDN name of the server from which the SQL backup was taken, such as agtestnode1.jintech.com
 
.PARAMETER BkupToRestore
    The String name of the backup item to restore
 
    For a default instance, the format is typically "sqldatabase;mssqlserver;<databasename>"
 
    Use Get-AzRecoveryServicesBackupItem in this manner to retrieve the list of possible items to restore for this parameter:
        $vault = Get-AzRecoveryServicesVault -ResourceGroupName "PureEnergy" -Name "TreyVault"
        Get-AzRecoveryServicesBackupItem -BackupManagementType AzureWorkload -WorkloadType MSSQL -VaultId $vault.ID
 
.PARAMETER RestoreDestinationSrvFQDN
 
    String name of the destination VM. Typically FQDN such as agtestnode2.jintech.com; but to be certain, use Get-AzRecoveryServicesBackupProtectableItem to list out SQL instances that are registered with the vault. If your target is not in the list, then the SQL instance needs to be registered with ARS Vault.
 
    $vault = Get-AzRecoveryServicesVault -ResourceGroupName "PureEnergy" -Name "TreyVault"
    Get-AzRecoveryServicesBackupProtectableItem -WorkloadType MSSQL -VaultId $vault.ID -ItemType SQLInstance
 
.PARAMETER RestoreDestinationInst
    String name of the destination SQL instance. It typically takes the format of "sqlinstance;mssqlserver" where mssqlserver is a default instance.
    Use Get-AzRecoveryServicesBackupProtectableItem as noted above to find the exact string for the instance.
 
.EXAMPLE
 
.NOTES
    AUTHOR: Trey Troegel
    LASTEDIT: July 13, 2020
#>


param
(
    [Parameter(Mandatory=$true)]
    [String] $VaultName,

    [Parameter(Mandatory=$true)]
    [String] $ResGroup,

    [Parameter(Mandatory=$true)]
    [String] $SourceBackupServerFQDN,

    [Parameter(Mandatory=$true)]
    [String] $BkupToRestore,

    [Parameter(Mandatory=$true)]
    [String] $RestoreDestinationSrvFQDN,

    [Parameter(Mandatory=$true)]
    [String] $RestoreDestinationInst

)

# Ensures you do not inherit an AzContext in your runbook
Disable-AzContextAutosave –Scope Process

Write-Verbose –Message ""
Write-Verbose –Message "------------------------ Authentication ------------------------"
Write-Verbose –Message "Logging into Azure ..."

$connection = Get-AutomationConnection -Name AzureRunAsConnection

while(!($connectionResult) -And ($logonAttempt -le 2))
{
    $LogonAttempt++
    # To connect to Azure Government use: Connect-AzAccount -Environment AzureUSGovernment
    $connectionResult =    Connect-AzAccount `
                               -ServicePrincipal `
                               -Tenant $connection.TenantID `
                               -ApplicationId $connection.ApplicationID `
                               -CertificateThumbprint $connection.CertificateThumbprint

    Start-Sleep -Seconds 30

}

Write-Verbose –Message ""
Write-Verbose –Message "Finding Azure Recovery Vault..."
$vault = Get-AzRecoveryServicesVault -ResourceGroupName $ResGroup -Name $VaultName

<#
Use Get-AzRecoveryServicesBackupItem in the manner to below to find the exact "-Name" parameter of the database to be restored. This will also give you the sometimes elusive Container name that is used in other commands.
 
    $vault = Get-AzRecoveryServicesVault -ResourceGroupName "MyResGroup" -Name "MyVault"
    Get-AzRecoveryServicesBackupItem -BackupManagementType AzureWorkload -WorkloadType MSSQL -VaultId $vault.ID
 
#>


Write-Verbose –Message ""
Write-Verbose –Message "------------------Finding a backup to be restored------------------"
Write-Verbose –Message "Finding backup items from $($SourceBackupServerFQDN)"
Write-Verbose –Message ""
# There can be multiple database backups with the same name, but from different source servers registered in with vault. A common scenario would be the SQL system databases (master, msdb etc.).
# Therefore, we need to filter Get-AzRecoveryServicesBackupItem by $SourceBackupServerFQDN in order to target the correct backup.
$bkpItem = Get-AzRecoveryServicesBackupItem -BackupManagementType AzureWorkload -WorkloadType MSSQL -Name $BkupToRestore -VaultId $vault.ID | Where-Object ServerName -eq $SourceBackupServerFQDN

$startDate = (Get-Date).AddDays(-14).ToUniversalTime()
$endDate = (Get-Date).ToUniversalTime()
$RecPointList = Get-AzRecoveryServicesBackupRecoveryPoint -Item $bkpItem -VaultId $vault.ID -StartDate $startdate -EndDate $endDate

# Next, narrow down the list of recovery points to the most recent Full backup by sorting the list Descending and taking the top 1 record
$RecPoint = $RecPointList | Where-Object RecoveryPointType -eq "Full" | Sort-Object -Descending -Property RecoveryPointTime | Select-Object -First 1

<#
To get a list of potential targets, use Get-AzRecoveryServicesBackupProtectableItem to list out SQL instances that are registered with the vault. If your target is not in the list, then the SQL instance needs to be registered with ARS Vault.
You'll want to pull BOTH the -Name and -ServerName parameters from the output of Get-AzRecoveryServicesBackupProtectableItem
 
    $vault = Get-AzRecoveryServicesVault -ResourceGroupName "MyResGroup" -Name "MyVault"
    Get-AzRecoveryServicesBackupProtectableItem -WorkloadType MSSQL -VaultId $vault.ID -ItemType SQLInstance
#>

$NewDestinationInstance = Get-AzRecoveryServicesBackupProtectableItem -WorkloadType MSSQL -ItemType SQLInstance -Name $RestoreDestinationInst -ServerName $RestoreDestinationSrvFQDN -VaultId $vault.ID

<#
Use Set/Get-AzRecoveryServicesBackupWorkloadRecoveryConfig to adjust restore options such as Restored DB Name, alternate mdf/ldf restore paths, and whether to overwrite an existing database.
#>

$InstanceWithFullConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -RecoveryPoint $RecPoint -TargetItem $NewDestinationInstance -AlternateWorkloadRestore -VaultId $vault.ID

Write-Verbose –Message ""
Write-Verbose –Message "------------------Restoring------------------"
Write-Verbose –Message "Restoring $($BkupToRestore) to $($RestoreDestinationSrvFQDN)"
Write-Verbose –Message ""
# This is the command that actually performs the restore.
Restore-AzRecoveryServicesBackupItem -WLRecoveryConfig $InstanceWithFullConfig -VaultId $vault.ID