Speak guys !!
Today (13/04) a novelty was announced in Azure SQL Database, which is the Azure SQL DB BackupHistory, 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.
In the query below, I am viewing when my full, differential and log backups were made:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SELECT [A].[backup_file_id], [B].[name] AS [database_name], [A].[backup_start_date], [A].[backup_finish_date], [A].[backup_type], (CASE WHEN [A].[backup_type] = 'L' THEN 'Log Backup' WHEN [A].[backup_type] = 'D' THEN 'Full Database Backup' WHEN [A].[backup_type] = 'I' THEN 'Incremental or Differential Backup' END) AS [backup_type_desc], [A].[in_retention] FROM [sys].[dm_database_backups] AS [A] JOIN [sys].[databases] AS [B] ON [B].[physical_database_name] = [A].[physical_database_name] WHERE B.[name] <> 'master' ORDER BY [A].[backup_type], [A].[backup_finish_date] DESC; |
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.
- 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.
