Backup Strategy Analysis — SQL Server
Use this skill when the user asks about database backup strategy, backup jobs, maintenance plans, or backup recommendations on SQL Server or Managed Instance.
Platform Guard: This skill targets Engine Edition 1–4 or 8. For Azure SQL DB (Engine Edition 5, 11), use backup-history-azuresql instead.
Scope
- Reviews or designs the backup strategy: recovery model,
msdb backup history, backup Agent jobs, maintenance plans, and the RPO the current strategy achieves.
- Also checks a recovery model and full, differential and log backup schedule that the user supplies against a target RPO (for example, log backups every 90 minutes against a 60-minute RPO).
Required tools
- A SQL query tool connected to the target server and 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.
Data Collection Steps
- Server and database name, current date — include the current date in the report header
- Default backup directory — run
SELECT SERVERPROPERTY('InstanceDefaultBackupPath') AS DefaultBackupDirectory; (SQL Server 2019+). If it returns NULL (older versions, or Managed Instance, which backs up to URL), ask the user for the backup directory or report it as unknown
- Available backups — generate query against
msdb.dbo.backupset
- Backup maintenance plans — execute
GetBackupMaintenancePlansForCurrentDatabase
- Backup agent jobs — execute
GetExistingBackupAgentJobsForCurrentDb
- Recovery model — execute
GetDatabaseRecoveryModel
SQL Queries
GetExistingBackupAgentJobsForCurrentDb
SELECT
j.job_id,
j.name AS JobName,
js.step_id,
js.step_name,
js.command
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js ON j.job_id = js.job_id
WHERE (js.command LIKE '%BACKUP DATABASE%' AND js.command LIKE '%' + DB_NAME() + '%')
OR (js.command LIKE '%BACKUP LOG%' AND js.command LIKE '%' + DB_NAME() + '%')
OPTION (MAXDOP 1);
GetDatabaseRecoveryModel
SELECT recovery_model_desc AS RecoveryModel
FROM sys.databases
WHERE name = db_name()
OPTION (MAXDOP 1);
GetBackupMaintenancePlansForCurrentDatabase
SELECT name, package_data
FROM (
SELECT
p.name,
p.id,
CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS package_data
FROM msdb.dbo.sysssispackages AS p
JOIN msdb.dbo.sysssispackagefolders AS pf ON p.folderid = pf.folderid
WHERE foldername = 'Maintenance Plans'
) AS tab
WHERE package_data LIKE '%Back Up Database Task%' AND package_data LIKE '%' + DB_NAME() + '%'
OPTION (MAXDOP 1);
Report Template
## Database Backup Strategy
Database: {{ current database name}}
Recovery Model: {{ current database's recovery model}}
Default Backup Directory: {{ server's current backup directory}}
Date: {{current date in YYYY-MM-DD format}}
### Available Backups
{{Include list of all available backups and their types (use words, not special codes)}}
### Backup Maintenance Plans
{{Include list of all backup maintenance plans related to backups for the current database}}
### Backup Agent Jobs
{{Include list of all backup agent jobs related to backups for the current database}}
### Backup Strategy Summary
{{Include: summary of the backup strategy for the current database and if possible the RPO the current strategy provides.}}
### Recommendations
{{Include: any recommendations for improving the backup strategy for the current database. Show any BACKUP or job-creation T-SQL as a script to review, with its impact; do not run it without the user's explicit confirmation.}}
Behavioral Guidance
Confirmation Gate for State-Changing Commands
- The data collection queries above are read-only.
BACKUP DATABASE, BACKUP LOG, ALTER DATABASE ... SET RECOVERY ..., and creating or changing
SQL Agent backup jobs or schedules (sp_add_job, sp_add_jobstep, sp_add_jobschedule,
sp_update_job, and similar), or maintenance plans, change server state. Present them as
recommendations or scripts only.
- Never run any of them without the user's explicit confirmation for that specific command. A
request to analyze or design a backup strategy is not that confirmation.
- Before asking, explain the impact and the rollback: a backup consumes I/O, CPU, and destination
space, and an ad hoc full or log backup changes the differential base or the log chain unless
COPY_ONLY is used; a new job runs on its schedule until it is disabled or deleted
(sp_update_job @enabled = 0 or sp_delete_job); a recovery model change affects log growth and
point-in-time restore until it is switched back and a new full backup is taken.
Creating Database Backups
If the user asks to create a database backup (the confirmation gate above applies):
- Gather first:
- Server's default backup location
- Database's current recovery model
- Date and time of last full backup
- Defaults:
- Use the current database name unless told otherwise
- Use the server's default backup location unless told otherwise
- Present the
BACKUP command with its target path, type, and options (for example
COPY_ONLY for an ad hoc backup that must not change the backup chain), explain the impact, and
run it only after the user explicitly confirms that command.
Determining Potential Data Loss
If the user asks about potential data loss from a restore:
- Verify server version — if Azure SQL DB, provide PITR guidance and stop.
- Gather latest backup information (LSN and date/time). Do NOT use
physical_device_name.
- Query active transaction log LSNs from
fn_dblog. Convert segmented hex LSNs to decimal for comparison.
- Get current recovery model.
- Estimate data loss — records in
fn_dblog with LSNs higher than latest backup LSN represent potential data loss.
Response requirements:
- Caution that the estimate is not a guarantee
- Organize into sections: Latest Backup Info, Active Transaction Log, Potential Data Loss Estimate
- If no backups exist, state clearly that no restore is possible
1---2name: backup-strategy-sqlserver3description: "Use when the user asks to review or design a SQL Server or Managed Instance backup strategy, or whether the recovery model and backup schedule meet an RPO. For Azure SQL Database, use `backup-history-azuresql`."4user-invocable: true5metadata:6 source: https://skillmd.com/skills/microsoft/backup-strategy-sqlserver7---89# Backup Strategy Analysis — SQL Server1011Use this skill when the user asks about database backup strategy, backup jobs, maintenance plans, or backup recommendations on **SQL Server** or **Managed Instance**.1213> **Platform Guard**: This skill targets Engine Edition 1–4 or 8. For Azure SQL DB (Engine Edition 5, 11), use `backup-history-azuresql` instead.1415## Scope1617- Reviews or designs the backup strategy: recovery model, `msdb` backup history, backup Agent jobs, maintenance plans, and the RPO the current strategy achieves.18- Also checks a recovery model and full, differential and log backup schedule that the user supplies against a target RPO (for example, log backups every 90 minutes against a 60-minute RPO).1920## Required tools2122- A SQL query tool connected to the target server and database. Prefer, in order: the SQL Tools MCP23 server that ships with the `microsoft-sql-admin` plugin (`mssql_list_database_connections`,24 `mssql_connect_database`, then `mssql_execute_query`); any other SQL tool the agent host provides25 for running T-SQL and reading result sets;26 [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the27 SQL Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup`28 skill, which asks before installing or configuring anything. If no SQL tool can be made available,29 give the user the T-SQL to run and ask for the results.3031## Data Collection Steps32331. **Server and database name, current date** — include the current date in the report header342. **Default backup directory** — run `SELECT SERVERPROPERTY('InstanceDefaultBackupPath') AS DefaultBackupDirectory;` (SQL Server 2019+). If it returns NULL (older versions, or Managed Instance, which backs up to URL), ask the user for the backup directory or report it as unknown353. **Available backups** — generate query against `msdb.dbo.backupset`364. **Backup maintenance plans** — execute `GetBackupMaintenancePlansForCurrentDatabase`375. **Backup agent jobs** — execute `GetExistingBackupAgentJobsForCurrentDb`386. **Recovery model** — execute `GetDatabaseRecoveryModel`3940## SQL Queries4142### GetExistingBackupAgentJobsForCurrentDb43```sql44SELECT45 j.job_id,46 j.name AS JobName,47 js.step_id,48 js.step_name,49 js.command50FROM msdb.dbo.sysjobs AS j51INNER JOIN msdb.dbo.sysjobsteps AS js ON j.job_id = js.job_id52WHERE (js.command LIKE '%BACKUP DATABASE%' AND js.command LIKE '%' + DB_NAME() + '%')53 OR (js.command LIKE '%BACKUP LOG%' AND js.command LIKE '%' + DB_NAME() + '%')54 OPTION (MAXDOP 1);55```5657### GetDatabaseRecoveryModel58```sql59SELECT recovery_model_desc AS RecoveryModel60FROM sys.databases61WHERE name = db_name()62OPTION (MAXDOP 1);63```6465### GetBackupMaintenancePlansForCurrentDatabase66```sql67SELECT name, package_data68FROM (69 SELECT70 p.name,71 p.id,72 CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS package_data73 FROM msdb.dbo.sysssispackages AS p74 JOIN msdb.dbo.sysssispackagefolders AS pf ON p.folderid = pf.folderid75 WHERE foldername = 'Maintenance Plans'76) AS tab77WHERE package_data LIKE '%Back Up Database Task%' AND package_data LIKE '%' + DB_NAME() + '%'78OPTION (MAXDOP 1);79```8081## Report Template8283```84## Database Backup Strategy85Database: {{ current database name}}86Recovery Model: {{ current database's recovery model}}87Default Backup Directory: {{ server's current backup directory}}88Date: {{current date in YYYY-MM-DD format}}8990### Available Backups91{{Include list of all available backups and their types (use words, not special codes)}}9293### Backup Maintenance Plans94{{Include list of all backup maintenance plans related to backups for the current database}}9596### Backup Agent Jobs97{{Include list of all backup agent jobs related to backups for the current database}}9899### Backup Strategy Summary100{{Include: summary of the backup strategy for the current database and if possible the RPO the current strategy provides.}}101102### Recommendations103{{Include: any recommendations for improving the backup strategy for the current database. Show any BACKUP or job-creation T-SQL as a script to review, with its impact; do not run it without the user's explicit confirmation.}}104```105106## Behavioral Guidance107108### Confirmation Gate for State-Changing Commands109110- The data collection queries above are read-only.111- `BACKUP DATABASE`, `BACKUP LOG`, `ALTER DATABASE ... SET RECOVERY ...`, and creating or changing112 SQL Agent backup jobs or schedules (`sp_add_job`, `sp_add_jobstep`, `sp_add_jobschedule`,113 `sp_update_job`, and similar), or maintenance plans, change server state. Present them as114 recommendations or scripts only.115- Never run any of them without the user's explicit confirmation for that specific command. A116 request to analyze or design a backup strategy is not that confirmation.117- Before asking, explain the impact and the rollback: a backup consumes I/O, CPU, and destination118 space, and an ad hoc full or log backup changes the differential base or the log chain unless119 `COPY_ONLY` is used; a new job runs on its schedule until it is disabled or deleted120 (`sp_update_job @enabled = 0` or `sp_delete_job`); a recovery model change affects log growth and121 point-in-time restore until it is switched back and a new full backup is taken.122123### Creating Database Backups124125If the user asks to **create** a database backup (the confirmation gate above applies):1261271. **Gather first:**128 - Server's default backup location129 - Database's current recovery model130 - Date and time of last full backup1312. **Defaults:**132 - Use the current database name unless told otherwise133 - Use the server's default backup location unless told otherwise1343. **Present the `BACKUP` command** with its target path, type, and options (for example135 `COPY_ONLY` for an ad hoc backup that must not change the backup chain), explain the impact, and136 run it only after the user explicitly confirms that command.137138### Determining Potential Data Loss139140If the user asks about **potential data loss** from a restore:1411421. Verify server version — if Azure SQL DB, provide PITR guidance and stop.1432. Gather latest backup information (LSN and date/time). Do NOT use `physical_device_name`.1443. Query active transaction log LSNs from `fn_dblog`. Convert segmented hex LSNs to decimal for comparison.1454. Get current recovery model.1465. Estimate data loss — records in `fn_dblog` with LSNs higher than latest backup LSN represent potential data loss.147148**Response requirements:**149- Caution that the estimate is not a guarantee150- Organize into sections: Latest Backup Info, Active Transaction Log, Potential Data Loss Estimate151- If no backups exist, state clearly that no restore is possible