Restore Database

Restore: replaying a backup to rebuild the database

A restore applies a backup to bring data back. How depends on which kind of backup you took (see Backup), and the syntax is engine-specific — there is no single universal statement.

Correct, real commands

# Logical restore = re-run the exported SQL
mysql -u root -p mydb < mydb.sql                 # MySQL
psql mydb < mydb.sql                             # PostgreSQL (plain dump)
pg_restore -d mydb mydb.dump                     # PostgreSQL custom-format dump

# Physical restore (SQL Server — real T-SQL)
RESTORE DATABASE mydb FROM DISK = 'D:\mydb.bak' WITH REPLACE;

There is no RESTORE TABLE ... FROM DISK statement. To restore a single table you restore into a staging database (or load that table from a logical dump) and copy the rows over. Physical RESTORE DATABASE is SQL-Server-specific; Postgres/MySQL physical restores put the data files back and let the server replay the log.

Point-in-time recovery, traced

1. Restore the most recent base backup (gets you to, say, Sunday 02:00).
2. Replay the archived WAL/binlog forward, stopping at the target timestamp (e.g. just before the bad delete at Tue 14:32:05).
3. Bring the database online. You've recovered to the exact instant — without the mistake.

Recovery-target syntax (real engines)

# PostgreSQL recovery (modern: restore_command + recovery signal)
# postgresql.auto.conf / recovery settings concept:
#   restore_command = 'cp /wal_archive/%f %p'
#   recovery_target_time = '2024-03-12 14:32:00'
#   recovery_target_action = 'promote'
# Place backup data directory, configure restore_command + recovery_target_*,
# start Postgres; it replays WAL until the target then promotes.

# MySQL: restore base data, then
mysqlbinlog --stop-datetime="2024-03-12 14:32:00" binlog.000123 | mysql -u root -p mydb
# Or: mysqlbinlog --start-datetime=... --stop-datetime=... across binlog files in order

Single-table restore via staging (the real procedure)

1. Restore the full backup into a staging instance/database (mydb_restore), never over production first.
2. Extract the needed table: pg_dump -t public.orders mydb_restore > orders.sql or CREATE TABLE prod.orders_recovered AS SELECT * FROM staging.orders;
3. In production, in a transaction if FKs allow: rename/swap or INSERT … SELECT missing rows; re-check FK children.
4. Drop staging when validated.

Logical reload and FK order

Loading plain SQL dumps with FKs enabled fails if children load before parents. Mitigations:

• Load parents before children (dependency order), or
• Temporarily SET session_replication_role = replica (Postgres) / SET FOREIGN_KEY_CHECKS=0 (MySQL) during load, then re-enable and validate, or
• Use pg_restore which orders objects by dependency when possible.

RTO drill: rehearse before the outage

Step	What you measure	Pass criteria (example)
1. Provision staging host from runbook	clock start	documented; credentials work
2. Restore latest base backup	wall time	within RTO budget (e.g. < 30 min for 100GB physical)
3. Apply WAL/binlog to recovery target	replay time + lag	reaches target; data spot-checks OK
4. Application smoke test (read + write)	functional	login, critical path, no FK orphans
5. Record total RTO	sum	≤ agreed RTO; if not, change backup strategy (more frequent base, physical vs logical)

Run this quarterly. An untested backup is a hypothesis, not a recovery plan.

Pitfalls

• RTO (how fast you can be back) is dominated by restore + log-replay time — logical restores of a large DB can take hours; physical is faster. Measure it (see RPO/RTO).
• Restoring over a live DB usually needs exclusive access (WITH REPLACE / single-user / a fresh instance).
• Test restores on a separate host before you need them for real.
• FK order on logical reload and forgetting FOREIGN_KEY_CHECKS re-enable validation leave silent integrity holes.

Takeaways

• Logical restore = re-run the dump (mysql < / psql < / pg_restore); physical = put files back + replay log.
• RESTORE TABLE … FROM DISK isn't real; single-table restore goes via a staging DB.
• PITR = restore base, then replay WAL/binlog to the target instant (recovery_target_time / mysqlbinlog --stop-datetime).
• RTO is a rehearsed number, not a slide-deck promise — run the drill table above.

---

Re-authored for correctness and ops depth for this guide (the prior version used the non-existent RESTORE TABLE … FROM DISK; recovery-target syntax, staging single-table path, FK order, RTO drill added). Follows PostgreSQL/MySQL/SQL Server docs. See also: Backup, Transactions & ACID (WAL), RPO/RTO & disaster recovery.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Restore Database

Why this concept exists (judgment chain)

Restore is not one SQL keyword — it is replaying a backup class: logical (re-run dump) vs physical (files + WAL/binlog). PITR = base + replay to recovery_target_time. RTO is measured by drill, not promised on slides. Single-table recovery goes through staging, never mythical RESTORE TABLE.

Worked example with numbers or traced steps

Logical: mysql < mydb.sql; psql < dump; pg_restore -d mydb custom.dump
Physical SQL Server: RESTORE DATABASE … WITH REPLACE
PITR: restore Sunday 02:00 base; replay WAL to Tue 14:32:00 just before bad DELETE; promote.
Single table: restore to mydb_restore → pg_dump -t orders → load/swap into prod.
FK load order: parents first or session_replication_role/FK_CHECKS off then validate.
RTO drill: provision → base restore wall time → WAL apply → smoke test → total ≤ RTO.

When NOT to use / named alternative

Do not restore over production first — always staging validate. Prefer physical backups when RTO for large DBs makes logical restore too slow. Skip PITR tooling if RPO allows only daily full dumps and business accepts that loss (document it).

Failure / ops fingerprint

Untested backup: restore fails missing WAL segment. Logical dump FK order errors mid-load. Overwrite live DB without REPLACE/single-user. Ops: quarterly RTO game day; alert on backup age and failed restore tests; binlog/WAL archive retention ≥ PITR window.

Hostile-panel Q&As (model answers)

Q1. Logical vs physical restore?
Model answer: Logical re-executes SQL/objects; physical replaces data files and replays logs — usually faster at scale.

Q2. How do you restore one table safely?
Model answer: Full restore to staging, extract table, merge into prod with validation — not RESTORE TABLE FROM DISK.

Q3. What is PITR?
Model answer: Base backup + ordered WAL/binlog replay stopped at a timestamp/LSN before the incident.

I just read a lesson on **Restore Database** (Databases) and want to truly understand it. Explain Restore Database from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Restore Database** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Restore Database** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Restore Database** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.