sys.dm_database_backups

Applies to: Azure SQL Database

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

Note

The 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.
logical_database_id	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.
logical_server_name	nvarchar(128)	Name of the logical server on which the Azure SQL Database that is being backed up is present. Not null.
logical_database_name	nvarchar(128)	User-created name of the database on which the operation is performed. 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. Not null. D = Full database backup I = Incremental or differential backup L = Log backup.
in_retention	bit	Backup retention status. Tells whether backup is within retention period. Null. 1 = In retention 0 = Out of retention.

Permissions

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra ID admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role, is required.

Remarks

Backups retained and shown in the backup history view depend on configured backup retention. Some backups older than the retention period (in_retention = 0) are also shown in the sys.dm_database_backups view. They're needed to do point in time 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;

To get a user friendly list of backups for a database, please run:

SELECT backup_file_id, 
    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 IsBackupAvailable
FROM sys.dm_database_backups
ORDER BY backup_start_date DESC;