Azure SQL DB Backup History - View backups of your Azure SQL Database

[Sudhir_Raparla]

Azure SQL Database takes regular automated backups for user databases and stores them in Azure Storage to enable point-in-time-restore to any desired point within configured retention. Automated backups include Full backups which are taken every week, Differential backups which are taken once in 12 to 24 hours and Log backups which are taken every 5 to 10 minutes. Backup automation process takes away the burden of manual backup management and provides restorability by default. Azure SQL Database abstracts this complex automation process and provides a simple experience to restore database: just select the point i.e., date and time, to which you would like to restore the database and click restore.

              However sometimes you would want to manually check and verify if the automated backups are sufficient to meet your data protection compliance and regulatory requirements. To help you in such scenarios Azure SQL Database has introduced a new feature called Backup History that lets you view the list of backups using simple T-SQL. Backup History introduced a new Dynamic Management View(DMV) called Sys.dm_database_backups, that contains metadata information on all the active backups that are needed for enabling point-in-time restore within configured retention. Metadata information includes:

• Backup_file_id – Backup file ID
• Database_guid –Logical Database ID
• Physical_Database_name – Physical Database Name
• Server_name – Physical Server Name
• Backup_start_date – Backup Start Timestamp
• Backup_finish_date – Backup End Timestamp
• Backup_Type – Type of Backup. D stands for Full Database Backup, L – Stands for Log Backup and I – Stands for differential backup
• In_Retention – Whether backup is within retention period or not. 1 stands for within retention period and 0 stands for out of retention

How to query Backup History Catalog view:

Users can query this view using T-SQL via all supported SQL clients like SSMS, Query Editor, Azure Data Studio, Visual Studio etc.  To query the DMV user requires VIEW DATABASE STATE permission on the database. Here are steps to query sys.dm_database_backups:

1. Get Server Name from Overview section of your Azure SQL Server
2. Connect to this server using SSMS.
   1. For Server Type select Database Engine
   2. For Server Name enter the Server Name from Azure SQL overview blade
   3. For Authentication select SQL Server Authentication
   4. For Login and Password, enter your Login and Password credentials
3. Select the database for which you would like to view the Backup history and create a New Query
4. Use following T-SQL command to query sys.dm_database_backups and view list of all active backups for this database:

Select * from sys.dm_database_backups 
ORDER BY backup_finish_date DESC

Please note we retain some database backups even though they are out of retention period SLA(in_retention=0), as they are needed to restore a database to a point in time within the retention SLA.

Preview Limitations and Known issues:

1. This feature is only available for Azure SQL DB General Purpose and Business Critical Tiers and not available for Hyperscale.
2. dm_database_backups DMV returns backup history for 35 days, regardless of backup retention days settings.
3. If a database is newly created, then the first one or two backups may not show up in the backup history view.
4. Currently this DMV lists only short-term retention (PITR) backups and does not list Long-term retention (LTR) backups

More information on Backup History can be found in Documentation: sys.dm_database_backups - SQL Server | Microsoft Docs