Part 6 — Data at work › Chapter 41

Backup, restore, PITR and replication

RPO and RTO in one sentence each, the backup nobody tested, and what replication does not protect you from.

From the advert

“Gestione di backup, restore e manutenzione dei database” — and in a small Italian software house, the person who writes the SQL is usually also the person who finds out whether the backups work.

RPO and RTO, one sentence each

🧒 Simple

Two different questions. How much of yesterday are you willing to lose? And how long may everything be broken while you fix it? They are not the same question and they usually have different answers.

	Question	Bought by
RPO Recovery Point Objective	How much data may we lose?	Backup frequency. A 15-minute RPO means log backups every 15 minutes
RTO Recovery Time Objective	How long may we be down?	Restore speed, standby servers, automatic failover

✔ The first question to ask, and almost nobody does

“What are our RPO and RTO?” — because if nobody has decided them, then nobody has decided the backup schedule either, and whatever is currently running was chosen by default. Asking it in an interview, about their systems, is a very strong signal.

The three backup types, and how they combine

Type	Contains	To restore you need
Full	Everything	Just itself
Differential	Changes since the last full	The full + the latest differential
Transaction log	Every change since the last log backup	The full + differential + every log backup in sequence

A backup schedule, read as the recovery it buys you.

-- A typical schedule and what it costs you
Sunday 02:00   FULL                 -- the base: everything, once a week
Daily  02:00   DIFFERENTIAL         -- everything changed since the FULL, not since yesterday
Every  15 min  LOG                  -> RPO = 15 minutes worst case

-- Restoring to Wednesday 14:23 needs:
--   Sunday's full  +  Wednesday's differential  +  every log from 02:00 to 14:23
-- One missing log in that chain and you stop at the gap. Which is why the CHAIN
-- matters more than any individual file.

How to read a schedule

This is a plan, not SQL. Each line is a job; the arrow is what the schedule guarantees.
A differential is measured from the last full, not from the last differential. So they grow through the week, and taking an unscheduled full backup silently resets the base every later differential depends on — which is why an ad-hoc backup must use COPY_ONLY.
RPO — how much data you accept losing — is set by the log interval. RTO — how long you accept being down — is set by how long the restore takes, which is a different problem with different answers.
The log chain is a chain: one missing file and recovery stops there. Copying backups to a second location is protecting the chain, not the files.

⚠ Trap — the log backup that breaks the chain

Somebody takes an ad-hoc full backup with a tool that truncates the log, or switches the database to SIMPLE recovery “to stop the log growing”, or takes a COPY_ONLY backup believing it is part of the chain. Point-in-time recovery is now impossible between that moment and the next full backup, and nothing reports an error until the day you need it. Anything that touches the backup chain deserves a change request.

Point-in-time recovery

Point-in-time recovery: restoring to the moment before somebody ran a DELETE without a WHERE.

-- SQL Server: restore the chain, leaving the database RECOVERING until the last step.
RESTORE DATABASE forge FROM DISK = 'full.bak'   WITH NORECOVERY;  -- the base…
RESTORE DATABASE forge FROM DISK = 'diff.bak'   WITH NORECOVERY;  -- …changes since it…
RESTORE LOG      forge FROM DISK = 'log1.trn'   WITH NORECOVERY;  -- …then every log…
RESTORE LOG      forge FROM DISK = 'log2.trn'                     -- …in order…
       WITH STOPAT = '2026-09-03T14:23:00', RECOVERY;             -- …stopping HERE, and opening
--          ^ the moment BEFORE somebody ran the DELETE without a WHERE clause

-- PostgreSQL: a base backup plus archived WAL, with a recovery target.
-- postgresql.conf:  restore_command = 'cp /archive/%f %p'         -- where to find the WAL…
--                   recovery_target_time = '2026-09-03 14:23:00'  -- …and when to stop

The one keyword that ruins the restore

