Backup Coverage Heat Map

Status: Reviewed (spike v1)
Path: Spike #2 (after #1 Inventory MVP; next #3 Agent fail board → #7 Alert ack)
Stack: PowerShell → SQL/stored procs → Blazor Server
Builds on: SQL-02 Backup & restore readiness; requires dbo.SqlInstance from SQL-SPIKE-01
Goal: For each inventoried instance’s user DBs, show last full/diff/log + RAG vs class intervals on a Blazor heat map.

In scope (MVP)

• Collect last backup times from msdb per DB on each active SqlInstance
• Upsert DB + backup status; compute FullOverdue / LogOverdue / NoFullEver
• Simple DB class table (A/B/C) with interval hours (scaffolding — Rick tunes later)
• Blazor heat map/grid: instance × database × RAG
• Failures queue = Red rows only

Out of scope (later)

• File-on-share probe, restore-test automation, copy_only policy nuance UI
• Auto-creating Agent backup jobs
• Alert evaluate (spike #7)

A. Database objects (extend inventory DB)

CREATE TABLE dbo.DbBackupClass (
  BackupClassCode CHAR(1) NOT NULL PRIMARY KEY, -- A/B/C
  DisplayName NVARCHAR(64) NOT NULL,
  FullIntervalHours INT NOT NULL,
  DiffIntervalHours INT NULL,          -- NULL = optional
  LogIntervalHours INT NULL,           -- NULL = N/A (e.g. SIMPLE or class C)
  RpoMinutes INT NULL
);

-- Seed (edit with Rick later)
-- A: Full 24h, Diff 6h, Log 0.25h (15m)
-- B: Full 24h, Diff 12h, Log 0.5h
-- C: Full 168h, Diff NULL, Log NULL

CREATE TABLE dbo.SqlDatabase (
  SqlDatabaseId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  DatabaseName NVARCHAR(128) NOT NULL,
  RecoveryModel NVARCHAR(60) NULL,     -- FULL/BULK_LOGGED/SIMPLE
  BackupClassCode CHAR(1) NOT NULL CONSTRAINT DF_SqlDatabase_Class DEFAULT ('B')
    REFERENCES dbo.DbBackupClass(BackupClassCode),
  IsUserDatabase BIT NOT NULL CONSTRAINT DF_SqlDatabase_User DEFAULT (1),
  LastSeenAt DATETIME2(0) NOT NULL,
  CONSTRAINT UQ_SqlDatabase UNIQUE (SqlInstanceId, DatabaseName)
);

CREATE TABLE dbo.SqlDatabaseBackupStatus (
  SqlDatabaseId INT NOT NULL PRIMARY KEY REFERENCES dbo.SqlDatabase(SqlDatabaseId),
  LastFullAt DATETIME2(0) NULL,
  LastDiffAt DATETIME2(0) NULL,
  LastLogAt DATETIME2(0) NULL,
  LastFullIsCopyOnly BIT NULL,
  RagStatus CHAR(1) NOT NULL,          -- G/Y/R
  FullOverdue BIT NOT NULL CONSTRAINT DF_Bak_FullOverdue DEFAULT (0),
  LogOverdue BIT NOT NULL CONSTRAINT DF_Bak_LogOverdue DEFAULT (0),
  NoFullEver BIT NOT NULL CONSTRAINT DF_Bak_NoFullEver DEFAULT (0),
  EvaluatedAt DATETIME2(0) NOT NULL
);

Scoring (v1):

• Ignore copy_only fulls for “policy full” if LastFullIsCopyOnly = 1 and no non-copy full (treat as NoFullEver/overdue)
• NoFullEver → R
• Else if full age > 2× FullIntervalHours → R; > FullIntervalHours → Y
• If recovery FULL/BULK_LOGGED and LogIntervalHours NOT NULL: log age > 2× → R; > 1× → Y (at least)
• Else G
• Worst flag wins for RagStatus

Procs:

-- Upsert DB row + touch LastSeenAt
CREATE PROC dbo.usp_SqlDatabase_Upsert
  @SqlInstanceId INT, @DatabaseName NVARCHAR(128), @RecoveryModel NVARCHAR(60),
  @IsUserDatabase BIT = 1 AS ...

-- Apply backup times + compute flags using DbBackupClass
CREATE PROC dbo.usp_SqlDatabaseBackupStatus_Apply
  @SqlDatabaseId INT,
  @LastFullAt DATETIME2(0) = NULL,
  @LastDiffAt DATETIME2(0) = NULL,
  @LastLogAt DATETIME2(0) = NULL,
  @LastFullIsCopyOnly BIT = NULL
AS ...

CREATE PROC dbo.usp_BackupCoverage_GetHeatmap
  @RedOnly BIT = 0,
  @SqlInstanceId INT = NULL
AS
BEGIN
  SELECT i.HostName, i.InstanceName, d.DatabaseName, d.RecoveryModel, d.BackupClassCode,
         s.LastFullAt, s.LastDiffAt, s.LastLogAt, s.RagStatus,
         s.FullOverdue, s.LogOverdue, s.NoFullEver, s.EvaluatedAt,
         d.SqlDatabaseId, i.SqlInstanceId
  FROM dbo.SqlDatabaseBackupStatus s
  JOIN dbo.SqlDatabase d ON d.SqlDatabaseId = s.SqlDatabaseId
  JOIN dbo.SqlInstance i ON i.SqlInstanceId = d.SqlInstanceId
  WHERE i.IsActive = 1
    AND (@SqlInstanceId IS NULL OR i.SqlInstanceId = @SqlInstanceId)
    AND (@RedOnly = 0 OR s.RagStatus = 'R')
  ORDER BY CASE s.RagStatus WHEN 'R' THEN 0 WHEN 'Y' THEN 1 ELSE 2 END, i.HostName, d.DatabaseName;
END;

CREATE PROC dbo.usp_BackupCoverage_GetFailures AS
  EXEC dbo.usp_BackupCoverage_GetHeatmap @RedOnly = 1;

Reuse usp_CollectRun_Start / _Complete from spike #1 (CollectorName = N'BackupCoverage').

B. PowerShell collector (shape)

Inputs: -InventorySqlConnectionString (warehouse); uses active instances from SqlInstance (query or small read proc later — MVP: SELECT SqlInstanceId, HostName, InstanceName FROM dbo.SqlInstance WHERE IsActive = 1).

Per instance (try/catch; continue):

1. Connect to target HOST[\INSTANCE]
2. Run collect SQL (concept):

SELECT
  d.name AS DatabaseName,
  d.recovery_model_desc AS RecoveryModel,
  CASE WHEN d.database_id > 4 THEN 1 ELSE 0 END AS IsUserDatabase,
  (SELECT MAX(backup_finish_date) FROM msdb.dbo.backupset bs
    WHERE bs.database_name = d.name AND bs.type = 'D' AND ISNULL(bs.is_copy_only,0) = 0) AS LastFullAt,
  (SELECT MAX(backup_finish_date) FROM msdb.dbo.backupset bs
    WHERE bs.database_name = d.name AND bs.type = 'D' AND bs.is_copy_only = 1) AS LastFullCopyOnlyAt,
  (SELECT MAX(backup_finish_date) FROM msdb.dbo.backupset bs
    WHERE bs.database_name = d.name AND bs.type = 'I') AS LastDiffAt,
  (SELECT MAX(backup_finish_date) FROM msdb.dbo.backupset bs
    WHERE bs.database_name = d.name AND bs.type = 'L') AS LastLogAt
FROM sys.databases d
WHERE d.state = 0 AND d.name NOT IN (N'tempdb'); -- MVP: include system optional via switch

3. Prefer non-copy full for LastFullAt; if only copy_only exists, pass that + @LastFullIsCopyOnly = 1
4. usp_SqlDatabase_Upsert then usp_SqlDatabaseBackupStatus_Apply
5. Complete CollectRun

Script: Collect-SqlBackupCoverage.ps1
Rights on targets: read msdb backup tables + sys.databases (custom role preferred). Warehouse writer = execute upsert/apply only.

C. Blazor Server (MVP UI)

Page: /backups or /sql/backups

Service: procs only — usp_BackupCoverage_GetHeatmap, GetFailures

Grid: Host | Instance | Database | Class | Recovery | Last Full | Last Log | RAG badge | flags

Filters: Red only (default off or on — pick Red default off, toggle on); instance dropdown; search name

Color: R red / Y amber / G green — accessible text letter still shown

Detail (optional MVP): same row fields; link text to SQL-02 runbook URL in SharePoint

App pool: read procs only

D. Acceptance checks

• [ ] Seed DbBackupClass A/B/C
• [ ] Collector against instance with known backup history → status rows
• [ ] DB with no full → NoFullEver + R
• [ ] FULL recovery + old log → LogOverdue + R/Y per math
• [ ] Heat map lists rows; Red-only filter works
• [ ] Second collect updates times without duplicate SqlDatabase keys
• [ ] System DBs excluded or flagged per switch (document choice)

E. Repo layout

/sql/backup/001_tables.sql
/sql/backup/002_procs.sql
/sql/backup/003_seed_classes.sql
/ps/Backup/Collect-SqlBackupCoverage.ps1
/src/.../Pages/Backups/Index.razor
/src/.../Services/BackupCoverageService.cs

Next spike (wait for go-ahead)

#3 Agent fail board — surfaces backup job failures that cause Red cells (SQL-04).

SQL Dude — SQL-SPIKE-02 Backup Heat Map v1