Click on the banner to learn about and purchase my database training on Azure

Azure SQL Database - How to view automatic backup history

Reading Time: 2 minutes

Speak guys !!
Today (13/04) a novelty was announced in Azure SQL Database, which is the Azure SQL DB Backup History, which arrives as Preview.

This feature is a new DMV that has been made available in Azure SQL DB to allow you to view the execution history of automatic backups, which was a very common question among DBA's and there has always been a need to ensure that the base is up to date with backups and understand how these backups work in Azure.

Until then, the only way to know if the backup was working correctly was to open the Restore screen and check the available dates and times:

And now with this DMV, it is possible to easily consult, and in much more detail, how the backup is being done in the bank.

If you need to restore, just choose the exact time you want to restore, including day, hour, minute and second and the name of the new base that will be created with the restored data. Azure will not overwrite any data. If this is necessary, it must be done by you, consulting data from the restored base and changing/inserting it into the original base.

To better understand how backup/restore works in Azure SQL Database, I suggest watching my video below, which is exclusive content from my training Database Training on Azure, which I am making available for FREE to anyone reading this article.

Did you like the tutorial? If you are interested in purchasing the full training, just click here.

In the query below, I am viewing when my full, differential and log backups were made:

Result:

Based on this new DMV, we can see that full backups occur every week, differential backups occur every 12-24 hours and log backups every 5-10 minutes. Everything is done automatically so you don't have to worry about it.

Limitations of the dm_database_backups DMV (at least while it's still in Preview):

  • This DMV is available for the General Purpose, Business Critical, and DTU tiers. Not available for the Hyperscale tier
  • DMV dm_operation_status returns backup history for 35 days regardless of retention days settings
  • If a database has just been created, the first 2 backups may not show in the DMV
  • This DMV only lists normal short-term (PITR) backups. Long term backups (LTR) will not be returned

To learn more about this new DMV, take a look at official documentation.