Backup & Restore Readiness

Status: Reviewed (deep-dive v1)
Stack: PowerShell collect → SQL store/procs → Blazor Server UI
Depends on: SQL-01 Inventory (population list = managed instances)
Goal: Know every database is backed up to policy, restorable, and visible when it isn’t.

Why this second

Inventory tells you what exists. Backup readiness tells you whether you can survive losing it. Fix coverage gaps before optimizing indexes.

What “good” looks like

Each user database has a class (e.g. Tier A/B/C) with RPO/RTO targets
Full / Diff / Log backup cadence matches class (or explicit exception)
Last successful full + last log (FULL recovery) known and fresh
Backup checksum / VERIFYONLY cadence defined (at least sample + critical DBs)
Failures and gaps surface in Blazor within one collect cycle
Secrets (backup share credentials) never shown in UI

Database classes (starting defaults — confirm with Rick)

Class	Example	RPO (start)	Full	Diff	Log
A — Critical	OLTP prod, auth, billing	≤ 15 min	Daily	Every 4–6 h	Every 5–15 min
B — Standard	Internal apps	≤ 1–4 h	Daily	Daily / every 12 h	Every 15–30 min (FULL)
C — Low	Dev/test, scratch	≤ 24 h	Daily or weekly	Optional	SIMPLE often OK

SIMPLE recovery → no log backups required; still need full (+ optional diff).
Do not invent final estate policy — use these as scaffolding until Rick sets real targets.

Checklist — collect (PowerShell)

Scope = instances from inventory (LastSeenAt fresh). Prefer T-SQL via Invoke-Sqlcmd / SMO against msdb + sys.databases.

[ ] Per database: name, state, recovery_model, create_date, is_read_only, is_in_standby
[ ] Last full / diff / log backup finish time, backup size, device/path (from msdb.dbo.backupset / backupmediafamily)
[ ] Is copy_only? (don’t count copy_only as meeting chain policy unless policy says so)
[ ] Recent backup failures: failed Agent jobs named like backup; SQL Agent history; optional Windows event scan
[ ] File presence probe (optional): does last backup file exist on share / URL? (size & timestamp)
[ ] Emit payload with CollectedAt, instance key (host + instance), per-db rows

Keep it read-only on prod. No BACKUP DATABASE from the collector.

Checklist — store (SQL + stored procedures)

Table	Purpose
`dbo.DbBackupClass`	Class name, RPO minutes, full/diff/log interval targets
`dbo.SqlDatabase`	DB identity tied to `SqlInstance`
`dbo.SqlDatabaseBackupStatus`	Latest backup timestamps + computed gap flags
`dbo.SqlDatabaseBackupHistory`	Optional trim of recent backupset facts
`dbo.BackupPolicyException`	Approved gaps (who/when/why)

Procs:

[ ] usp_SqlDatabase_Upsert — merge DBs from collect
[ ] usp_SqlDatabaseBackupStatus_Apply — upsert latest times; set FullOverdue, LogOverdue, NoFullEver, RecoveryMismatch
[ ] usp_BackupCoverage_GetHeatmap — Blazor: instance × DB with RAG status
[ ] usp_BackupCoverage_GetFailures — queue of overdue / failed
[ ] usp_BackupPolicyException_List — suppress known exceptions in UI (still visible under Exceptions)

Scoring idea (simple):

Green: within class intervals
Yellow: within 2× interval
Red: beyond 2×, never backed up, or FULL recovery with no log backup ever

Checklist — present (Blazor Server)

[ ] Heat map / grid: instance, database, class, last full, last log, RAG
[ ] Filters: Red only, FULL with no log, missing from inventory policy, exceptions
[ ] Detail: backup history snippet, device paths, Agent job names (if known)
[ ] Actions: link to this runbook; flag “needs restore test”; do not trigger backups from UI v1
[ ] Badge count on home: critical overdue count

Drive grids from procs only.

Restore verification (ops, not just backup)

Backing up ≠ restorable. Lean program:

[ ] Tier A: restore-test at least monthly (file → nonprod instance → RESTORE VERIFYONLY or full restore + smoke query)
[ ] Tier B: quarterly sample
[ ] Log RestoreTestRun (db, as-of time, result, operator, notes)
[ ] Blazor: last restore-test date column; stale test = yellow

PS can orchestrate restore to a dedicated verify instance; SQL stores results; UI shows freshness.

Operator runbook (short)

Open Backup coverage → filter Red
Confirm recovery model vs expected class
Check SQL Agent backup jobs + msdb history on that instance
If job OK but file missing: storage/share permissions / retention wiping too early
If FULL and log overdue: check log backup job, disk full, AG secondary backup preference
After fix: wait for next collect or request recollect; confirm Green
For incidents needing restore: use last good full + diff + log chain; document in change notes

Common failure patterns (cheat sheet)

Symptom	Likely cause
Never a full	Job missing / filtered DB / new DB not in maintenance plan
Log overdue, FULL	Log job stopped; AG backup on wrong replica; log VLFs/disk full
Only copy_only fulls	Ad-hoc backups; scheduled job not running true fulls
Green in msdb, file gone	Retention script; wrong path; share moved
Diff without base full	Broken chain after file restore / recovery model flip

Permissions notes

Collector: VIEW ANY DEFINITION / read msdb backup tables; public-ish minimum via custom role preferred
Writer login: upsert only on inventory/backup tables
Blazor: exec read procs only
Restore-test account: separate, limited to verify instance

Done definition for this topic

[ ] Coverage rows for DBs on inventoried instances
[ ] Heat map shows RAG using class intervals
[ ] At least one overdue path tested end-to-end (flag → fix → clear)
[ ] This note linked from the Knowledge Base list item

Next after this

Index & statistics maintenance — safer once backup coverage is trusted.

SQL Dude — SQL-02 Backup & Restore Readiness v1