Backup History — Azure SQL Database / Fabric SQL Database
Use this skill when the user asks about backups, backup history, retention, or data recovery on Azure SQL Database or Fabric SQL Database.
Platform Guard: This skill targets Engine Edition 5 (Azure SQL DB) or 11 (Fabric SQL DB). For SQL Server (Engine Edition 1–4, 8), use backup-strategy-sqlserver instead.
Required tools
- A read-only SQL query tool connected to the target database. Prefer, in order: the SQL Tools MCP
server that ships with the
microsoft-sql-admin plugin (mssql_list_database_connections,
mssql_connect_database, then mssql_execute_query); any other SQL tool the agent host provides
for running T-SQL and reading result sets;
sqlcmd from a terminal. If the
SQL Tools MCP server is not connected or lists no connections, follow the sql-tools-mcp-setup
skill, which asks before installing or configuring anything. If no SQL tool can be made available,
give the user the T-SQL to run and ask for the results.
- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that
holds only
VIEW SERVER PERFORMANCE STATE (VIEW SERVER STATE before SQL Server 2022) or, on Azure
SQL Database, VIEW DATABASE PERFORMANCE STATE or ##MS_ServerStateReader##, plus any other read
permission these queries need. A tool is only as read-only as the login it connects with.
SQL Queries
GetLatestBackupHistory
SELECT TOP 100
backup_file_id,
backup_start_date,
backup_finish_date,
CASE
WHEN backup_type = 'D' THEN 'Full database backup'
WHEN backup_type = 'I' THEN 'Incremental or differential backup'
WHEN backup_type = 'L' THEN 'Log backup'
END AS backup_type,
CASE
WHEN in_retention = 0 THEN 'Out of retention'
WHEN in_retention = 1 THEN 'In retention'
END AS retention_status
FROM sys.dm_database_backups
ORDER BY backup_finish_date DESC
OPTION (MAXDOP 1);
The $BackupType parameter can be adjusted based on what the user wants:
- All types:
'D','I','L'
- Full only:
'D'
- Differential only:
'I'
- Log only:
'L'
Platform-Specific Guidance
Azure SQL Database
- The table
msdb.dbo.backupset is not available in Azure SQL Database. Use sys.dm_database_backups instead.
- Retention policy is based on the database's service tier. Guide the user to leverage the Azure Portal for retention configuration.
- For backup/restore and disaster recovery questions, focus on Point-In-Time Restore (PITR) and geo-replication capabilities.
Fabric SQL Database
- Default retention period is 7 days. The retention period cannot be changed.
- Only short-term retention backups are supported. No long-term retention backup support.
- Fabric SQL Database does not support the
BACKUP command. Use SqlPackage to export a .bacpac file instead.
Behavioral Guidance
- When asked about data loss or system crash recovery, focus on the product's HA and GeoDR functionality.
- When asked general questions about backup, mention the 3 types: full, differential, and transaction log.
- To get backup information, use
GetLatestBackupHistory — not msdb.dbo.backupset.
1---2name: backup-history-azuresql3description: "Use when the user asks for backup history, the latest backups or backup retention on Azure SQL Database or Fabric SQL Database. For SQL Server or Managed Instance, use `backup-strategy-sqlserver`."4user-invocable: true5metadata:6 source: https://skillmd.com/skills/microsoft/backup-history-azuresql7---89# Backup History — Azure SQL Database / Fabric SQL Database1011Use this skill when the user asks about backups, backup history, retention, or data recovery on **Azure SQL Database** or **Fabric SQL Database**.1213> **Platform Guard**: This skill targets Engine Edition 5 (Azure SQL DB) or 11 (Fabric SQL DB). For SQL Server (Engine Edition 1–4, 8), use `backup-strategy-sqlserver` instead.1415## Required tools1617- A read-only SQL query tool connected to the target database. Prefer, in order: the SQL Tools MCP18 server that ships with the `microsoft-sql-admin` plugin (`mssql_list_database_connections`,19 `mssql_connect_database`, then `mssql_execute_query`); any other SQL tool the agent host provides20 for running T-SQL and reading result sets;21 [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the22 SQL Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup`23 skill, which asks before installing or configuring anything. If no SQL tool can be made available,24 give the user the T-SQL to run and ask for the results.25- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that26 holds only `VIEW SERVER PERFORMANCE STATE` (`VIEW SERVER STATE` before SQL Server 2022) or, on Azure27 SQL Database, `VIEW DATABASE PERFORMANCE STATE` or `##MS_ServerStateReader##`, plus any other read28 permission these queries need. A tool is only as read-only as the login it connects with.2930## SQL Queries3132### GetLatestBackupHistory33```sql34SELECT TOP 10035 backup_file_id,36 backup_start_date,37 backup_finish_date,38 CASE39 WHEN backup_type = 'D' THEN 'Full database backup'40 WHEN backup_type = 'I' THEN 'Incremental or differential backup'41 WHEN backup_type = 'L' THEN 'Log backup'42 END AS backup_type,43 CASE44 WHEN in_retention = 0 THEN 'Out of retention'45 WHEN in_retention = 1 THEN 'In retention'46 END AS retention_status47FROM sys.dm_database_backups48ORDER BY backup_finish_date DESC49OPTION (MAXDOP 1);50```5152The `$BackupType` parameter can be adjusted based on what the user wants:53- All types: `'D','I','L'`54- Full only: `'D'`55- Differential only: `'I'`56- Log only: `'L'`5758## Platform-Specific Guidance5960### Azure SQL Database61- The table `msdb.dbo.backupset` is **not available** in Azure SQL Database. Use `sys.dm_database_backups` instead.62- Retention policy is based on the database's service tier. Guide the user to leverage the Azure Portal for retention configuration.63- For backup/restore and disaster recovery questions, focus on Point-In-Time Restore (PITR) and geo-replication capabilities.6465### Fabric SQL Database66- Default retention period is **7 days**. The retention period cannot be changed.67- Only short-term retention backups are supported. No long-term retention backup support.68- Fabric SQL Database does not support the `BACKUP` command. Use **SqlPackage** to export a `.bacpac` file instead.6970## Behavioral Guidance7172- When asked about data loss or system crash recovery, focus on the product's HA and GeoDR functionality.73- When asked general questions about backup, mention the 3 types: full, differential, and transaction log.74- To get backup information, use `GetLatestBackupHistory` — not `msdb.dbo.backupset`.