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;
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for