> Complete content index: https://sirannon.sondelali.com/llms.txt

# Restoring a database

> Work out what a restore to a given moment reads, rebuild the database from that moment, and find the backups no restore still needs.

URL: https://sirannon.sondelali.com/docs/backup-restore
Section: Backups
Version: 0.3 (@delali/sirannon-db@0.3.0, latest)

A chain of backups exists for one job, which is putting a database back the way it was. `restoreBackup` takes the destination, the moment you want, and the path to write to, and it rebuilds the database from the full copy at the head of the chain plus the change pieces captured up to that moment.

The restore fetches one stored piece and applies it before it asks for the next, so the memory it holds is one piece however large the database it rebuilds. That is what lets a restore on a small machine rebuild a database far bigger than its memory.

This page builds one file, `restore.ts`, step by step. You will build a chain with two captures, ask what a restore to the earlier of them would read, run it, and count the rows that come back. It closes with a standalone file that lists the backups no restore still needs. The [chains page](/docs/backup-chains) builds the chain this page reads.

```bash
rm -rf data && mkdir -p data
```

## Ask what a restore would read

`db.backupRestorePlan(moment)` picks the newest full copy finished at or before that moment, then every change piece of that chain captured at or before it, in sequence order. It reads the chain records and fetches no backup, so it is cheap enough to run before every restore.

The plan also states `restoresTo`, which is the moment the rebuilt database would reflect. One change piece covers every write in the interval it was taken over, so a restore stops at a piece boundary, which may be earlier than the exact millisecond you named. Reading `restoresTo` before you start is how you find out how much you would give up.

The database below takes two captures with two orders between them, then asks for a restore to the first of those captures.

```ts title="restore.ts"

const stored = new Map<string, Uint8Array>()
const pieceKey = (name: string, index: number): string => `${name}#${index}`

const memory: BackupDestination = {
  async writePiece(name, index, bytes) {
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
  },
  async writePieceIfAbsent(name, index, bytes) {
    if (stored.has(pieceKey(name, index))) return false
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
    return true
  },
  async readPiece(name, index) {
    const bytes = stored.get(pieceKey(name, index))
    if (!bytes) throw new Error(`No piece ${index} of '${name}'`)
    return bytes
  },
  async listPieces(name) {
    const pieces: BackupPiece[] = []
    for (const [key, bytes] of stored) {
      if (key.startsWith(`${name}#`)) {
        pieces.push({ index: Number(key.slice(name.length + 1)), byteLength: bytes.byteLength })
      }
    }
    return pieces
  },
}

const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })

const db = await sirannon.open('orders', './data/orders.db', {
  backups: { destination: memory, intervalMs: 0, onError: (err) => console.error('Backup cycle failed:', err.message) },
})

await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')
await db.executeBatch('INSERT INTO orders (customer, total) VALUES (?, ?)', [
  ['Amara Okonkwo', 128.5],
  ['Lucía Fernández', 64],
  ['Yuki Tanaka', 249.99],
])

await db.captureBackupChanges()

await db.executeBatch('INSERT INTO orders (customer, total) VALUES (?, ?)', [
  ['Priya Raghunathan', 19.99],
  ['Tomás Ferreira', 310],
])

await db.captureBackupChanges()

const chains = await db.backupChain()
const beforeTheLastTwoOrders = chains[0].changes[0].capturedAt

const plan = await db.backupRestorePlan(beforeTheLastTwoOrders)

console.log(
  JSON.stringify(
    {
      chain: { changes: chains[0].changes.length },
      plan: {
        changes: plan.changes.length,
        reachesTheMomentAsked: plan.restoresTo === beforeTheLastTwoOrders,
        baseIsTheChainsFullCopy: plan.base.name === chains[0].base?.name,
      },
    },
    null,
    2,
  ),
)

await sirannon.shutdown()
```

```json result
{
  "chain": {
    "changes": 2
  },
  "plan": {
    "changes": 1,
    "reachesTheMomentAsked": true,
    "baseIsTheChainsFullCopy": true
  }
}
```

The chain holds two change pieces and the plan selects one, because the second piece was captured after the moment asked for. `intervalMs: 0` keeps the cycle from taking a turn of its own, which is what makes the two captures on this page the only two.

A moment no full copy reaches fails with `BACKUP_CHAIN_BROKEN`, and the message names the earliest moment the destination can restore. A gap in the selected sequence fails with the same code and names the piece the sequence stops at, because a plan that stopped part-way through would rebuild a database missing every write past that gap.

## Rebuild the database

`restoreBackup` is exported from `@delali/sirannon-db/backup`, a subpath of its own, so a recovery tool can import it and leave the rest of the library out. It needs the destination, the name the chains are listed under, and a driver to open the rebuilt database through. `db.backupLocation()` returns the first two exactly as the database was opened with them, including the destination deadline the operator set.

```ts title="restore.ts"

import { restoreBackup } from '@delali/sirannon-db/backup' // [!code ++]

