Backing Up and Restoring Databases

Define the recovery requirement

Recovery point objective describes the maximum tolerable data loss measured in time. Recovery time objective describes how long restoration of the service may take. Different datasets can have different objectives. Order records may need continuous log backup, while a rebuildable search index may only need its source data and a tested reconstruction process.

Identify corruption, accidental deletion, failed migration, credential compromise, zone loss, regional loss, and provider-account loss as distinct scenarios. Replicas support availability but can copy destructive statements immediately. Snapshots, continuous recovery logs, logical exports, and application-level archives cover different failure and granularity needs. Use a layered strategy where consequence justifies it.

Create protected recovery points

Automate backups and monitor completion, duration, size, and expected coverage. Encrypt in transit and at rest, manage keys with a recovery plan, and restrict backup reading and deletion. Place critical copies in a separate account or vault boundary when the threat model requires it. Immutability or deletion locks need carefully governed emergency and retention processes.

Set retention for operational recovery, compliance, cost, and deletion obligations. Keep enough points to recover from corruption discovered late. Record engine version, extensions, parameters, encryption keys, users, and surrounding infrastructure needed to open the data. A database file without compatible software and credentials may be practically unusable.

Restore through an isolated workflow

Restore into a non-production network with access restricted to the recovery team. Do not overwrite the only live database during a test. Select the recovery point, provision compatible capacity, restore data, replay logs when needed, and capture every duration. Keep the restored system disconnected from outbound integrations that could send emails or trigger payments.

For point-in-time recovery, confirm timezone and transaction boundaries. For partial logical recovery, preserve relationships and audit requirements. Scan or investigate when compromise is suspected before reconnecting data. If the latest point contains the damaging change, move backward deliberately and quantify the accepted loss.

Validate data and service recovery

Check schema versions, row counts, constraints, checksums or sampled records, recent known transactions, and important aggregates. Then connect a safe application instance and run critical read and write journeys with integrations disabled or replaced. Confirm credentials, DNS, connection pools, migrations, and monitoring all work with the restored endpoint.

Record achieved recovery point, total recovery time, manual decisions, failed steps, and evidence. Update the runbook and fix backup gaps immediately. Schedule exercises at a cadence matched to consequence and after major engine, encryption, topology, or migration changes. Restore evidence should be reviewed by the data owner, not only the backup operator.

Test recovery after accidental deletion

A subscription database needs proof that operators can recover to within fifteen minutes without affecting production.

1. Choose a known timestamp in an isolated test scenario and record transactions immediately before and after it.
2. Restore the latest base backup plus transaction logs into a restricted recovery environment at the chosen point.
3. Validate schema, account counts, known boundary transactions, constraints, and a representative billing read with external calls disabled.
4. Measure data loss and elapsed time, delete the test environment through its approved cleanup, and revise the runbook from observed delays.

Database recovery evidence pack

Retain this pack for each protected database and each restore exercise.

• Data owner, critical datasets, recovery point objective, recovery time objective, and covered scenarios.
• Backup types, schedule, retention, vault or account, encryption keys, access, and deletion protection.
• Engine compatibility, dependencies, restore network, integrations disabled, and step-by-step operator path.
• Schema, integrity, row, transaction, application, security, and monitoring validation checks.
• Selected point, achieved loss, elapsed stages, defects, approvals, cleanup, and next exercise date.

Common mistakes

• Treating a read replica as a backup even though it immediately receives accidental deletions.
• Monitoring backup-job success but never opening a restored database with the application.
• Running a restore test where the recovered system can contact real payment or messaging integrations.

Try one

Sources

• AWS Backup documentationOfficial AWS overview of policy-based backup management, recovery points, and restore testing.
• AWS Well-Architected Reliability PillarOfficial AWS design principles and practices for reliable workloads and recovery planning.