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:
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:
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:
More information on Backup History can be found in Documentation: sys.dm_database_backups - SQL Server | Microsoft Docs
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.