const stored = new Map<string, Uint8Array>()
const pieceKey = (name: string, index: number): string => `${name}#${index}`

const memory: BackupDestination = {
  async writePiece(name, index, bytes) {
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
  },
  async writePieceIfAbsent(name, index, bytes) {
    if (stored.has(pieceKey(name, index))) return false
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
    return true
  },
  async readPiece(name, index) {
    const bytes = stored.get(pieceKey(name, index))
    if (!bytes) throw new Error(`No piece ${index} of '${name}'`)
    return bytes
  },
  async listPieces(name) {
    const pieces: BackupPiece[] = []
    for (const [key, bytes] of stored) {
      if (key.startsWith(`${name}#`)) {
        pieces.push({ index: Number(key.slice(name.length + 1)), byteLength: bytes.byteLength })
      }
    }
    return pieces
  },
}

const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })

const db = await sirannon.open('orders', './data/orders.db', {
  backups: { destination: memory, intervalMs: 0, onError: (err) => console.error('Backup cycle failed:', err.message) },
})

await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')
await db.executeBatch('INSERT INTO orders (customer, total) VALUES (?, ?)', [
  ['Amara Okonkwo', 128.5],
  ['Lucía Fernández', 64],
  ['Yuki Tanaka', 249.99],
])

await db.captureBackupChanges()

await db.executeBatch('INSERT INTO orders (customer, total) VALUES (?, ?)', [
  ['Priya Raghunathan', 19.99],
  ['Tomás Ferreira', 310],
])

await db.captureBackupChanges()

const chains = await db.backupChain()
const beforeTheLastTwoOrders = chains[0].changes[0].capturedAt

const plan = await db.backupRestorePlan(beforeTheLastTwoOrders)
// [!code ++:17]
const location = db.backupLocation()

await sirannon.shutdown()

const rebuilt = await restoreBackup({
  destination: location.destination,
  chainName: location.chainName,
  driver,
  destPath: './data/orders-earlier.db',
  moment: beforeTheLastTwoOrders,
})

const check = new Sirannon({ driver: betterSqlite3() })
const earlier = await check.open('earlier', './data/orders-earlier.db')
const counted = await earlier.queryOne<{ orders: number }>('SELECT COUNT(*) AS orders FROM orders')

await check.shutdown()

console.log(
  JSON.stringify(
    {
      chain: { changes: chains[0].changes.length },
      plan: {
        changes: plan.changes.length,
        reachesTheMomentAsked: plan.restoresTo === beforeTheLastTwoOrders,
        baseIsTheChainsFullCopy: plan.base.name === chains[0].base?.name,
      },
      // [!code ++:9]
      restore: {
        changesApplied: rebuilt.changesApplied,
        framesApplied: rebuilt.framesApplied,
        batchCount: rebuilt.batchCount,
        pieceCount: rebuilt.pieceCount,
        bytesFetched: rebuilt.bytesFetched,
        reachedTheMomentAsked: rebuilt.restoresTo === beforeTheLastTwoOrders,
        orders: counted?.orders,
      },
    },
    null,
    2,
  ),
)

