title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | |||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.dm_database_backups |
Returns information about backups of a database in an Azure SQL Database server. |
SudhirRaparla |
nvraparl |
randolphwest |
09/28/2023 |
azure-sql-database |
reference |
|
|
|
=azuresqldb-current |
[!INCLUDE Azure SQL Database]
Returns information about backups of a database in an [!INCLUDE ssazure-sqldb] 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 [!INCLUDE ssazure-sqldb] on which the operation is performed. Not null. |
physical_database_name |
nvarchar(128) | Name of the physical [!INCLUDE ssazure-sqldb] on which the operation is performed. Not null. |
logical_server_name |
nvarchar(128) | Name of the logical server on which the [!INCLUDE ssazure-sqldb] 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. |
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.
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.
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;