Managed Databases
Intermediate 12 min read Managed Databases

Database Backups and Restore

Restorable copies and a tested restore path

Database backups combine automated snapshots, transaction-log PITR and physical/logical exports. Knowing exactly what to restore — a table, a database, a point-in-time — determines your RPO and recovery speed.

backupPITRsnapshotrestorebinlogdumptransaction log
Prerequisites: Backup and Archival
Before we start

What you will learn

  • Compose snapshot + transaction-log backups for PITR
  • Choose logical (dump) vs physical (file) backups
  • Plan and validate restores against RPO/RTO
In 60 seconds

A snapshot is crash-consistent point-in-time; transaction logs fill the gap granularly. Managed databases automate both into PITR (restore to any second). Dumps (pg_dump/mysqldump) back up logical data but restore slowly.

Introduction

What is this?

Physical: volume/instance snapshots + managed automated backups (AWS Backup/RDS, Azure restore points, Cloud SQL backups) with PITR via binary logs. Logical: pg_dump, mysqldump, SQL BACPAC, gcloud exports — portable, engine-aware, but slower and less granular. Restore targets: same instance (in-place), new instance (cautious), point-in-time (undo a bad migration/deletion). RPO/RTO decision point: dump daily vs snapshot hourly vs log-applied PITR.

Motivation

Why do you need it?

  • 'Oops, I dropped a table' is the #1 restore trigger — PITR saves the day.
  • Snapshot restores are minutes; a 5-day-old full dump 'restore' can be hours.
  • Logical dumps are needed for engine upgrades and cross-engine migration.
  • A tested restore = a real RPO/RTO number, not a promise.
Think about it

Mental model

Imagine

A daily photograph of your desk plus a per-minute list of what changed

Think of

Snapshot + transaction logs

The photo (snapshot) captures the state each morning; notes of every paper moved (transaction log) reconstruct how it looked at 3:07pm — that's point-in-time recovery.

Mechanics

How it works

  • Automated snapshots run on a schedule; each captures the whole store consistently.
  • Binary/transaction logs record every committed change; PITR replays them to an exact timestamp.
  • Restores: choose a snapshot → restore → optionally apply logs up to the target time.
  • Logical dumps (pg_dump) produce portable SQL/text; import as a fresh DB (slower, coherent).
  • Cross-region: copy backups/vaults for region-loss recovery.

PITR timeline.

Snap S0
binlogs
Crash
Restore S0
Apply logs
Hands-on

Examples

Point-in-time restore

Point-in-time restore
# RDS: create new DB restored to a specific time
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance app-db \
  --target-db-instance app-db-pitr \
  --restore-time 2026-09-22T11:59:00Z \
  --use-latest-restorable-time
# Postgres logical dump
pg_dump -h <endpoint> -U app dbname > full.sql
psql -h restored -U app dbname < full.sql

PITR for incidents, dumps for portability or engine/OS moves.

Real world

In production

  • A fintech runs Aurora PITR with 35-day retention: restoring a bad release is a 10-minute op.
  • Azure SQL keeps 7-day PITR plus long-term backups vaulted for annual audits.
  • Cloud SQL nightly export to GCS parquet feeds the warehouse without touching prod.
Guidelines

Best practices

  • Set retention from RPO/legal demands, and test a restore on the consumer side.
  • Prefer instant-snapshot restores for speed; dump for portability.
  • Rehearse 'drop the table' PITR in staging quarterly.
  • Keep backup/restore credentials scoped and audit restore provenance.
Watch out

Common mistakes

  • Restoring to the same instance in place instead of a new instance for a change.
  • Dumps with no binlog coverage → gaps beyond last full backup.
  • Backing up the DB but not the app config/migrations it needs to run.
  • Assuming 'restore point' and 'PITR' are the same granularity (they differ!).
Root cause analysis

Common issues

Error
PITR returns 'no restorable time' near crash
Root cause

Log retention shorter than the RPO needed

Fix

Lengthen binlog/backup retention to cover the longest plausible recovery window.

Error
Logical restore fails on foreign keys
Root cause

Independent-table import order

Fix

Use --data-only with constraints disabled, or the managed import/export path.

When things break

Debugging tips

  • Restore fails fast? Usually grants on the target instance — check DB user perms.
  • PITR low on time-range: verify transaction log retention equals retention.
  • Restore slow: choose file-by-file or parallel import for big dumps.
  • After PITR, quarantine the window: restore to a *new* instance and inspect before flipping.
Interviews

Interview checkpoint

Practice

Hands-on lab

Automate Backup and Practice a PITR Restore
30 min · intermediate
In progress
Objectives
  • Enable automated backups + PITR on a database
  • Delete a table and restore to a point in time
  • Verify rows match the restore manifest

Automate Backup and Practice a PITR Restore

30 min · intermediate

0/4 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/1
1

What makes point-in-time recovery possible beyond the last snapshot?

Go deeper

Official documentation

AWS — Backing up and restoring RDS
docs
Azure — Restore SQL DB
docs
GCP — Cloud SQL backups
docs