await sirannon.shutdown() // [!code --]
```

```json result
{
  "chain": {
    "changes": 2
  },
  "plan": {
    "changes": 1,
    "reachesTheMomentAsked": true,
    "baseIsTheChainsFullCopy": true
  },
  "restore": {
    "changesApplied": 1,
    "framesApplied": 3,
    "batchCount": 1,
    "pieceCount": 2,
    "bytesFetched": 20584,
    "reachedTheMomentAsked": true,
    "orders": 3
  }
}
```

Three orders come back, which is the state the database was in when the first capture ran. The two orders written after it stay in the second change piece, and a moment of `Date.now()` would bring all five back.

`pieceCount` counts the full copy and every change piece the restore fetched. `framesApplied` counts the log frames those change pieces held, and `batchCount` counts the batches they were replayed in, each one folded into the database by a checkpoint of its own.

## Where a restore writes, and what it leaves behind

Sirannon assembles the rebuilt database beside the path you named, at `{destPath}.restoring`, and renames it onto that path once the last batch is folded in. A restore that fails, and one the machine kills part-way, therefore leave that path holding whatever it held before.

Where a database already occupies the path, Sirannon takes two further steps in a fixed order, because the rename replaces the file while that database's write-ahead log still holds commits the file does not.

- Sirannon folds that log back into the database before the rename, so a machine that stops between those two steps leaves the old database whole.
- Where the fold cannot empty the log, because another connection holds the database or SQLite cannot open the file, Sirannon removes that database together with its log, so a machine stopping there leaves the path empty, which an operator can see at once.

That is also why `replaceExisting` defaults to false. A path already holding a file fails with `BACKUP_ERROR` and the message says what to do: `A restore would replace './data/orders.db', and its write-ahead log with it. Pass replaceExisting to say that is what you want, or name a path this process holds nothing at.`

Restoring over a database Sirannon still has open needs that database closed first, since no connection may be open on the file while its bytes are replaced. Over the network the server does that for you, and the [server page](/docs/server#restore-a-database-over-the-network) covers the route.

| Option | Default | What it does |
| --- | --- | --- |
| `destination` | required | Where the backups and their records are stored. |
| `driver` | required | Driver the restore opens the rebuilt database through to fold each batch in. |
| `destPath` | required | Path the rebuilt database is written to. |
| `moment` | now | Epoch milliseconds you want back, which reaches the newest backup the destination holds. |
| `chainName` | `sirannon-backup-chain` | Name the list of chains is stored under. |
| `replaceExisting` | `false` | Whether to replace a database already at that path. |
| `batchSize` | `16` | Change pieces replayed between one checkpoint and the next, up to 4096. |
| `destinationTimeoutMs` | `600000` | Milliseconds one call to the destination may take. |
| `onProgress` | none | Called after every piece, with `phase` reading `'full-copy'` or `'changes'`. |

`batchSize` is what bounds the log Sirannon writes beside the rebuilt database, so the disk a restore needs is the finished database, plus one stored piece, plus one batch of change pieces. Lower it where disk is tight, and raise it where a long chain would otherwise run too many checkpoints. At the default capture interval of one minute, the ceiling of 4,096 covers close to three days of pieces, which is longer than the day a chain lasts before a fresh full copy replaces it.

## Find the backups nobody needs

A destination fills up with chains a newer full copy has already made redundant. `db.backupPiecesSafeToDelete(options)` names the records no restore still needs, and it deletes nothing, because the destination is yours and your retention policy governs what goes.

Three kinds of record come back. A chain whose full copy has gone is unusable whatever you ask for, since nothing can replay its change pieces onto a copy that is absent. Every change piece after a gap is unusable for the same reason. Given `restorableFrom`, the answer also covers every chain older than the newest full copy finished at or before that moment.

The standalone file below sets `fullCopyIntervalMs` to one millisecond so that each turn starts a fresh chain, which gives it three chains in under a second. A service leaves that option at its default of 24 hours and reaches the same state a day at a time.

```ts title="prune-backups.ts"

const stored = new Map<string, Uint8Array>()
const pieceKey = (name: string, index: number): string => `${name}#${index}`

const memory: BackupDestination = {
  async writePiece(name, index, bytes) {
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
  },
  async writePieceIfAbsent(name, index, bytes) {
    if (stored.has(pieceKey(name, index))) return false
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
    return true
  },
  async readPiece(name, index) {
    const bytes = stored.get(pieceKey(name, index))
    if (!bytes) throw new Error(`No piece ${index} of '${name}'`)
    return bytes
  },
  async listPieces(name) {
    const pieces: BackupPiece[] = []
    for (const [key, bytes] of stored) {
      if (key.startsWith(`${name}#`)) {
        pieces.push({ index: Number(key.slice(name.length + 1)), byteLength: bytes.byteLength })
      }
    }
    return pieces
  },
}

const sirannon = new Sirannon({ driver: betterSqlite3() })

const db = await sirannon.open('orders', './data/orders.db', {
  backups: {
    destination: memory,
    intervalMs: 0,
    fullCopyIntervalMs: 1,
    onError: (err) => console.error('Backup cycle failed:', err.message),
  },
})

await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')
await db.execute('INSERT INTO orders (customer, total) VALUES (?, ?)', ['Amara Okonkwo', 128.5])
await db.captureBackupChanges()
await db.execute('INSERT INTO orders (customer, total) VALUES (?, ?)', ['Lucía Fernández', 64])
await db.captureBackupChanges()

const chains = await db.backupChain()
const deletable = await db.backupPiecesSafeToDelete({ restorableFrom: Date.now() })

console.log(
  JSON.stringify(
    {
      chainsAtTheDestination: chains.length,
      deletable: deletable.map((record) => ({ kind: record.kind, spansTheNewestChain: record.chainId === chains[0].chainId })),
    },
    null,
    2,
  ),
)

await sirannon.shutdown()
```

```json result
{
  "chainsAtTheDestination": 3,
  "deletable": [
    {
      "kind": "full",
      "spansTheNewestChain": false
    },
    {
      "kind": "full",
      "spansTheNewestChain": false
    }
  ]
}
```

The destination holds three chains, and the answer names the two older full copies as deletable, oldest first, because the newest full copy already reaches the moment asked for. The newest chain stays out of the answer, because a restore to now would read it.

With `restorableFrom` left out, the answer covers only the records no restore could ever use, whatever moment it asked for. Pass the moment your retention policy still promises, and the answer covers everything older than the full copy that reaches it.

`backupPiecesSafeToDelete` returns `BackupChainRecord` values, each stating the `name` its pieces are stored under and the `pieceCount` those pieces run to, which is what a delete loop over your own storage needs.