Backups, restores, and migrations

A fast query does not help if yesterday's catalog is gone. This lesson treats recovery as a skill: copy schema and rows, prove you can restore them, then change schema on purpose with a version number, a backup, and a rehearsal. Migrations are just writes that must be reversible or at least recoverable.

Recovery is a restore you already practiced

People say “we have backups” meaning a file landed in object storage. Recovery means: last night's shop opens today with the same SKUs and stock. If you have never loaded that file into an empty database, you have a hope, not a backup.

Do the drill when nobody is waiting. Compare COUNT(*), open one known row (Ada's notebook), run PRAGMA integrity_check or PostgreSQL pg_restore --list. Time how long restore takes. That time is your real RTO (how long until you are open again). How much data you can afford to lose is RPO. This catalog will not measure clocks; it will measure whether the rows come back.

A logical backup is schema plus rows

Physical: copy the SQLite file (or VACUUM INTO 'backup.db' on a quiet database), or copy PostgreSQL's data directory only with the matching tool. Logical: SQL that recreates objects and INSERTs. PostgreSQL pg_dump / pg_restore, MySQL mysqldump, SQLite .dump in the CLI.

Here we copy sqlite_master.sql so we remember CREATE, snapshot rows with CREATE TABLE backup_products AS SELECT *, destroy the live table, recreate, and insert from the snapshot. The live shop returns. That is restore.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 12),
  ('PEN-2', 'Gel pen', 40);

SELECT name, type, sql
FROM sqlite_master
WHERE name = 'products';

CREATE TABLE backup_products AS
SELECT * FROM products;

DROP TABLE products;

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products
SELECT * FROM backup_products;

SELECT sku, name, stock
FROM products
ORDER BY sku;

Edit the query, predict the rows it will return, then run it.

user_version is a tiny migrations table

Applications forget which ALTER already ran. SQLite stores an integer you control: PRAGMA user_version. Read it at startup. If it is 1, run the script that makes it 2, then set 2. Never run step 2 twice as a blind ADD COLUMN.

PostgreSQL and MySQL usually keep a schema_migrations table of filenames. Same contract: each change has a name, applied once, recorded after success. Failed scripts must not record success. Last lesson's transactions belong around that record.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 12),
  ('PEN-2', 'Gel pen', 40);

PRAGMA user_version;

PRAGMA user_version = 1;

PRAGMA user_version;

SELECT COUNT(*) AS product_rows
FROM products;

Edit the query, predict the rows it will return, then run it.

Add a column, backfill, then bump the version

The safest production change is expand: add a column the old app can ignore. SQLite ALTER TABLE ... ADD COLUMN accepts a NULL column (or a constant DEFAULT). Backfill with UPDATE. Then ship the app that writes color. Then, much later, you may tighten NOT NULL with a rebuild.

Do it in BEGIN … COMMIT so a failed backfill does not leave a new column and a stale version. If ADD COLUMN cannot join a transaction on your SQLite build, still backfill and version in one deploy script you test on a copy first.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 12),
  ('PEN-2', 'Gel pen', 40);

PRAGMA user_version = 1;

BEGIN;

ALTER TABLE products
ADD COLUMN color TEXT;

UPDATE products
SET color = 'navy'
WHERE sku = 'NB-1';

UPDATE products
SET color = 'black'
WHERE sku = 'PEN-2';

PRAGMA user_version = 2;

COMMIT;

PRAGMA table_info(products);

SELECT sku, name, color, stock
FROM products
ORDER BY sku;

Edit the query, predict the rows it will return, then run it.

Rebuild the table when ALTER is not enough

SQLite cannot always rename a column or change a type in place. The portable move: create products_new with the shape you want, INSERT ... SELECT mapping name to title, drop the old table, ALTER TABLE ... RENAME TO products. Rebuild indexes and triggers you dropped with the old table (this catalog's PRIMARY KEY comes back on the new CREATE).

During a rolling deploy, keep writing both names if old servers still INSERT name (expand/contract). Drop the old column only after every writer is new. PostgreSQL can RENAME COLUMN more often; the rehearsal rule does not change.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 12),
  ('PEN-2', 'Gel pen', 40);

