854 строки
36 KiB
PowerShell
854 строки
36 KiB
PowerShell
#requires -Version 5.1
|
|
#requires -RunAsAdministrator
|
|
|
|
### -----------------------------------------
|
|
### Strings
|
|
### -----------------------------------------
|
|
Data Strings
|
|
{
|
|
# culture="en-US"
|
|
ConvertFrom-StringData @'
|
|
# message
|
|
MsgComputeQuota = BackupType: ComputeQuota
|
|
MsgNetworkQuota = BackupType: NetworkQuota
|
|
MsgStorageQuota = BackupType: StorageQuota
|
|
MsgOffer = BackupType: Offer
|
|
MsgSubscription = BackupType: UserSubscription
|
|
MsgPlan = BackupType: Plan
|
|
|
|
# progress
|
|
ProgressConnectBackupStore = Connecting to the backup store with provided credential
|
|
ProgressConnectSqlServerDefault = Connecting to the SQL server '{0}' with Windows Authentication
|
|
ProgressConnectSqlServerProvided = Connecting to the SQL server '{0}' with provided credential
|
|
ProgressCreateTmpFolder = Creating a temporary folder {0} under {1}
|
|
ProgressGetBackupSnapshots = Getting required backup snapshots of BackupID {0}
|
|
ProgressDecryptSnapshot = Starting to decrypt snapshot {0}
|
|
ProgressCompletedDecryptionJobs = Decryption took {0} to complete
|
|
ProgressRestoreSubscriptionDb = Restoring subscription DB with {0}
|
|
|
|
# warning
|
|
WarningDecryptedBackupDataNotFound = Decrypted backup data for {0}: '{1}' is not found, skip listing those resources
|
|
|
|
# error
|
|
ErrorFailToConnectBackupStore = Failed to connect to the backup store '{0}' with provided credential. Exception: {1}
|
|
ErrorFailToConnectSqlServerDefault = Failed to connect to the SQL server '{0}' with Windows Authentication. Exception: {1}
|
|
ErrorFailToConnectSqlServerProvided = Failed to connect to the SQL server '{0}' with provided credential. Exception: {1}
|
|
ErrorFailToFindBackupSnapshots = Failed to find backup snapshots with BackupID {0}
|
|
ErrorFindMoreThanOneSnapshot = Found more than one snapshots for repository: {0}
|
|
ErrorFailToFindBackupChain = Failed to find backup chain for {0}
|
|
ErrorFailToDecryptSnapshot = Backup decryption failed for snapshot: '{0}'. Exception: {1}
|
|
ErrorFailToFindSqlBackupFile = Failed to find SQL backup file: '{0}'. Cannot restore the database.
|
|
ErrorDatabaseMissingItem = Failed to retrieve {0} as {1} with [{2}] value '{3}' is missing in the database.
|
|
|
|
# html
|
|
HtmlTitle = Backup Validation Report
|
|
HtmlCrpQuotaHeader = BackupType: ComputeQuota
|
|
HtmlNrpQuotaHeader = BackupType: NetworkQuota
|
|
HtmlSrpQuotaHeader = BackupType: StorageQuota
|
|
HtmlOfferHeader = BackupType: Offer
|
|
HtmlSubscriptionHeader = BackupType: Subscription
|
|
HtmlPlanHeader = BackupType: Plan
|
|
HtmlResourceCount = Count:
|
|
|
|
'@
|
|
}
|
|
|
|
# Import localized strings
|
|
Import-LocalizedData Strings -FileName BackupValidationTool.Strings.psd1 -ErrorAction SilentlyContinue
|
|
|
|
### -----------------------------------------
|
|
### Constants
|
|
### -----------------------------------------
|
|
$BackupStoreDriveName = "BackupStore"
|
|
$SubscriptionSqlDbName = "Microsoft.AzureStack.Subscriptions"
|
|
$SubTmpFolderName = "BackupValidationTmp"
|
|
$OutputReportFile = "BackupValidationReport.htm"
|
|
|
|
$BackupRepoNames = @{
|
|
ComputeQuota = "CRP;Microsoft.Compute.Admin;-;Quota"
|
|
NetworkQuota = "NRP;Microsoft.Network.Admin;-;Quota"
|
|
Storage = "SRP;SRP;-;-"
|
|
Subscription = "WAS;WasService;-;Microsoft.AzureStack.Subscriptions"
|
|
}
|
|
|
|
$JsonFileNames = @{
|
|
ComputeQuota = "CRP-Microsoft.Compute.Admin.json"
|
|
NetworkQuota = "NRP-Microsoft.Network.Admin.json"
|
|
StorageQuota = "SRP-Microsoft.Storage.Admin.json"
|
|
StorageAccount = "Account.json"
|
|
}
|
|
|
|
$StorageBackupSubRepoNames = @{
|
|
Quota = "SRP;Microsoft.Storage.Admin;-;Quota"
|
|
StorageAccount = "SRP;StorageAccount;-;SRP"
|
|
}
|
|
|
|
$SubscriptionSqlBackupFileNames = @{
|
|
BackupFile = "Microsoft.AzureStack.Subscriptions.bak"
|
|
LogFile = "Microsoft.AzureStack.Subscriptions.log"
|
|
}
|
|
|
|
function Decrypt-BackupSnapshot
|
|
{
|
|
param(
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[String]
|
|
$SnapshotFullName,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[String]
|
|
$TargetDirectory,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[PSCredential]
|
|
$BackupStoreCredential,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateScript({$_ | Test-Path -PathType Leaf})]
|
|
[String]
|
|
$DecryptionCertPath,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[SecureString]
|
|
$DecryptionCertPassword
|
|
)
|
|
|
|
$ErrorActionPreference = "Stop"
|
|
|
|
try
|
|
{
|
|
$dest = Join-Path $TargetDirectory ($SnapshotFullName -Split "\\")[-2] # sub directory with repo name
|
|
$dest = Join-Path $dest ($SnapshotFullName -Split "\\")[-1] # sub-sub directory with snapshot name
|
|
Write-Verbose ($Strings.ProgressDecryptSnapshot -f $SnapshotFullName) -Verbose
|
|
if (!(Test-Path $dest))
|
|
{
|
|
$null = New-Item $dest -ItemType Directory -Force | Out-Null
|
|
}
|
|
else
|
|
{
|
|
$null = Remove-Item "$dest\*" -Recurse -Force | Out-Null
|
|
}
|
|
|
|
$null = Unprotect-AzsBackup `
|
|
-BackupSnapshotZip $SnapshotFullName `
|
|
-Destination $dest `
|
|
-Certificate $DecryptionCertPath `
|
|
-CertificatePassphrase $DecryptionCertPassword `
|
|
-ShareCrendential $BackupStoreCredential | Out-Null
|
|
}
|
|
catch
|
|
{
|
|
throw ($Strings.ErrorFailToDecryptSnapshot -f $SnapshotFullName, $_)
|
|
}
|
|
}
|
|
|
|
function ConvertDictionariesToCustomObjects
|
|
{
|
|
param(
|
|
[Parameter(Mandatory = $true)]
|
|
[HashTable]
|
|
$Dictionaries
|
|
)
|
|
|
|
$ErrorActionPreference = "Stop"
|
|
|
|
return @($Dictionaries.Values.GetEnumerator()) | ForEach-Object {
|
|
$props = @{}
|
|
$_.GetEnumerator() | ForEach-Object {
|
|
$props[$_.Key] = $_.Value
|
|
}
|
|
|
|
[PSCustomObject]$props
|
|
}
|
|
}
|
|
|
|
function ExpandProperties
|
|
{
|
|
param(
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[PSCustomObject[]]
|
|
$Objects,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[String]
|
|
$ResourceType
|
|
)
|
|
|
|
$ErrorActionPreference = "Stop"
|
|
|
|
$returnedObjs = @()
|
|
|
|
if ($ResourceType -eq "quota")
|
|
{
|
|
$Objects | % {
|
|
$obj = $_ | ConvertTo-Json | ConvertFrom-Json
|
|
foreach ($p in $obj.properties.PSObject.Properties)
|
|
{
|
|
$obj | Add-Member -NotePropertyName $p.Name -NotePropertyValue $p.Value
|
|
}
|
|
|
|
$obj.PSObject.Properties.Remove('properties')
|
|
$returnedObjs += $obj
|
|
}
|
|
}
|
|
elseif ($ResourceType -eq "plan")
|
|
{
|
|
$Objects | % {
|
|
$obj = $_ | ConvertTo-Json | ConvertFrom-Json
|
|
$obj.QuotaIds = $obj.QuotaIds -join ";;;"
|
|
$obj | Add-Member -NotePropertyName "AsBasePlanOfOffers" -NotePropertyValue $($obj.Offers.Base -join ";;;")
|
|
$obj | Add-Member -NotePropertyName "AsAddonPlanOfOffers" -NotePropertyValue $($obj.Offers.Addon -join ";;;")
|
|
$obj | Add-Member -NotePropertyName "AsNonePlanOfOffers" -NotePropertyValue $($obj.Offers.None -join ";;;")
|
|
$obj.PSObject.Properties.Remove("Offers")
|
|
$returnedObjs += $obj
|
|
}
|
|
}
|
|
|
|
return $returnedObjs
|
|
}
|
|
|
|
<#
|
|
.Synopsis
|
|
List the ARM resources extracted from the backup.
|
|
|
|
.Description
|
|
- Get the backup chain according to the BackupID
|
|
- Decrypt the required snapshots to local
|
|
- Restore the backup into SQL server
|
|
- Retrieve ARM resources
|
|
|
|
.Example
|
|
$resources = Validate-AzsBackup -BackupStorePath $backupStorePath -BackupStoreCredential $backupStoreCredential -BackupID $backupID -DecryptionCertPath $decryptionCertPath -DecryptionCertPassword $decryptionCertPassword -TempFolder $tempFolder
|
|
$resources = Validate-AzsBackup -BackupStorePath $backupStorePath -BackupStoreCredential $backupStoreCredential -BackupID $backupID -DecryptionCertPath $decryptionCertPath -DecryptionCertPassword $decryptionCertPassword -SQLServerInstanceName $sqlServerInstanceName -SQLCredential $sqlCredential -TempFolder $tempFolder
|
|
#>
|
|
function Validate-AzsBackup
|
|
{
|
|
[CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'Medium')]
|
|
[OutputType([System.Collections.Hashtable])]
|
|
param(
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateScript({$_ | Test-Path -IsValid})]
|
|
[String]
|
|
$BackupStorePath,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[PSCredential]
|
|
$BackupStoreCredential,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateScript({[System.Guid]::TryParse($_, $([System.Management.Automation.PSReference][System.Guid]::Empty))})]
|
|
[String]
|
|
$BackupID,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateScript({$_ | Test-Path -PathType Leaf})]
|
|
[String]
|
|
$DecryptionCertPath,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[SecureString]
|
|
$DecryptionCertPassword,
|
|
|
|
[Parameter(Mandatory = $false)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[String]
|
|
$SQLServerInstanceName = "localhost\SQLEXPRESS", # Use default SQL Express server instance
|
|
|
|
[Parameter(Mandatory = $false)]
|
|
[ValidateNotNullOrEmpty()]
|
|
[PSCredential]
|
|
$SQLCredential,
|
|
|
|
[Parameter(Mandatory = $true)]
|
|
[ValidateScript({$_ | Test-Path -PathType Container})]
|
|
[String]
|
|
$TempFolder
|
|
)
|
|
|
|
$ErrorActionPreference = "Stop"
|
|
|
|
# STEP 1: Connect to the backup store with provided credential
|
|
Write-Verbose $Strings.ProgressConnectBackupStore -Verbose
|
|
try
|
|
{
|
|
$null = New-PSDrive -Name $BackupStoreDriveName -Root $BackupStorePath -Credential $BackupStoreCredential -PSProvider FileSystem | Out-Null
|
|
}
|
|
catch
|
|
{
|
|
throw ($Strings.ErrorFailToConnectBackupStore -f $BackupStorePath, $_)
|
|
}
|
|
|
|
Install-Module -Name SqlServer -AllowClobber -Force -Confirm:$false -RequiredVersion "22.0.59"
|
|
Import-Module SqlServer -DisableNameChecking
|
|
$sqlCommonParams = @{
|
|
ServerInstance = $SQLServerInstanceName
|
|
}
|
|
|
|
$sqlRestoreCommonParams = @{
|
|
ServerInstance = $SQLServerInstanceName
|
|
}
|
|
|
|
if (!$SQLCredential)
|
|
{
|
|
# STEP 2: Connect to the SQL server with Windows Authentication
|
|
Write-Verbose ($Strings.ProgressConnectSqlServerDefault -f $SQLServerInstanceName) -Verbose
|
|
try
|
|
{
|
|
$sqlInstance = Get-SqlInstance -ServerInstance $SQLServerInstanceName
|
|
}
|
|
catch
|
|
{
|
|
throw ($Strings.ErrorFailToConnectSqlServerDefault -f $SQLServerInstanceName, $_)
|
|
}
|
|
}
|
|
else
|
|
{
|
|
# STEP 2: Connect to the SQL server with provided credential
|
|
Write-Verbose ($Strings.ProgressConnectSqlServerProvided -f $SQLServerInstanceName) -Verbose
|
|
try
|
|
{
|
|
$sqlInstance = Get-SqlInstance -ServerInstance $SQLServerInstanceName -Credential $SQLCredential
|
|
$sqlRestoreCommonParams.SQLCredential = $SQLCredential
|
|
$sqlCommonParams.Credential = $SQLCredential
|
|
}
|
|
catch
|
|
{
|
|
throw ($Strings.ErrorFailToConnectSqlServerProvided -f $SQLServerInstanceName, $_)
|
|
}
|
|
}
|
|
|
|
# STEP 3: Create a tmp folder
|
|
Write-Verbose ($Strings.ProgressCreateTmpFolder -f $SubTmpFolderName, $TempFolder) -Verbose
|
|
$tmpDir = Join-Path $TempFolder $SubTmpFolderName
|
|
$null = Remove-Item $tmpDir -Force -Recurse -ErrorAction Ignore | Out-Null
|
|
$null = New-Item -Type Directory $tmpDir -Force | Out-Null
|
|
|
|
# STEP 4: Get the list of backup files according to the BackupID
|
|
Write-Verbose ($Strings.ProgressGetBackupSnapshots -f $BackupID) -Verbose
|
|
$backupRoot = Join-Path $BackupStorePath "MASBackup\progressivebackup"
|
|
$backupFiles = (Get-ChildItem -Path $backupRoot -Recurse | ? { $_.Name -match $BackupID -and $_.Name -match ".zip" }).FullName
|
|
$backupRepos = @($BackupRepoNames.Values.GetEnumerator())
|
|
if ($null -eq $backupFiles -or $backupFiles.Count -eq 0)
|
|
{
|
|
throw ($Strings.ErrorFailToFindBackupSnapshots -f $BackupID)
|
|
}
|
|
|
|
# It's progressive backup for Subscription, get the backup chain
|
|
# A backup file FullName is like: "$BackupStorePath\MASBackup\progressivebackup\1.2209.0.53\NRP;Microsoft.Network.Admin;-;Quota\202301171931_fa396b1d-1814-4c75-9ddc-fef71cf2ecd1_Full_C.zip"
|
|
# ($_ -Split "\")[-1] would be "202301171931_fa396b1d-1814-4c75-9ddc-fef71cf2ecd1_Full_C.zip" containing the backup ID.
|
|
# ($_ -Split "\")[-2] would be "NRP;Microsoft.Network.Admin;-;Quota" which is the name of the repo.
|
|
# ($_ -Split "\")[-3] would be "1.2209.0.53" which is the name of the backup store, also the stamp version.
|
|
$backupStoreName = ($backupFiles[0] -Split "\\")[-3]
|
|
$backupFiles = @()
|
|
$subscriptionBackups = @()
|
|
foreach ($backupRepo in $backupRepos)
|
|
{
|
|
$backupChain = Get-RepositoryBackupChain -ExternalShare $BackupStorePath `
|
|
-ExternalShareCredential $BackupStoreCredential -ProgressiveBackupStoreName $backupStoreName `
|
|
-RepositoryName $backupRepo -BackupId $BackupID
|
|
$backups = @()
|
|
foreach ($backup in $backupChain)
|
|
{
|
|
$backups += $backup.DataFileName
|
|
if ($backup.DataFileName -match $BackupID)
|
|
{
|
|
break
|
|
}
|
|
}
|
|
|
|
if (!$backups)
|
|
{
|
|
throw ($Strings.ErrorFailToFindBackupChain -f ($backupRepo))
|
|
}
|
|
|
|
if ($backupRepo -ne $BackupRepoNames.Subscription -and $backups.Count -gt 1)
|
|
{
|
|
throw ($Strings.ErrorFindMoreThanOneSnapshot -f ($backupRepo))
|
|
}
|
|
|
|
if ($backupRepo -eq $BackupRepoNames.Subscription)
|
|
{
|
|
$subscriptionBackups = $backups
|
|
}
|
|
|
|
$backupFiles += $backups
|
|
}
|
|
|
|
|
|
# STEP 5: Decrypt all required snapshots to the tmpDir
|
|
$start = Get-Date
|
|
foreach ($backupFile in $backupFiles)
|
|
{
|
|
Decrypt-BackupSnapshot -SnapshotFullName $backupFile `
|
|
-TargetDirectory $tmpDir -BackupStoreCredential $BackupStoreCredential `
|
|
-DecryptionCertPath $DecryptionCertPath -DecryptionCertPassword $DecryptionCertPassword
|
|
}
|
|
|
|
$end = Get-Date
|
|
$duration = $end - $start
|
|
Write-Verbose ($Strings.ProgressCompletedDecryptionJobs -f $duration) -Verbose
|
|
|
|
# STEP 6: Retrieve CRP quotas
|
|
$decryptedFolder = Join-Path $tmpDir $BackupRepoNames.ComputeQuota
|
|
$crpQuotaJsonFile = (Get-ChildItem -Path $decryptedFolder -Recurse | ? { $_.Name -match $JsonFileNames.ComputeQuota }).FullName
|
|
if (!$crpQuotaJsonFile)
|
|
{
|
|
Write-Warning ($Strings.WarningDecryptedBackupDataNotFound -f $BackupRepoNames.ComputeQuota, $JsonFileNames.ComputeQuota)
|
|
}
|
|
else
|
|
{
|
|
$crpQuotas = Get-Content $crpQuotaJsonFile | Out-String | ConvertFrom-Json
|
|
Write-Verbose $Strings.MsgComputeQuota -Verbose
|
|
Write-Host $($crpQuotas | Out-String)
|
|
}
|
|
|
|
# STEP 7: Retrieve NRP quotas
|
|
$decryptedFolder = Join-Path $tmpDir $BackupRepoNames.NetworkQuota
|
|
$nrpQuotaJsonFile = (Get-ChildItem -Path $decryptedFolder -Recurse | ? { $_.Name -match $JsonFileNames.NetworkQuota }).FullName
|
|
if (!$nrpQuotaJsonFile)
|
|
{
|
|
Write-Warning ($Strings.WarningDecryptedBackupDataNotFound -f $BackupRepoNames.NetworkQuota, $JsonFileNames.NetworkQuota)
|
|
}
|
|
else
|
|
{
|
|
$nrpQuotas = Get-Content $nrpQuotaJsonFile | Out-String | ConvertFrom-Json
|
|
Write-Verbose $Strings.MsgNetworkQuota -Verbose
|
|
Write-Host $($nrpQuotas | Out-String)
|
|
}
|
|
|
|
# STEP 8: Retrieve SRP quotas
|
|
$decryptedFolder = Join-Path $tmpDir $BackupRepoNames.Storage
|
|
$srpQuotaFolder = (Get-ChildItem -Path $decryptedFolder -Recurse | ? { $_.Name -match $StorageBackupSubRepoNames.Quota }).FullName
|
|
if (!$srpQuotaFolder)
|
|
{
|
|
Write-Warning ($Strings.WarningDecryptedBackupDataNotFound -f $BackupRepoNames.Storage, $StorageBackupSubRepoNames.Quota)
|
|
}
|
|
else
|
|
{
|
|
$srpZipFile = (Get-ChildItem -Path $srpQuotaFolder | ? { $_.Name -match ".zip" }).FullName
|
|
if (!$srpZipFile)
|
|
{
|
|
Write-Warning ($Strings.WarningDecryptedBackupDataNotFound -f $StorageBackupSubRepoNames.Quota, "snapshot zip file")
|
|
}
|
|
else
|
|
{
|
|
Expand-Archive -Path $srpZipFile -DestinationPath $srpQuotaFolder
|
|
$srpQuotaJsonFile = (Get-ChildItem -Path $srpQuotaFolder -Recurse | ? { $_.Name -match $JsonFileNames.StorageQuota }).FullName
|
|
if (!$srpQuotaJsonFile)
|
|
{
|
|
Write-Warning ($Strings.WarningDecryptedBackupDataNotFound -f $StorageBackupSubRepoNames.Quota, $JsonFileNames.StorageQuota)
|
|
}
|
|
else
|
|
{
|
|
$srpQuotas = Get-Content $srpQuotaJsonFile | Out-String | ConvertFrom-Json
|
|
Write-Verbose $Strings.MsgStorageQuota -Verbose
|
|
Write-Host $($srpQuotas | Out-String)
|
|
}
|
|
}
|
|
}
|
|
|
|
try
|
|
{
|
|
# STEP 9: Restore subscription DB
|
|
foreach ($snapshot in $subscriptionBackups)
|
|
{
|
|
$isFirstSnapshot = $snapshot -eq $subscriptionBackups[0]
|
|
$hasMoreSnapshotsToRestore = $snapshot -ne $subscriptionBackups[$subscriptionBackups.Count -1]
|
|
$decryptedFolder = Join-Path $tmpDir $BackupRepoNames.Subscription
|
|
$decryptedFolder = Join-Path $decryptedFolder ($snapshot -Split "\\")[-1]
|
|
Write-Verbose ($Strings.ProgressRestoreSubscriptionDb -f $decryptedFolder) -Verbose
|
|
$subBackupFile = Join-Path $decryptedFolder $SubscriptionSqlBackupFileNames.BackupFile
|
|
$subLogFile = Join-Path $decryptedFolder $SubscriptionSqlBackupFileNames.LogFile
|
|
if (!(Test-Path -Path $subBackupFile -PathType Leaf))
|
|
{
|
|
throw ($Strings.ErrorFailToFindSqlBackupFile -f $subBackupFile)
|
|
}
|
|
elseif (!(Test-Path -Path $subLogFile -PathType Leaf))
|
|
{
|
|
throw ($Strings.ErrorFailToFindSqlBackupFile -f $subLogFile)
|
|
}
|
|
|
|
Restore-SqlDatabase -Database $SubscriptionSqlDbName -BackupFile $subBackupFile `
|
|
-AutoRelocateFile -NoRecovery -ReplaceDatabase:$isFirstSnapshot @sqlRestoreCommonParams
|
|
Restore-SqlDatabase -Database $SubscriptionSqlDbName -BackupFile $subLogFile `
|
|
-RestoreAction Log -AutoRelocateFile -NoRecovery:$hasMoreSnapshotsToRestore @sqlRestoreCommonParams
|
|
}
|
|
|
|
# STEP 10: Retrieve offers
|
|
$SQLCmd = "SELECT [Id],[SubscriptionId] FROM [$SubscriptionSqlDbName].[subscriptions.internal].[ResellerSubscriptions]"
|
|
$resellerSubTable = Invoke-Sqlcmd -Database $SubscriptionSqlDbName -Query $SQLCmd -As DataSet -Encrypt Optional @sqlCommonParams
|
|
$resellerSubcriptions = @{}
|
|
if ($resellerSubTable.Tables.Count -gt 0 -and $null -ne $resellerSubTable.Tables[0].Rows)
|
|
{
|
|
$resellerSubTable.Tables[0].Rows | % { $resellerSubcriptions.Add($_.Id.ToString(), $_.SubscriptionId.ToString()) }
|
|
}
|
|
|
|
$SQLCmd = "SELECT [ProvisioningState],[ProvisioningStateName] FROM [$SubscriptionSqlDbName].[subscriptions.internal].[ProvisioningStates]"
|
|
$provisionStateTable = Invoke-Sqlcmd -Database $SubscriptionSqlDbName -Query $SQLCmd -As DataSet -Encrypt Optional @sqlCommonParams
|
|
$provisioningStates = @{}
|
|
if ($provisionStateTable.Tables.Count -gt 0 -and $null -ne $provisionStateTable.Tables[0].Rows)
|
|
{
|
|
$provisionStateTable.Tables[0].Rows | % { $provisioningStates.Add($_.ProvisioningState.ToString(), $_.ProvisioningStateName.ToString()) }
|
|
}
|
|
|
|
$SQLCmd = "SELECT [ResourceManagerType],[ResourceManagerTypeName] FROM [$SubscriptionSqlDbName].[subscriptions.internal].[ResourceManagerTypes]"
|
|
$resourceMgrTable = Invoke-Sqlcmd -Database $SubscriptionSqlDbName -Query $SQLCmd -As DataSet -Encrypt Optional @sqlCommonParams
|
|
$resourceManagerTypes = @{}
|
|
if ($resourceMgrTable.Tables.Count -gt 0 -and $null -ne $resourceMgrTable.Tables[0].Rows)
|
|
{
|
|
$resourceMgrTable.Tables[0].Rows | % { $resourceManagerTypes.Add($_.ResourceManagerType.ToString(), $_.ResourceManagerTypeName.ToString()) }
|
|
}
|
|
|
|
$SQLCmd = "SELECT [Id],[ResellerSubscriptionId],[ResourceGroupName],[ResourceLocation],[Tags],[Name],[DisplayName],[Description],[MaxSubscriptionsPerAccount]
|
|
,[ProvisioningState],[RoutingResourceManagerType] FROM [$SubscriptionSqlDbName].[subscriptions.internal].[Offers]"
|
|
$offerTable = Invoke-Sqlcmd -Database $SubscriptionSqlDbName -Query $SQLCmd -As DataSet -Encrypt Optional @sqlCommonParams
|
|
$offers = @{}
|
|
if ($offerTable.Tables.Count -gt 0 -and $null -ne $offerTable.Tables[0].Rows)
|
|
{
|
|
$offerColumnNames = $offerTable.Tables[0].Columns.ColumnName
|
|
foreach ($row in $offerTable.Tables[0].Rows)
|
|
{
|
|
$offerId = ""
|
|
$offer = [ordered] @{}
|
|
foreach ($column in $offerColumnNames)
|
|
{
|
|
$value = $row[$column].ToString()
|
|
if ($column -eq "ResellerSubscriptionId")
|
|
{
|
|
if (!$resellerSubcriptions.ContainsKey($value))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "offers", "reseller subcription", "Id", $value)
|
|
}
|
|
|
|
$offer[$column] = $resellerSubcriptions[$value]
|
|
}
|
|
elseif ($column -eq "ProvisioningState")
|
|
{
|
|
if (!$provisioningStates.ContainsKey($value))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "offers", "provisioning state", "ProvisioningState", $value)
|
|
}
|
|
|
|
$offer[$column] = $provisioningStates[$value]
|
|
}
|
|
elseif ($column -eq "RoutingResourceManagerType")
|
|
{
|
|
if (!$resourceManagerTypes.ContainsKey($value))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "offers", "resource manager type", "ResourceManagerType", $value)
|
|
}
|
|
|
|
$offer[$column] = $resourceManagerTypes[$value]
|
|
}
|
|
elseif ($column -eq "Id")
|
|
{
|
|
$offerId = $value
|
|
}
|
|
else
|
|
{
|
|
$offer[$column] = $value
|
|
}
|
|
}
|
|
|
|
$offer["Id"] = "/subscriptions/$($offer["ResellerSubscriptionId"])/resourceGroups/$($offer["ResourceGroupName"])/providers/Microsoft.Subscriptions.Admin/offers/$($offer["Name"])"
|
|
$offers.Add($offerId, $offer)
|
|
}
|
|
|
|
Write-Verbose $Strings.MsgOffer -Verbose
|
|
foreach ($offer in $offers.Values)
|
|
{
|
|
Write-Host ($offer | Out-String)
|
|
Write-Host "`n"
|
|
}
|
|
}
|
|
|
|
# STEP 11: Retrieve subscriptions
|
|
$SQLCmd = "SELECT [SubscriptionState],[SubscriptionStateName] FROM [$SubscriptionSqlDbName].[subscriptions.internal].[SubscriptionStates]"
|
|
$subStateTable = Invoke-Sqlcmd -Database $SubscriptionSqlDbName -Query $SQLCmd -As DataSet -Encrypt Optional @sqlCommonParams
|
|
$subscriptionStates = @{}
|
|
if ($subStateTable.Tables.Count -gt 0 -and $null -ne $subStateTable.Tables[0].Rows)
|
|
{
|
|
$subStateTable.Tables[0].Rows | % { $subscriptionStates.Add($_.SubscriptionState.ToString(), $_.SubscriptionStateName.ToString()) }
|
|
}
|
|
|
|
$SQLCmd = "SELECT [Id],[ResellerSubscriptionId],[SubscriptionId],[DisplayName],[OfferId],[Owner],[TenantId],[RoutingResourceManagerType],[State],[Tags] FROM [$SubscriptionSqlDbName].[subscriptions.internal].[Subscriptions]"
|
|
$subTable = Invoke-Sqlcmd -Database $SubscriptionSqlDbName -Query $SQLCmd -As DataSet -Encrypt Optional @sqlCommonParams
|
|
$subscriptions = @{}
|
|
if ($subTable.Tables.Count -gt 0 -and $null -ne $subTable.Tables[0].Rows)
|
|
{
|
|
$subscriptionColumnNames = $subTable.Tables[0].Columns.ColumnName
|
|
foreach ($row in $subTable.Tables[0].Rows)
|
|
{
|
|
$subscriptionId = ""
|
|
$subscription = [ordered] @{}
|
|
foreach ($column in $subscriptionColumnNames)
|
|
{
|
|
$value = $row[$column].ToString()
|
|
if ($column -eq "ResellerSubscriptionId")
|
|
{
|
|
if (!$resellerSubcriptions.ContainsKey($value))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "subscriptions", "reseller subcription", "Id", $value)
|
|
}
|
|
|
|
$subscription[$column] = $resellerSubcriptions[$value]
|
|
}
|
|
elseif ($column -eq "State")
|
|
{
|
|
if (!$subscriptionStates.ContainsKey($value))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "subscriptions", "subscription state", "SubscriptionState", $value)
|
|
}
|
|
|
|
$subscription[$column] = $subscriptionStates[$value]
|
|
}
|
|
elseif ($column -eq "RoutingResourceManagerType")
|
|
{
|
|
if (!$resourceManagerTypes.ContainsKey($value))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "subscriptions", "resource manager type", "ResourceManagerType", $value)
|
|
}
|
|
|
|
$subscription[$column] = $resourceManagerTypes[$value]
|
|
}
|
|
elseif ($column -eq "OfferId")
|
|
{
|
|
if (!$offers.ContainsKey($value))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "subscriptions", "offer", "Id", $value)
|
|
}
|
|
|
|
$subscription[$column] = $offers[$value].Id
|
|
}
|
|
elseif ($column -eq "Id")
|
|
{
|
|
$subscriptionId = $value
|
|
}
|
|
else
|
|
{
|
|
$subscription[$column] = $value
|
|
}
|
|
}
|
|
|
|
$subscription["Id"] = "/subscriptions/$($subscription["ResellerSubscriptionId"])/providers/Microsoft.Subscriptions.Admin/subscriptions/$($subscription["SubscriptionId"])"
|
|
$subscriptions.Add($subscriptionId, $subscription)
|
|
}
|
|
|
|
Write-Verbose $Strings.MsgSubscription -Verbose
|
|
foreach ($subscription in $subscriptions.Values)
|
|
{
|
|
Write-Host ($subscription | Out-String)
|
|
Write-Host "`n"
|
|
}
|
|
}
|
|
|
|
# STEP 12: Retrieve plans
|
|
$SQLCmd = "SELECT [PlanId],[ResourceId] FROM [$SubscriptionSqlDbName].[subscriptions.internal].[Quotas]"
|
|
$quotaTable = Invoke-Sqlcmd -Database $SubscriptionSqlDbName -Query $SQLCmd -As DataSet -Encrypt Optional @sqlCommonParams
|
|
$plan2quota = @{}
|
|
if ($quotaTable.Tables.Count -gt 0 -and $null -ne $quotaTable.Tables[0].Rows)
|
|
{
|
|
foreach ($row in $quotaTable.Tables[0].Rows)
|
|
{
|
|
$planId = $row.PlanId.ToString()
|
|
$resourceId = $row.ResourceId.ToString()
|
|
if ($plan2quota.ContainsKey(($planId)))
|
|
{
|
|
$plan2quota[$planId] += $resourceId
|
|
}
|
|
else
|
|
{
|
|
$plan2quota[$planId] = @($resourceId)
|
|
}
|
|
}
|
|
}
|
|
|
|
$SQLCmd = "SELECT [PlanLinkType],[PlanLinkTypeName] FROM [$SubscriptionSqlDbName].[subscriptions.internal].[PlanLinkTypes]"
|
|
$planLinkTypeTable = Invoke-Sqlcmd -Database $SubscriptionSqlDbName -Query $SQLCmd -As DataSet -Encrypt Optional @sqlCommonParams
|
|
$planLinkTypes = @{}
|
|
if ($planLinkTypeTable.Tables.Count -gt 0 -and $null -ne $planLinkTypeTable.Tables[0].Rows)
|
|
{
|
|
$planLinkTypeTable.Tables[0].Rows | % { $planLinkTypes.Add($_.PlanLinkType.ToString(), $_.PlanLinkTypeName.ToString()) }
|
|
}
|
|
|
|
$SQLCmd = "SELECT [OfferId],[PlanId],[PlanLinkType] FROM [$SubscriptionSqlDbName].[subscriptions.internal].[PlanLinks]"
|
|
$planLinkTable = Invoke-Sqlcmd -Database $SubscriptionSqlDbName -Query $SQLCmd -As DataSet -Encrypt Optional @sqlCommonParams
|
|
$plan2offer = @{}
|
|
if ($planLinkTable.Tables.Count -gt 0 -and $null -ne $planLinkTable.Tables[0].Rows)
|
|
{
|
|
foreach ($row in $planLinkTable.Tables[0].Rows)
|
|
{
|
|
$planId = $row.PlanId.ToString()
|
|
if (!$offers.ContainsKey($row.OfferId.ToString()))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "plans", "offer", "Id", $row.OfferId.ToString())
|
|
}
|
|
|
|
$offerId = $offers[$row.OfferId.ToString()].Id
|
|
if (!$planLinkTypes.ContainsKey($row.PlanLinkType.ToString()))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "plans", "plan link type", "PlanLinkType", $row.PlanLinkType.ToString())
|
|
}
|
|
|
|
$planLinkType = $planLinkTypes[$row.PlanLinkType.ToString()]
|
|
if ($plan2offer.ContainsKey(($planId)))
|
|
{
|
|
$plan2offer[$planId][$planLinkType] += $offerId
|
|
}
|
|
else
|
|
{
|
|
$emptyType2Offer = @{}
|
|
@($planLinkTypes.Values.GetEnumerator()) | % {
|
|
$emptyType2Offer[$_] = @()
|
|
}
|
|
|
|
$plan2offer[$planId] = $emptyType2Offer
|
|
$plan2offer[$planId][$planLinkType] += $offerId
|
|
}
|
|
}
|
|
}
|
|
|
|
$SQLCmd = "SELECT [Id],[ResellerSubscriptionId],[ResourceGroupName],[Tags],[Name],
|
|
[DisplayName],[Description],[ProvisioningState],[RoutingResourceManagerType]
|
|
FROM [$SubscriptionSqlDbName].[subscriptions.internal].[Plans]"
|
|
$planTable = Invoke-Sqlcmd -Database $SubscriptionSqlDbName -Query $SQLCmd -As DataSet -Encrypt Optional @sqlCommonParams
|
|
$plans = @{}
|
|
if ($planTable.Tables.Count -gt 0 -and $null -ne $planTable.Tables[0].Rows)
|
|
{
|
|
$planColumnNames = $planTable.Tables[0].Columns.ColumnName
|
|
foreach ($row in $planTable.Tables[0].Rows)
|
|
{
|
|
$planId = ""
|
|
$plan = [ordered] @{}
|
|
foreach ($column in $planColumnNames)
|
|
{
|
|
$value = $row[$column].ToString()
|
|
if ($column -eq "ResellerSubscriptionId")
|
|
{
|
|
if (!$resellerSubcriptions.ContainsKey($value))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "plans", "reseller subcription", "Id", $value)
|
|
}
|
|
|
|
$plan[$column] = $resellerSubcriptions[$value]
|
|
}
|
|
elseif ($column -eq "ProvisioningState")
|
|
{
|
|
if (!$provisioningStates.ContainsKey($value))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "plans", "provisioning state", "ProvisioningState", $value)
|
|
}
|
|
|
|
$plan[$column] = $provisioningStates[$value]
|
|
}
|
|
elseif ($column -eq "RoutingResourceManagerType")
|
|
{
|
|
if (!$resourceManagerTypes.ContainsKey($value))
|
|
{
|
|
throw ($Strings.ErrorDatabaseMissingItem -f "plans", "resource manager type", "ResourceManagerType", $value)
|
|
}
|
|
|
|
$plan[$column] = $resourceManagerTypes[$value]
|
|
}
|
|
elseif ($column -eq "Id")
|
|
{
|
|
$planId = $value
|
|
}
|
|
else
|
|
{
|
|
$plan[$column] = $value
|
|
}
|
|
}
|
|
|
|
$plan["Id"] = "/subscriptions/$($plan["ResellerSubscriptionId"])/resourceGroups/$($plan["ResourceGroupName"])/providers/Microsoft.Subscriptions.Admin/plans/$($plan["Name"])"
|
|
$plan["QuotaIds"] = $plan2quota[$planId]
|
|
$plan["Offers"] = $plan2offer[$planId]
|
|
$plans.Add($planId, $plan)
|
|
}
|
|
|
|
Write-Verbose $Strings.MsgPlan -Verbose
|
|
foreach ($plan in $plans.Values)
|
|
{
|
|
Write-Host ($plan | Out-String)
|
|
Write-Host "`n"
|
|
}
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
# Remove temp folder
|
|
$null = Remove-Item $tmpDir -Force -Recurse -ErrorAction Ignore | Out-Null
|
|
|
|
# Drop subscription DB
|
|
$disconnectScript = @"
|
|
DECLARE @DatabaseName nvarchar(50)
|
|
SET @DatabaseName = N'$SubscriptionSqlDbName'
|
|
DECLARE @SQL varchar(max)
|
|
SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
|
|
FROM MASTER..SysProcesses
|
|
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
|
|
--SELECT @SQL
|
|
EXEC(@SQL)
|
|
"@
|
|
|
|
Invoke-Sqlcmd -Query $disconnectScript -Encrypt Optional @sqlCommonParams -ErrorAction Continue
|
|
Invoke-Sqlcmd -Query ('Drop database "{0}"' -f $SubscriptionSqlDbName) -Encrypt Optional @sqlCommonParams -ErrorAction Continue
|
|
}
|
|
|
|
# Convert offer, subscription and plan into PSCustomObject
|
|
$offersObj = ConvertDictionariesToCustomObjects -Dictionaries $offers
|
|
$subscriptionsObj = ConvertDictionariesToCustomObjects -Dictionaries $subscriptions
|
|
$plansObj = ConvertDictionariesToCustomObjects -Dictionaries $plans
|
|
|
|
# Output results in HTML format to TempFolder
|
|
if ($crpQuotas.Count -gt 0)
|
|
{
|
|
$crpQuotaHtml = ExpandProperties -Objects $crpQuotas -ResourceType "quota" | ConvertTo-HTML -As List
|
|
}
|
|
|
|
if ($nrpQuotas.Count -gt 0)
|
|
{
|
|
$nrpQuotaHtml = ExpandProperties -Objects $nrpQuotas -ResourceType "quota" | ConvertTo-HTML -As List
|
|
}
|
|
|
|
if ($srpQuotas.Count -gt 0)
|
|
{
|
|
$srpQuotaHtml = ExpandProperties -Objects $srpQuotas -ResourceType "quota" | ConvertTo-HTML -As List
|
|
}
|
|
|
|
$offerHtml = $offersObj | ConvertTo-HTML -As List
|
|
$subscriptionHtml = $subscriptionsObj | ConvertTo-HTML -As List
|
|
|
|
if ($plansObj.Count -gt 0)
|
|
{
|
|
$planHtml = ExpandProperties -Objects $plansObj -ResourceType "plan" | ConvertTo-HTML -As List
|
|
$planHtml = $planHtml.Replace(";;;", "<br/>")
|
|
}
|
|
|
|
ConvertTo-HTML -Body "<h3>$($Strings.HtmlCrpQuotaHeader)</h3> <h3>$($Strings.HtmlResourceCount)$($crpQuotas.Count)</h3> $crpQuotaHtml `
|
|
<h3>$($Strings.HtmlNrpQuotaHeader)</h3> <h3>$($Strings.HtmlResourceCount)$($nrpQuotas.Count)</h3> $nrpQuotaHtml `
|
|
<h3>$($Strings.HtmlSrpQuotaHeader)</h3> <h3>$($Strings.HtmlResourceCount)$($srpQuotas.Count)</h3> $srpQuotaHtml `
|
|
<h3>$($Strings.HtmlOfferHeader)</h3> <h3>$($Strings.HtmlResourceCount)$($offersObj.Count)</h3> $offerHtml `
|
|
<h3>$($Strings.HtmlSubscriptionHeader)</h3> <h3>$($Strings.HtmlResourceCount)$($subscriptionsObj.Count)</h3> $subscriptionHtml `
|
|
<h3>$($Strings.HtmlPlanHeader)</h3> <h3>$($Strings.HtmlResourceCount)$($plansObj.Count)</h3> $planHtml" `
|
|
-Title $Strings.HtmlTitle | Out-File -FilePath $(Join-Path $TempFolder $OutputReportFile)
|
|
|
|
return @{
|
|
ComputeQuota = $crpQuotas
|
|
NetworkQuota = $nrpQuotas
|
|
StorageQuota = $srpQuotas
|
|
Offer = $offersObj
|
|
Subscription = $subscriptionsObj
|
|
Plan = $plansObj
|
|
}
|
|
}
|
|
|
|
Export-ModuleMember -Function Validate-AzsBackup |