sys.dm_database_backups

Applies to: Azure SQL Database

Returns information about backups of a databases in a Azure SQL Database server.

Note

sys.dm_database_backups DMV is currently in preview and is available for all Azure SQL Database service tiers except Hyperscale tier.

Column Name	Data Type	Description
backup_file_id	uniqueidentifier	ID of the generated backup file. Not null
database_guid	uniqueidentifier	Logical Database ID of the Azure SQL Database on which the operation is performed. Not Null.
physical_database_name	nvarchar(128)	Name of the Physical Azure SQL Database on which the operation is performed. Not Null
server_name	nvarchar(128)	Name of the Physical server on which the Azure SQL Database which is being backed up is present. Not Null.
backup_start_date	datetime2(7)	Timestamp when the Backup operation started. Not Null.
backup_finish_date	datetime2(7)	Timestamp when the Backup operation finished. Not Null.
backup_type	char(1)	Type of Backup D = Full Database Backup I = Incremental or Differential Backup L = Log Backup. Not Null.
in_retention	bit	Backup Retention Status. Tells whether backup is within retention period 1 = In Retention 0 = Out of Retention. Null.

Permissions

Requires VIEW DATABASE STATE permission on the database.

Remarks

Backups retained and shown in Backup history view depend on configured backup retention. Some backups older than the retention period, in_retention=0, are also shown in dm_database_backups view. They're needed to do point in restore within the configured retention.

Example

Show list of all active backups for the current database ordered by backup finish date.

SELECT * 
FROM sys.dm_database_backups     
ORDER BY backup_finish_date DESC;  

You can get a friendlier resultset by joining to sys.databases and using a CASE statement. Run this query in the master database to get backup history for all databases in the Azure SQL Database server.

SELECT db.name
    , backup_start_date
    , backup_finish_date
    , CASE backup_type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
    END AS BackupType
    , CASE in_retention
        WHEN 1 THEN 'In Retention'
        WHEN 0 THEN 'Out of Retention'
        END AS is_Bakcup_Available
FROM sys.dm_database_backups AS ddb
INNER JOIN sys.databases AS db
    ON ddb.physical_database_name = db.physical_database_name
ORDER BY backup_start_date DESC;

Run the below query in the user database context to get backup history for a single database.

SELECT backup_start_date
    , backup_finish_date
    , CASE backup_type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        END AS BackupType
    , CASE in_retention
        WHEN 1 THEN 'In Retention'
    WHEN 0 THEN 'Out of Retention'
    END AS is_Bakcup_Available
FROM sys.dm_database_backups AS ddb
INNER JOIN sys.databases AS db
    ON ddb.physical_database_name = db.physical_database_name
ORDER BY backup_start_date DESC;