---
title: 'Point-in-Time Recovery'
source: 'https://academia.sh/en/courses/database-administration/point-in-time-recovery'
course: 'Relational Database Administration'
language: en
updated: '2026-08-23T07:00:40+00:00'
license: 'CC BY-SA 4.0'
---

# Point-in-Time Recovery

Using a base backup together with a log archive: the recovery window, choosing the target moment, fast-forwarding the log to that target, the work lost after an incident, and computing a partial recovery.

The previous lesson left three gaps in the watermark-based incremental backup: updates,
deletes, and schema changes were not captured. All three shared the same cause — looking
at the final state of rows does not tell what happened between them.

A source that records every change in order already exists. To ensure durability, the
engine writes every change to a **log** before writing it to the data files; this
mechanism was covered in the Engine Architecture topic of this course. If that log is
retained, what is on hand is not just two snapshots of the database, but every movement
between them.

**Point-in-time recovery** is this put to use: a base backup is restored, log records are
applied on top of it up to a chosen moment, and the database returns to its state at that
moment.

## The Recovery Window

The method consists of two parts.

A **base backup** is a full copy at a specific moment — the logical or physical backup
from the previous lesson. It is the recovery's starting point.

A **log archive** is the stored record of log entries from the moment the base backup
was taken up to today. The engine keeps the log in its own working area for a limited
time; archiving is copying filled log segments to a separate location.

Together, the two define a **recovery window**: recovery can reach any point between the
base backup's moment and the archive's last moment. The window's start is set by the
base backup's age, its end by the archive's continuity. If archiving stops silently, the
window stops growing, and the recovery capability quietly regresses to the base backup's
moment without anyone noticing.

## An Incident and Its Recovery

The block below re-enacts a day. A base backup is taken at nine in the morning. Loan
records are opened and returns are processed throughout the day; every change is written
to the log with a timestamp. At 11:15, an unconditional delete statement runs — every
loan record from the Shore branch is gone. Nobody notices, and work continues until
evening.

Recovery means loading the base backup into a new database and applying the log up to
**just before** 11:15.

The `DELETE FROM loan WHERE branch_id=2;` statement in the block is deliberately
destructive and is what this lesson is about. It operates only on temporary files
produced within this block. In a real system, an unconditional or broadly conditioned
delete statement is tested first by running a count query with the same condition;
recovery does not substitute for that habit.

```bash
rm -f library.db recovered.db base_snapshot.db base.sql changes.log

sqlite3 library.db <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL,
                   branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT);
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<2000)
INSERT INTO loan(id,book_id,member_id,branch_id,pickup,returned)
SELECT n,(n%500)+1,(n%300)+1,(n%3)+1,'2025-06-01',NULL FROM s;
SQL

# 09:00 -- base backup
sqlite3 library.db .dump > base.sql

# Log: each line "time|statement". There is an unconditional delete at 11:15.
node - <<'EOF' > changes.log
const lines = [];
const time = (min) => new Date(Date.UTC(2025, 5, 10, 9, 0, 0) + min * 60000)
  .toISOString().slice(0, 19);
for (let i = 1; i <= 300; i++) {
  const min = i * 0.44;
  if (i % 3 === 0) {
    lines.push(time(min) + "|UPDATE loan SET returned='2025-06-10' WHERE id=" + (i * 5) + ";");
  } else {
    const id = 2000 + i;
    lines.push(time(min) + "|INSERT INTO loan VALUES(" + id + "," + ((id % 500) + 1) +
               "," + ((id % 300) + 1) + "," + ((id % 3) + 1) + ",'2025-06-10',NULL);");
  }
}
lines.push("2025-06-10T11:15:00|DELETE FROM loan WHERE branch_id=2;");
for (let i = 1; i <= 200; i++) {
  const id = 3000 + i;
  lines.push(time(136 + i * 1.7) + "|INSERT INTO loan VALUES(" + id + "," + ((id % 500) + 1) +
             "," + ((id % 300) + 1) + "," + ((id % 3) + 1) + ",'2025-06-10',NULL);");
}
console.log(lines.join("\n"));
EOF

# The full log is applied to the live database.
awk -F'|' '{print $2}' changes.log | sqlite3 library.db

# Recovery: base backup plus the portion of the log up to the target moment.
TARGET='2025-06-10T11:15:00'
sqlite3 recovered.db < base.sql
awk -F'|' -v t="$TARGET" '$1 < t {print $2}' changes.log | sqlite3 recovered.db

sqlite3 base_snapshot.db < base.sql

echo "log entries    : $(wc -l < changes.log | tr -d ' ')"
echo "entries applied: $(awk -F'|' -v t="$TARGET" '$1 < t' changes.log | wc -l | tr -d ' ')"
echo "entries skipped: $(awk -F'|' -v t="$TARGET" '$1 >= t' changes.log | wc -l | tr -d ' ')"

sqlite3 -box -header library.db "
ATTACH 'recovered.db' AS r; ATTACH 'base_snapshot.db' AS b;
SELECT 'base backup (09:00)' AS state, COUNT(*) AS rows, SUM(branch_id=2) AS branch2, SUM(returned IS NOT NULL) AS returned FROM b.loan
UNION ALL SELECT 'recovered (before 11:15)', COUNT(*), SUM(branch_id=2), SUM(returned IS NOT NULL) FROM r.loan
UNION ALL SELECT 'live (17:00, after the incident)', COUNT(*), SUM(branch_id=2), SUM(returned IS NOT NULL) FROM main.loan;

SELECT (SELECT COUNT(*) FROM r.loan WHERE id NOT IN (SELECT id FROM main.loan)) AS only_in_recovered,
       (SELECT COUNT(*) FROM main.loan WHERE id NOT IN (SELECT id FROM r.loan)) AS only_in_live;"
```

