Skip to content
eSHARS Operations

Database Restore & Seed — v2 Runbooks + Port Self-Service

The current way to restore or seed an eSHARS database. This replaced the legacy per-copy runbook zoo on aa-eshars-prod — see Backup & Restore (legacy) only for the old estate. Facts current as of eshars-ops develop (July 2026).

The self-service path (start here)

You normally never touch the runbooks directly — two Port actions front them:

Restore Database (port-restore-db.yml)

Inputs: target environment, target database (default core), BACPAC source (latest = newest existing BACPAC · fresh = export a new one from prod first · specific = a named blob), scrub control (auto = environment default, raw only for regression/uat · force-on · force-off), and confirm unscrubbed — required to force scrubbing OFF on an environment that is normally scrubbed (PII exposure gate).

The workflow: validates inputs → derives tier/account/container routing → a concurrency guard on the importer → (if fresh) runs the producer on the prod account → resolves the blob → captures the pre-restore SKU → runs the import runbook → runs the post-patch runbook, polling each to completion with diagnostics on failure.

Seed Dev Data (port-seed-dev-data.yml)

Dev only. Inputs: seed source (synthetic — generated fake districts/students/clinicians/services/visits, no PII; or qa-slice — a dependency-complete slice of scrubbed QA data), volume, and force. qa-slice with force=true wipes dev’s FK-descendant closure and additionally requires confirm_wipe=true.

The machinery underneath

Tier automation accounts

AccountResource groupServes
aa-eshars-nonprod-cusrg-eshars-ops-nonprod-cusdev, qa, regression
aa-eshars-prod-cusrg-eshars-ops-prod-cusuat, training, prod

Created by workload-core (modules/automation), system-assigned managed identity only — Connect-AzAccount -Identity for the control plane, Entra access tokens for Azure SQL. No stored credentials anywhere.

Long-running work (the prod→BACPAC producer) runs on the tier ops VM’s Hybrid Runbook Worker group run-bacpacs-{tier} (registered by infrastructure/vms/ops); short jobs run in the Azure sandbox.

The five v2 runbooks (scripts/azure-runbooks/v2/)

One parameterized copy per runbook — no per-environment variants:

RunbookRoleTier
ar-eshars-db-restore-from-bacpac-genericPure importer — SqlPackage /Action:Import into the existing Terraform-owned DB. Scales to GP_S_Gen5_8 + disables auto-pause for the import; restores the captured SKU in finallyboth
ar-eshars-db-restore-post-patchPost-restore wiring: EDI settings + SQL patches fetched from blob + SKU restore; prod-only TMHP checkboth
ar-eshars-prod-to-bacpacProducer: copy prod → static scratch → optional PII scrub (-Mode scrub/raw) → referential-integrity gate (DBCC CHECKCONSTRAINTS) → export to db-storage-scrubbed/db-storage-raw → drop scratch; optional cross-tier blob copyprod
ar-eshars-db-seed-devSynthetic dev seeder; hard-refuses any non--dev databasenonprod
ar-eshars-db-seed-dev-from-qaQA-slice dev seeder; force wipe requires explicit confirmationnonprod

BACPAC handoff lives on the tier staging storage account (saeshars{tier}cus, containers db-storage-scrubbed / db-storage-raw — created by workload-core’s bacpac-handoff.tf). Post-restore SQL patches are the version-controlled files in database/post-restore/, synced to esharsdbstorage/post-restore-patches on push.

How the runbooks deploy

deploy-runbooks-v2.yml runs on push to develop touching scripts/azure-runbooks/v2/*.ps1 or database/post-restore/*.sql (or manual dispatch per tier). Each runbook carries a # deploy-tier: marker (nonprod / prod / both) controlling which account(s) receive it; runbooks with active jobs are skipped; and a prune step removes ar-eshars-* runbooks from an account when they are off-tier or deleted from git (matched by the managed_by=github-actions tags, so hand-created runbooks are never touched). The v1 legacy runbooks deploy separately via deploy-runbooks.yml (push to main, excludes v2/**).

Manual invocation (when Port is unavailable)

Start the runbook on the right tier account, e.g.:

Terminal window
az automation runbook start \
  --automation-account-name aa-eshars-nonprod-cus \
  --resource-group rg-eshars-ops-nonprod-cus \
  --name ar-eshars-db-restore-from-bacpac-generic \
  --parameters TargetEnvironment=qa TargetDatabase=core BacpacBlobName=<blob>

For the producer, add --run-on run-bacpacs-prod (it must run on the hybrid worker). Check the workflow files (port-restore-db.yml, port-seed-dev-data.yml) for the authoritative parameter set — they are the reference implementation of the full sequence (SKU capture, import, post-patch).