azure-sql-databaseazure-cliazure-log-analytics

How do I generate a list of all the times I've restored an Azure SQL Database?


In the Azure Portal, SQL Databases have a "Restore" feature, which will create a new database from a restore a backup/restore point.

Restore button in Azure Portal

Suppose I have an Azure SQL Database named "Contoso" and over the past 12 months I've used the "Restore" feature described above to restore a copy of Contoso several times. After each restore, the newly restored database was deleted within a few days.

How would I generate a list of all the times I conducted a restore of the Contoso database? Or all the times I've conducted a restore in general?

I've tried looking for a Azure CLI command or Log analytics query but keep finding results on listing restore points, rather than restore actions.


Solution

  • You'll find this information in the Activity Log for your SQL Server resource in Azure.

    The default maximum retention for events is 90 days.

    If you'd like to store this information for a longer period, you'll need to use Diagnostic Settings for the Activity Log.

    To set this up, or to check if this is currently enabled and you want to find where the data is being sent, navigate to your SQL Server resource, then select Activity Log, and finally select Export Activity Logs from the top menu.

    If this hasn't been configured for you, you can click on Add diagnostic settings and follow the steps to send your Activity Log events to a Log Analytics workspace, BlobStorage, etc.