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;