WITH NORECOVERY leaves the database in a restoring state so more backups can be applied. Forget it on an intermediate step and the database opens — and the chain is broken. You start again from the full backup.
RECOVERY (the default) is the last step: it rolls back uncommitted transactions and opens the database. Once done, no further log can be applied.
STOPAT is exclusive of the transaction you are avoiding, so pick a timestamp just before it. Finding that timestamp is what the transaction log or the audit table is for.
Restore to a different database name first, check the data, then swap. Point in-time recovery over the live database is how a recoverable incident becomes an unrecoverable one.
A backup you have never restored is a hypothesis. Schedule the rehearsal, not just the backup.

Restore to a copy, not over the original

When somebody deletes a table at 14:23, the instinct is to restore the database. Far better: restore to a new database name, extract the rows you lost, and insert them back into the live system. The rest of the business keeps working, and you have not thrown away everything that happened after 14:23 in order to recover something from before it.

A backup nobody has restored is not a backup

🎓 The four things a restore drill actually discovers

Whether the files are readable. Silent corruption, a full destination, a permission change six months ago.
How long it takes. Which is your RTO, whatever the document says. A 900 GB restore over a network share is not a twenty-minute job.
What else is needed. Logins and users are separate objects in SQL Server — restore a database and every user is orphaned. Certificates, linked servers, jobs, encryption keys: none of it is in the database backup.
Whether anyone knows how. At 03:00, under pressure, from a runbook that has never been followed.

✔ The honest sentence

“We do not have backups; we have restores that we have tested. Anything else is a file we hope about.” If you say one thing about backups in an interview, say a version of that — and if you have actually run a restore drill, say when.

Replication and high availability

Mechanism	Protects against	Does NOT protect against
Synchronous replica	Node failure, with no data loss	A bad `DELETE` — it is replicated instantly
Asynchronous replica	Node or site failure; scales reads	The same, plus you can lose the replication lag on failover
Backups	Human error, corruption, ransomware, "oops"	Downtime — a restore takes as long as it takes

⚠ Replication is not a backup, and this is not pedantry

A replica faithfully reproduces every statement — including DELETE FROM orders with no WHERE, in milliseconds, on every node. Replication answers availability; backups answer recoverability. You need both, they cost different money, and conflating them is one of the most expensive mistakes in the field. The ransomware era has made it worse: an attacker who reaches the primary reaches the replicas too, which is why offline or immutable backup copies now matter more than they used to.

Read replicas, and the lag

An asynchronous replica also solves a different problem: taking reporting load off the primary. The catch is replication lag, and its symptom is specific and confusing — a user saves a record, is redirected to a page that reads from the replica, and the record is not there. The fixes are to route read-after-write to the primary, or to wait for the replica to catch up to the write's log position before reading.

RPO is how much data you may lose; RTO is how long you may be down. Different questions, different costs.
Log backups define your RPO. Full and differential backups define how long the restore takes.
Point-in-time recovery needs an unbroken log chain. One missing file and you stop at the gap.
Anything that truncates the log or changes the recovery model breaks the chain silently.
Restore to a copy and extract what you lost, rather than rolling the whole system back.
An untested backup is a file you hope about. The drill is what tells you your real RTO.
Logins, jobs, certificates and linked servers are not in the database backup.
Replication is availability, not recoverability. It replicates your mistakes faithfully.
Ransomware makes offline or immutable copies necessary, not merely prudent.
Read replicas lag. Route read-after-write to the primary.

Interview questions this chapter answers

Describe a backup strategy.

Start from RPO and RTO, because they decide everything else. Typically a weekly full, daily differentials and log backups at the RPO interval — so a 15-minute RPO means log backups every 15 minutes. Then off-site and, now, immutable copies; monitoring that the jobs actually ran; and a scheduled restore drill, because the restore time is the real RTO and the only way to know it is to do it.

Is a replica a backup?

No. It replicates every statement including the mistaken ones — a DELETE without a WHERE is on every node in milliseconds. Replication gives availability; backups give recoverability from human error, corruption and ransomware. You need both.

Somebody deleted a table at 14:23. What do you do?

Stop further writes if I can, then restore the full backup plus differential plus logs to a new database with STOPAT just before 14:23, extract the missing rows and insert them back into the live system. Restoring over the original would also discard everything legitimate that happened after 14:23.