Plan backups and schema migrations

A backup is useful only if you can restore it. Automate backups, keep copies away from the primary system, and test recovery.

Use the backup or snapshot tools designed for your database. A copy made while writes are in progress must still be a consistent snapshot; copying raw database files is not automatically safe. Encrypt backups, restrict access, keep at least one copy away from the primary system, and define a retention policy.

Two targets make the plan concrete:

• Recovery point objective (RPO): how much recent data can you afford to lose?
• Recovery time objective (RTO): how long can the application be unavailable?

Daily backups cannot meet a one-hour RPO. A large backup that takes eight hours to restore cannot meet a one-hour RTO.

Test restoration into an isolated database. Confirm row counts, constraints, and a few important application flows. Record how long the restore takes. A successful backup job proves that a file was created, not that the business can recover.

Schema migrations solve a related problem. Store each ordered migration in version control and apply the same reviewed sequence in development, staging, and production. Do not edit production tables by hand.

For risky changes, prefer an expand-and-contract rollout:

1. Add the new column or table without breaking the old application.
2. Deploy code that can work with both shapes and backfill existing data.
3. Verify the backfill and switch reads to the new shape.
4. Remove the old column in a later deployment.

This avoids requiring every application instance and every row to change at the same instant.

Some databases make many schema changes transactional; others auto-commit or rebuild/lock a table. Know the behavior before production. Make long backfills restartable, and separate application rollback from data rollback: deploying the old code may not reverse a destructive migration.

A backup taken before a migration is valuable, but it is not a rollback plan until you know it restores correctly and fits your RTO.

Your action: apply a small migration to a copy of the database, restore the latest backup into a second isolated database, and measure both operations. Verify the restored schema, row counts, constraints, and one critical application query.