Get backup schedule and retention (short and long term) for SQL Servers using Azure Resource Graph Explorer

Question

I am trying to get the backup schedule and retention for SQL Servers using Azure Resource Graph Explorer. I am unable to find any such information.

The current query that I have is

resources
| where type == 'microsoft.sql/servers'

I am trying to get the short term and long term backup information for SQL Servers and databases but it doesn't seem to exist in the resources table. I am not sure where this information is stored. I couldn't find anything in recoveryservicesresources table related to PaaS instance of SQL Server backups.

Any ideas?

PS: I am trying to do this using KQL. I know it is possible using PowerShell using 'Get-AzSqlDatabaseBackupShortTermRetentionPolicy' and 'Get-AzSqlDatabaseBackupLongTermRetentionPolicy'.

Answer 1

I am trying to get the backup schedule and retention for SQL Servers using Azure Resource Graph Explorer. I am unable to find any such information.

The backup schedule and retention information for SQL Servers and databases is not stored in the resources table. However, you can use Azure Resource Graph Explorer to query information on backup for your Azure resources.

The RecoveryServicesResources table contains most of the backup-related records, such as job details, backup instance details You can use the following Kusto query to list all backup policies used for Azure SQL Servers:

RecoveryServicesResources | where  type == 'microsoft.recoveryservices/vaults/backuppolicies'

This query will return all backup policies. However, it will not provide you with the backup schedule and retention information for Azure SQL Server, you can use below PowerShell cmdlets to get this information.

 Get-AzSqlDatabaseBackupShortTermRetentionPolicy 
Get-AzSqlDatabaseBackupLongTermRetentionPolicy

To retrieve the backup schedule and retention information directly from Azure Resource Graph Explorer is not currently supported.