ALTER TABLE products
ADD COLUMN color TEXT;

UPDATE products SET color = 'navy' WHERE sku = 'NB-1';
UPDATE products SET color = 'black' WHERE sku = 'PEN-2';

BEGIN;

CREATE TABLE products_new (
  sku TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  stock INTEGER NOT NULL,
  color TEXT
);

INSERT INTO products_new (sku, title, stock, color)
SELECT sku, name, stock, color
FROM products;

DROP TABLE products;

ALTER TABLE products_new RENAME TO products;

COMMIT;

SELECT sku, title, color, stock
FROM products
ORDER BY sku;

Edit the query, predict the rows it will return, then run it.

Restore parents before children

Foreign keys are restore order. If you load signups while workshops is empty and PRAGMA foreign_keys = ON, INSERT fails (and this runner would hide earlier results). Safer teaching: turn checks off, load children first, then PRAGMA foreign_key_check. You should see orphan workshop_id rows. Load parents. Check again—empty.

The correct script never needs that scare: insert workshops, then signups, checks ON the whole time. Same idea as dump order in pg_dump.

PRAGMA foreign_keys = ON;

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE
);

CREATE TABLE signups (
  signup_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL REFERENCES workshops(workshop_id),
  attendee TEXT NOT NULL
);

INSERT INTO workshops VALUES (1, 'sql-lab');
INSERT INTO signups VALUES (1, 1, 'Ada');

CREATE TABLE backup_workshops AS SELECT * FROM workshops;
CREATE TABLE backup_signups AS SELECT * FROM signups;

DROP TABLE signups;
DROP TABLE workshops;

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE
);

CREATE TABLE signups (
  signup_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL REFERENCES workshops(workshop_id),
  attendee TEXT NOT NULL
);

PRAGMA foreign_keys = OFF;

INSERT INTO signups
SELECT * FROM backup_signups;

PRAGMA foreign_key_check;

INSERT INTO workshops
SELECT * FROM backup_workshops;

PRAGMA foreign_key_check;

Edit the query, predict the rows it will return, then run it.

PRAGMA foreign_keys = ON;

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE
);

CREATE TABLE signups (
  signup_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL REFERENCES workshops(workshop_id),
  attendee TEXT NOT NULL
);

INSERT INTO workshops VALUES (1, 'sql-lab');
INSERT INTO signups VALUES (1, 1, 'Ada');

CREATE TABLE backup_workshops AS SELECT * FROM workshops;
CREATE TABLE backup_signups AS SELECT * FROM signups;

DROP TABLE signups;
DROP TABLE workshops;

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE
);

CREATE TABLE signups (
  signup_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL REFERENCES workshops(workshop_id),
  attendee TEXT NOT NULL
);

INSERT INTO workshops
SELECT * FROM backup_workshops;

INSERT INTO signups
SELECT * FROM backup_signups;

PRAGMA foreign_key_check;

SELECT s.attendee, w.slug
FROM signups AS s
JOIN workshops AS w ON w.workshop_id = s.workshop_id;

Edit the query, predict the rows it will return, then run it.

Compare counts; do not trust a quiet INSERT

After restore, the backup table and the live table should agree on COUNT(*). Spot-check a primary key. PRAGMA integrity_check should return ok. If counts differ, stop. Do not open the shop on a half-loaded catalog.

Production adds checksums, row sampling, and “restore to a side database, then swap.” Never restore by dropping prod and hoping the dump is last night's. Test the dump on Tuesday so Friday's incident is muscle memory.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 12),
  ('PEN-2', 'Gel pen', 40);

CREATE TABLE backup_products AS
SELECT * FROM products;

SELECT
  (SELECT COUNT(*) FROM products) AS live_rows,
  (SELECT COUNT(*) FROM backup_products) AS backup_rows;

DROP TABLE products;

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products
SELECT * FROM backup_products;

