Backup & Restore (legacy)

Legacy — applies to the pre-rebuild estate only

This page documents the legacy restore procedures (portal imports and the per-copy runbooks on aa-eshars-prod). For the rebuilt tier-based estate, use the v2 self-service path instead: Database Restore & Seed — v2 Runbooks + Port Self-Service. The v2 path replaces the manual EDI-settings capture, the one-time SQL scripts, and the pricing-tier juggling below with parameterized, tier-aware runbooks.

Procedures for database backup and restore operations on the legacy estate.

Backup Overview

Automatic Backups

Azure SQL provides automatic backups:

Backup Type	Frequency	Retention
Full	Weekly	7-35 days (configurable)
Differential	Every 12-24 hours	Same as full
Transaction Log	Every 5-10 minutes	Same as full

Manual Backups

For migrations or major changes, create manual backups.

Creating Manual Backup

Via Azure Portal

1. Go to Azure Portal → SQL Database
2. Select database (ESHARS)
3. Click “Export”
4. Configure:
   • Storage account
   • Container
   • File name: ESHARS_YYYYMMDD_manual.bacpac
   • Authentication
5. Click “OK”
6. Monitor export progress

Via Command Line

# Using SqlPackage
SqlPackage.exe /Action:Export \
  /SourceConnectionString:"Server=tcp:server.database.windows.net;Database=ESHARS;..." \
  /TargetFile:"ESHARS_20240115.bacpac"

Point-in-Time Restore

When to Use

• Accidental data deletion
• Data corruption
• Testing with production data snapshot

Procedure

1. Go to Azure Portal → SQL Database
2. Click “Restore”
3. Select “Point-in-time”
4. Choose restore point (date/time)
5. Enter new database name: ESHARS_Restored_YYYYMMDD
6. Click “Review + Create”
7. Wait for restore to complete (may take hours for large DBs)

Post-Restore

1. Verify restored database
2. If replacing production:
   • Rename current: ESHARS → ESHARS_Old_YYYYMMDD
   • Rename restored: ESHARS_Restored → ESHARS
   • Update connection strings if needed
   • Restart application

Connection Strings

If database name changes, update App Service configuration.

Restore from BACPAC

Via Azure Portal

1. Go to Azure Portal → SQL Server
2. Click “Import database”
3. Select storage account and BACPAC file
4. Configure:
   • Database name
   • Pricing tier
   • Collation
5. Click “OK”

Via Command Line

# Using SqlPackage
SqlPackage.exe /Action:Import \
  /TargetConnectionString:"Server=tcp:server.database.windows.net;..." \
  /SourceFile:"ESHARS_20240115.bacpac"

Disaster Recovery

Geo-Replication

For critical databases, enable geo-replication:

1. Go to SQL Database → Geo-Replication
2. Select secondary region
3. Configure (readable secondary if needed)
4. Failover is automatic or manual

Failover Procedure

1. Assess primary region status
2. If unrecoverable:
   • Go to secondary database
   • Click “Failover”
   • Confirm
3. Update DNS/connection strings if needed
4. Notify stakeholders

Production Database Restore to Other Environments

Procedure for restoring the production database to Regression, QA, UAT, or Training environments.

Pre-Restore: Capture Environment Settings

Before starting, capture the current settings of the target environment — these will be overwritten by production values.

1. EDI Settings — Log into the target environment (e.g., Regression), go to Location → Texas State → Data Mgmt → State Data → System Settings → EDI Settings. Screenshot all field values.
2. Pricing Tier — In Azure Portal, note the current pricing tier/DTU settings for the target database.
3. Notify Team — Inform the team to stay out of the environment being restored.

Run the Restore

Target Environment	Azure Automation Runbook
Regression	ar-eshars-regressiondb-copy
UAT	ar-eshars-uatdb-copy
QA	aa-eshars-prod-to-qa-scrub

1. Go to Azure Portal → Automation Accounts → aa-eshars-prod
2. Navigate to Runbooks → select the appropriate runbook
3. Click Start → OK
4. Wait for completion (~15 minutes)

Post-Restore Steps

1. Restore EDI Settings — Log back into the restored environment and re-enter the EDI Settings from your screenshots. Use the full FTP paths (not just the environment name portion):

   • PROD: PROD-4deefb1ce5d3
   • QA: QA-cbe96e528e40
   • UAT: UAT-c5718067b559
   • REGRESSION: REGRESSION-14d39416712d

2. Run District Data Restore Scripts — From HISD/Eshars.DBUpgrader/SQL/APP/OneTimeRun:

   • Run 1.3.63.2-DFP-SuccessED-HM-51756.SQL — execute the DELETE on line 1 first, then set the environment value on line 2
   • Run 1.3.78.0-SuccessED.sql — execute the DELETE on line 4 first, then set the environment on line 5
   • Run 1.3.69.0-Logfiles-HM-5558.sql — execute the DELETE on line 4 first, then set the environment on line 5

3. Reset Pricing Tier — In Azure Portal, find the restored database → Pricing Tier → adjust DTUs back to the pre-restore setting

4. Verify Database Sizes Match — Confirm the restored database is the same size as production

5. Verify TMHP Service — Ensure the TMHP service is running in the restored environment

QA-Specific: Rebuild Replication

When restoring PROD to QA, the QA report replication (db-eshars-nonprod-west\bd-eshars-qa) is removed. You must rebuild this replication after the restore.

Regression-Specific: Reset Test Passwords

After restoring to Regression, reset the passwords for DsTEST@test.com and DsTest1@test.com (used for weekly RnS file runs). Copy the PasswordHash from production and update it in the restored environment.

Verification

After any restore:

1. Connect to restored database
2. Verify table counts
3. Check recent data
4. Test application connectivity
5. Run smoke tests

Backup Schedule

Database	Manual Backup	Retention
ESHARS	Before major releases	90 days
ESHARSAUDIT	Monthly	1 year
ESHARSLOGS	Quarterly	6 months

---

Last reviewed: [Date]