```text
log entries    : 501
entries applied: 300
entries skipped: 201
┌──────────────────────────────────┬──────┬─────────┬──────────┐
│              state               │ rows │ branch2 │ returned │
├──────────────────────────────────┼──────┼─────────┼──────────┤
│ base backup (09:00)              │ 2000 │ 667     │ 0        │
│ recovered (before 11:15)         │ 2200 │ 767     │ 100      │
│ live (17:00, after the incident) │ 1633 │ 67      │ 100      │
└──────────────────────────────────┴──────┴─────────┴──────────┘
┌───────────────────┬──────────────┐
│ only_in_recovered │ only_in_live │
├───────────────────┼──────────────┤
│ 767               │ 200          │
└───────────────────┴──────────────┘
```

## Reading the Results

The first table places three states side by side.

At the moment of the base backup, there were two thousand rows, and none had been
returned. Returning to just this backup means erasing everything done after nine in the
morning — two hundred new loan records and one hundred return transactions.

The recovered copy is the state after three hundred log entries have been applied. The
Shore branch's seven hundred sixty-seven records are in place, and one hundred returns
have been processed. This is the actual state right before the incident, and it came
from the base backup plus the log, not from the base backup alone.

In the live database, only sixty-seven records from the Shore branch remain — all of
them new records opened after the incident. Seven hundred records have been deleted.

## The Cost of Recovery

The second table shows the real cost of recovery. The seven hundred sixty-seven rows
present in the recovered copy but not in the live database are what the delete took. The
two hundred rows present in the live database but not in the recovered copy are **what
recovery would take**: ordinary work done after the incident.

Putting the recovered copy directly in place of the live database means deleting those
two hundred records. This is the side of point-in-time recovery most often overlooked:
choosing the target moment means sacrificing everything after it.

This cost grows as the time between the incident and its discovery stretches out. A
deletion noticed six hours later produces two hundred records' worth of lost work; the
same deletion noticed two days later produces two days' worth.

For this reason, the preferred path in operations is not to put the recovered copy in
place of the live one, but to put it **alongside** it. The recovered copy is opened as a
separate database, the missing rows are pulled from it, and added to the live one. The
second table's first column is exactly the input to this operation: which rows to move
has already been counted. This path works only for deletions; a schema corruption or a
widespread update error requires a full rollback.

## Choosing the Target Moment

The recovery target can be given in two forms.

**By timestamp.** As in the example above: "apply up to this moment." It is easy to
read but not exact — several transactions may have finished within the same second, and
all of them are either included or excluded together.

**By transaction identifier.** With the sequence number or transaction ID engines write
to the log: "apply up to this transaction, exclusive." It gives an exact boundary.
Finding the faulty statement's identifier requires being able to read the log; most
engines provide an inspection tool for this.

In practice, the two steps are used together: the range is first narrowed roughly by
timestamp, then the log entries in that range are read to find the faulty statement's
exact boundary. Performing the recovery in a test environment first leaves room for a
second attempt if the target was chosen wrong.

## Where This Model Actually Diverges

The log above consists of statement text and is applied by re-executing it. This is a
model; it correctly shows the concepts of the recovery window, fast-forwarding, and
target selection, but real engines' logs do not look like this.

Real logs record change at the physical level: what a given page's bytes became, or what
values a given row moved to. The reason is **non-deterministic statements**. A statement
that uses the current time, a random number, or a sequence generator produces a
different result when re-executed. A recovery that replays statement text produces a
database that has drifted from the source.

The second difference is transaction boundaries. A real log carries which transaction
each record belongs to, and whether that transaction committed. During recovery, the
records of uncommitted transactions are rolled back. In the model above, each line is
independent; there is no concept of a transaction.

Three things remain conceptually unchanged: recovery starts from the base backup, the
log is fast-forwarded in order, and it stops at the target moment.

## Summary

- Point-in-time recovery is applying log records on top of a base backup up to a chosen
  moment.
- The recovery window starts at the base backup's moment and ends at the log archive's
  last moment; if archiving stops, the window silently narrows.
- Choosing the target moment means sacrificing all work done after it; the loss grows
  with how long the incident goes unnoticed.
- Placing the recovered copy alongside the live one and moving the missing rows over
  prevents work loss in deletion incidents.
- The target is given roughly by timestamp or exactly by transaction identifier.
- Real logs record physical change, not statements; non-deterministic statements make
  recovery based on statement replay unreliable.

## Next Step

Recovery worked in this lesson because the base backup was readable and the log was
complete. That both are true is known only by attempting the recovery — and the moment
that attempt happens should not be the moment of an actual failure. The next lesson
takes up backup validation: checking a backup's integrity with a hash, catching a
corrupted backup before it is restored, and what a regular recovery drill actually
guarantees.