SELECT
  (SELECT COUNT(*) FROM products) AS restored_rows,
  (SELECT COUNT(*) FROM backup_products) AS backup_rows;

PRAGMA integrity_check;

Edit the query, predict the rows it will return, then run it.

Expand, then contract

Add listed_cents without requiring it on INSERT. Old code still writes (sku, name, stock). New code fills the price. A following UPDATE backfills NULLs. Weeks later you rebuild to listed_cents INTEGER NOT NULL and stop accepting the three-column insert. That delay is how you change schema without a flag day.

Contract too early (NOT NULL + drop old column while old app servers run) is how migrations become outages. Backup before both expand and contract.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 12),
  ('PEN-2', 'Gel pen', 40);

-- Expand: new column exists, old app can still INSERT (sku, name, stock).
ALTER TABLE products
ADD COLUMN listed_cents INTEGER;

UPDATE products
SET listed_cents = stock * 100;

-- New app reads listed_cents. Old writes leave it NULL until backfill.
INSERT INTO products (sku, name, stock)
VALUES ('MUG-9', 'Mug', 8);

UPDATE products
SET listed_cents = 900
WHERE sku = 'MUG-9' AND listed_cents IS NULL;

SELECT sku, name, stock, listed_cents
FROM products
ORDER BY sku;

Edit the query, predict the rows it will return, then run it.

What servers do that this page cannot

• SQLite. Copy the file after a quiet checkpoint, or VACUUM INTO. Keep the copy off the same disk. The CLI .dump is logical SQL.
• PostgreSQL. pg_dump (logical) and base backup + WAL for point-in-time recovery. Restore is pg_restore or replay. Schema migrations: Flyway, Liquibase, or app migrate commands.
• MySQL. mysqldump / dump utilities, or physical backups with the engine's rules. Do not copy InnoDB files casually.

Encryption, off-site copies, and “who may restore prod” are the next course section (roles). The SQL habit is: backup, migrate on a clone, restore drill, then prod.

What you should be able to do

• Explain logical versus physical backup.
• Snapshot schema and rows before DROP or ALTER.
• Stamp user_version only after a successful step.
• Expand with ADD COLUMN; rebuild to rename.
• Restore parents first and verify COUNT.

Independent lab: version 2 prices, then a loss

Start at user_version 1 with two products. Snapshot. Migrate to listed_cents and version 2. DROP products as the incident. Restore from the snapshot you actually took (version 1 or 2—be honest). Re-run migration 2 only if the restored database is still version 1. End with two rows, non-NULL prices, and user_version 2.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 12),
  ('PEN-2', 'Gel pen', 40);

PRAGMA user_version = 1;

-- Lab:
-- 1. Snapshot products (backup table + sqlite_master sql).
-- 2. Migration 2: ADD listed_cents, backfill, PRAGMA user_version = 2.
-- 3. Simulate loss: DROP products.
-- 4. Restore schema and rows from the snapshot, then re-apply migration 2
--    only if user_version is still 1 on a blank rebuild—or restore a snapshot
--    taken after version 2.
-- 5. Confirm COUNT matches and listed_cents is not NULL.

SELECT sku, name, stock
FROM products;

Edit the query, predict the rows it will return, then run it.

Common mistakes to avoid

• Never restoring a backup except during the outage.
• Recording user_version before the ALTER commits.
• ADD COLUMN in prod with no backup and no clone rehearsal.
• Rebuilding a table and forgetting indexes or triggers.
• Loading child tables first with foreign_keys ON.
• Trusting INSERT with no COUNT comparison.
• Contracting NOT NULL while old app servers still omit the column.

Lesson review

• I can define a backup as something I have restored onto empty storage.
• I can take a logical snapshot from sqlite_master and a copy of the rows.
• I can use user_version (or a migrations table) so steps run once.
• I can expand with ADD COLUMN and rebuild when a rename needs a new table.
• I can restore parents before children and read foreign_key_check.
• I can compare counts after restore before I trust the catalog.