Script to list all of the Azure SQL databases in multiple Azure subscriptions

PowerShell Help
1

I have a working script that lists all of the Azure SQL databases in a single Azure subscription. The user is prompted to enter the subscription name. I’d like to be able to have the user enter multiple subscription names since we have multiple subscriptions.

 

Any idea how I can place this in the script?


Thanks,


Frank

            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              2
              
            

            

              
I tried like this, and I hope it works for you…


$AvailableContexts = Get-AzContext -ListAvailable 

$AllSQLServers = foreach ($Context in $AvailableContexts)
{ 
    $SqlServer = Get-AzSqlServer -DefaultProfile $Context
    if ($SqlServer)
    {
        $SqlServer | ForEach-Object { Add-Member -InputObject $_ -MemberType NoteProperty -Name Context -Value $Context }
    }
    $SqlServer
} 

$AllDatabases = foreach ($Server in $AllSQLServers)
{
    $Databases = Get-AzSqlDatabase -ServerName $Server.ServerName -ResourceGroupName $Server.ResourceGroupName -DefaultProfile $Server.Context
    if ($Databases)
    {
        $Databases | ForEach-Object { 
            $BackupState = Get-AzSqlDatabaseGeoBackupPolicy -ServerName $Server.ServerName -DatabaseName $_.DatabaseName -ResourceGroupName $Server.ResourceGroupName -DefaultProfile $Server.Context | Select-Object -ExpandProperty State
            Add-Member -InputObject $_ -MemberType NoteProperty -Name BackupState -Value $BackupState 
            Add-Member -InputObject $_ -MemberType NoteProperty -Name SubscriptionName -Value $Server.Context.Subscription.Name 
        }
    }
    $Databases
} 

$AllDatabases | Select-Object SubscriptionName, ResourceGroupName, ServerName, DatabaseName, CreationDate, BackupState


 


Thank you.

            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              3
              
            

            

              
Hi,


That worked, but there are a few subscriptions where my account don’t have proper access and errors are returned.


Is there a way to suppress the error message and let the script continue to run?


Get-AzSqlServer : Your Azure credentials have not been set up or have expired, please run Connect-AzAccount to set up your 
Azure credentials.
At line:3 char:18
+ $SqlServer = Get-AzSqlServer -DefaultProfile $Context
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : CloseError: (:) [Get-AzSqlServer], ArgumentException
+ FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.Server.Cmdlet.GetAzureSqlServer


Thanks,


Frank

            


            

              
              
              
            


            

                
                
              


          

          

            

              



              
                  
                  
              4
              
            

            

              
Then, set ErrorAction preference to SilentlyContinue, or remove those Contexts on which you don’t have access.


Thanks.