Plan backups and schema migrations

A backup only helps if you can restore it. Automate backups, keep copies away from the primary system, and test recovery on a schedule.

Use the backup or snapshot tools your database provides. Copying raw database files while writes are in flight is not automatically safe. You need a consistent snapshot. Encrypt backups, restrict who can read them, and keep at least one copy off the primary machine.

Two numbers 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 app stay down?

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

Test restoration into an isolated database. Check row counts, constraints, and a few important application flows. Record how long the restore took. A successful backup job only proves a file was created. It does not prove the business can recover.

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

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

1. Add the new column or table without breaking the old application.
2. Deploy code that works 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 forcing every application instance and every row to change at the same instant.

Some databases make schema changes transactional. Others auto-commit or rebuild and lock a table. Know the behavior before production. Make long backfills restartable. Separate application rollback from data rollback: deploying 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.

Try this on your own: 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.