Get-AzSqlDatabase limit of returned databases

[rokuUK]

Description

I noticed that Get-AzSqlDatabase returns only the first 100 databases, even if I use an additional filter, the filtering is still in the range of these 100 databases. Is it possible to change the size of the recordset

Script or Debug output

No response

Environment data

No response

Module versions

No response

Error output

No response

[ghost]

Thank you for your feedback. This has been routed to the support team for assistance.

[navba-MSFT]

@rokuUK Thanks for reaching out to us. Apologies for the late reply.
How many databases do you have currently under your Azure SQL server? I will be doing some research around the PS command which is limiting to 100 results. In the meantime, Could you please test the behavior with the Azure CLI to perform the list database operation? This will help us to isolate the issue.

az login
az account set --subscription "123435---***-12345"
az sql db list --resource-group MyResourceGroup --server myserver

More Info here. Awaiting your reply.

[rokuUK]

sorry, my mistake, I should have written from the beginning that Az it returns me the full list of 260 databases
using the cloud shell in the azure portal also returns all databases

[navba-MSFT]

@rokuUK Thanks for getting back. While I am doing research on this, Could you please test with the below Powershell script and let me know if that helps ?
More Info here

$auth=Get-AzAccessToken
$authHeader= $auth.token
$subscriptionId='1234---1234'
$serverName="myServerName"
$resourceGroupName='myRgName'
$uri= "https://management.azure.com/subscriptions/" + $subscriptionId + "/resourceGroups/" + $resourceGroupName + "/providers/Microsoft.Sql/servers/" + $serverName + "/databases?api-version=2021-02-01-preview"
$output=Invoke-WebRequest -Uri $uri -Method GET -Headers @{"Authorization"="Bearer $authHeader"}
$output | ConvertFrom-Json | select -ExpandProperty value | select -ExpandProperty id

[rokuUK]

the script returned 100 lines

[navba-MSFT]

@rokuUK Thanks for getting back. The support for pagination was added to Get-AzSqlDatabase PS command as per this thread. Also refer #15772

Remove the existing module and please install the most recent version of Az.sql module and test the behavior. Awaiting your reply.

[navba-MSFT]

@rokuUK This is a quick follow-up to check if you had a chance to follow the action plan suggested in my above comment. Awaiting your reply.

[rokuUK]

`PS C:\windows\system32>
$databases = Get-AzSqlDatabase -ServerName "myservername" -ResourceGroupName "myresourcegroupname" #| Format-Table -AutoSize
$databases.count
WARNING: Upcoming breaking changes in the cmdlet 'Get-AzSqlDatabase' :

• The output type 'Microsoft.Azure.Commands.Sql.Database.Model.AzureSqlDatabaseModel' is changing
• The following properties in the output type are being deprecated : 'BackupStorageRedundancy'
• The following properties are being added to the output type : 'CurrentBackupStorageRedundancy' 'RequestedBackupStorageRedundancy'
• The change is expected to take effect from the version : '3.0.0'
  Note : Go to https://aka.ms/azps-changewarnings for steps to suppress this breaking change warning, and other information on breaking changes in Azure PowerShell.
  262

PS C:\windows\system32> `

thank you, now it looks reasonable

[navba-MSFT]

@rokuUK Thanks for getting back. We will now proceed with closure of this github thread. Feel free to reopen this thread if you need any further assistance. We would be happy to assist you further on this.