question

YuChen-9120 avatar image
1 Vote"
YuChen-9120 asked SadiqhAhmed-MSFT commented

Powershell find Always On protection status in Recovery Service Vault

I am writing a powershell script to check the backup items for SQL VM in the Recovery service vault. My script looks like below, it loops through the subscription and find all Vaults and then all container in those vaults and list the backup items. However, it only return instance backups, not Always On backups.

$Subscritions=Get-AzSubscription | Where-Object {$_.name -like "abc1*"}
foreach ($subscition in $Subscritions)
{

$subscition |Set-AzContext

     $vaults = Get-AzRecoveryServicesVault 
     foreach($vault in $vaults)
     {

         $Containers =Get-AzRecoveryServicesBackupContainer -ContainerType "AzureVMAppContainer" -Status "Registered" -VaultId $vault.ID 
                 foreach ($container in $Containers )
                 {
                 $backupitem=Get-AzRecoveryServicesBackupItem -Container $Container -WorkloadType MSSQL -VaultId $vault.ID
                  $backupitem |select -Property friendlyname, SErvername |ft
                 } 
               
        # $backupitem=$backupitem |select -Property ServerName,name, ProtectionStatus, LastBackupTime,LatestRecoveryPoint,ProtectionState,LastBackupErrorDetail,LastBackupStatus| out-datatable
        # Write-DataTable  -Data $backupitem  -ServerInstance YCHEN-LT -Database t1 -TableName dbo.BackupStatus 

     }
windows-server-powershellazure-sql-databaseazure-backup
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I@YuChen-9120 Sorry for the delayed response!

MHO, SQL AG containers are registered as container type: “SQLAvailabilityGroupContainer” , you should use this query to fetch details of AG.

0 Votes 0 ·

Thanks Sadiqh
there are only the following container types according to the help
AzureVM
Windows
AzureStorage
AzureVMAppContainer

I was not able to use the type you mentioned. it gives an error
Get-AzRecoveryServicesBackupContainer : Cannot bind parameter 'ContainerType'. Cannot convert value "SQLAvailabilityGroupContainer" to type
"Microsoft.Azure.Commands.RecoveryServices.Backup.Cmdlets.Models.ContainerType". Error: "Unable to match the identifier name SQLAvailabilityGroupContainer to a valid enumerator
name. Specify one of the following enumerator names and try again:
AzureVM, Windows, AzureSQL, AzureStorage, AzureVMAppContainer"
At line:1 char:70
+ ... ackupContainer -ContainerType "SQLAvailabilityGroupContainer" -Status ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Get-AzRecoveryServicesBackupContainer], ParameterBindingException

0 Votes 0 ·

Here is the result if I don't add container type or the container, it will prompt me

$Containers =Get-AzRecoveryServicesBackupContainer -Status "Registered" -VaultId $vault.ID
cmdlet Get-AzRecoveryServicesBackupContainer at command pipeline position 1
Supply values for the following parameters:
(Type !? for Help.)
ContainerType:
PS C:\Users\ychen> Get-AzRecoveryServicesBackupItem -WorkloadType MSSQL -VaultId $vault.ID
cmdlet Get-AzRecoveryServicesBackupItem at command pipeline position 1
Supply values for the following parameters:
(Type !? for Help.)
Container:


0 Votes 0 ·

@YuChen-9120 As I mentioned below, please “use backupManagementType and workloadType”. Then you should get without a container.

0 Votes 0 ·

1 Answer

SadiqhAhmed-MSFT avatar image
1 Vote"
SadiqhAhmed-MSFT answered SadiqhAhmed-MSFT commented

@YuChen-9120 AG containers are not listed as physical containers. All containers returned by the Get-Container command are standalone.

But container is NOT a mandatory filter. You could just use backupManagementType and workloadType and get ALL DBs (whether standalone or AG) without passing the container.


If the response helped, do "Accept Answer" and up-vote it

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you! it works.


         $backupitem=Get-AzRecoveryServicesBackupItem  -WorkloadType MSSQL -VaultId $vault.ID -BackupManagementType AzureWorkload 
0 Votes 0 ·

@ChenYu-6050 You're welcome.


Please, do "Accept Answer" and up-vote the post that helped.
·

0 Votes 0 ·