9. Backup and safety

• Category: Tutorial

What you will learn

• What protects your data, in simple words.
• How to check a database, make and test backups, and restore to an exact time.
• What to do when a disk is damaged: a real example with real output.

The examples use the database market with the sample data. No server may be running on it (these commands need the database closed). Chapter 8 shows the server versions (/api/check, /api/backup).

What protects your data

The details are in docs/FORMAT.md (for developers).

The commands

Exit codes: 0 OK, 1 error (for example: the database is open in another program), 2 damage, 3 the database needs a newer KDB (chapter 24).

kdb info

kdb info market

database        market
manifest        copy b, sequence 12
last shutdown   clean
log position    30 (saved in files up to 30)
newest change   2026-09-23 17:57:58
features        batched_log_sync, block_stats, partition_format_2
manifest keys   23

FILE                                      VERSION       SIZE          LSN  STATUS
catalog                                         6     2.1 KB           30  ok
t/1/all                                         7      777 B           30  ok
t/2/all                                         8      556 B           30  ok
t/3/2025                                        9     1.7 KB           30  ok
t/3/2026                                       10     3.6 KB           30  ok
t/4/2026-09                                    11    57.0 KB           30  ok
t/5/2026-09-09                                 12     1.4 KB           30  ok
t/5/2026-09-10                                 13     2.4 KB           30  ok
t/6/2026-09-10                                 14     2.7 KB           30  ok

9 files, 72.1 KB, 0 marked damaged
(STATUS is what the manifest records. To read every block and find new damage, run: kdb check)

kdb info only reads the list of files. It does not read the files. To find damage, use kdb check.

kdb check

kdb check market
"exit code: $LASTEXITCODE"

checked 11 files (74.7 KB) and 1 log segments
OK: no damage found
exit code: 0

kdb check changes nothing. Run it every day (see the routine at the end). For a running server, use the console's Check now or POST /api/check.

kdb backup

kdb backup market backups --keep-daily 7 --keep-weekly 4

backup created and verified: backups/20260923-121259-430
  9 files, 190.0 KB, log up to 30

• A backup is a new folder inside backups. Its name is the UTC time: 20260923-121259-430 = 2026-09-23 12:12:59.430 UTC (17:57 Nepal time).
• It holds a copy of the data files, the needed log files, and backup.kdb (the list, written last). A folder without backup.kdb is not finished and is never used.
• After copying, KDB reads the whole backup again. Only then it prints "verified".
• --keep-daily 7 --keep-weekly 4 deletes old backups: it keeps the newest backup of each of the last 7 days and each of the last 4 weeks (weeks start on Sunday, UTC). Without these options, nothing is deleted.
• kdb backup needs the database closed. With a running server, use BACKUP DATABASE, Backup now, or POST /api/backup. They work while the server keeps running.

--hard-links

kdb backup market backups-fast --hard-links

backup created and verified: backups-fast/20260923-121309-580
  9 files, 197.0 KB, log up to 38

Hard links are very fast and use no extra space. But the backup and the database then share the same disk sectors. If the disk goes bad, both are lost. Use hard links only as an extra "undo point" (for example before a big import), never as your only backup. The log files are always copied.

kdb backups and verify-backup

kdb backups backups

BACKUP                 CREATED               FILES       SIZE      LOG LSN
20260923-121259-430    2026-09-23 17:57:59       9   190.0 KB           30
1 backups

CREATED is shown in local time. The folder name is UTC.

$newest = Get-ChildItem backups -Directory | Sort-Object Name | Select-Object -Last 1
kdb verify-backup "backups\$($newest.Name)"
"exit code: $LASTEXITCODE"

OK: backup is intact
exit code: 0

A backup that you never tested is not a backup. Verify old backups from time to time, and do a test restore once a month (see below).

A story: the disk is damaged

This is a real run. We made a copy of the database and wrote zeros into the middle of one data file, like a bad disk sector would.

Never do this on real data. It is only for learning.

Before: a backup, then more work

The backup above was made at 17:57. After it, work went on:

-- Work goes on after the backup.
INSERT INTO ochl_day VALUES ('2026-09-13', 'CITY', 505.0, 509.0, 503.1, 508.2, 91000);
UPDATE ochl_day SET volume = 41500 WHERE symbol = 'VOLT' AND time IN '2026-09-10';

1 row(s) inserted

1 row(s) updated

The damage

# FOR LEARNING ONLY. Never do this to a real database.
# Make a copy, then write 64 zero bytes into the middle of one data file of the copy.
Copy-Item market market-test -Recurse

$file = Get-ChildItem market-test\files\t\4 -Filter *.kdb | Sort-Object Name | Select-Object -Last 1
"damaging files\t\4\$($file.Name) (table ochl_minutes)"
$fs = [System.IO.File]::OpenWrite($file.FullName)
$null = $fs.Seek(20000, [System.IO.SeekOrigin]::Begin)
$fs.Write((New-Object byte[] 64), 0, 64)
$fs.Close()

damaging files\t\4\2026-09.v11.kdb (table ochl_minutes)

Step 1: notice the problem

Queries on other tables still work. A query that needs the damaged rows refuses to answer. It never gives wrong numbers:

kdb sql market-test "SELECT count(*) FROM ochl_day"
kdb sql market-test "SELECT count(*) FROM ochl_minutes"
kdb sql market-test "SELECT time, close FROM ochl_minutes WHERE symbol = 'CITY' ORDER BY time DESC LIMIT 1"
"exit code: $LASTEXITCODE"

count(*)
--------
      58
(1 row, 0.3 ms)
count(*)
--------
     956
(1 row, 0.3 ms)
error: table 'ochl_minutes' partition 2026-09: market-test/files/t/4/2026-09.v11.kdb: block 0 (bytes 0-58257) failed its checksum. Run: kdb repair <data-dir> --backups <dir>
exit code: 2

• count(*) of ochl_minutes still answers, and the number is right. KDB saves the row count and the min, max and sum of each column with every file. count, sum, min and max of a whole partition come from these saved numbers, so the damaged block is not read.
• The third query needs the rows themselves. They are in the damaged block, so KDB refuses, names the file and the block, and exits with code 2.

Step 2: stop everything and check

Stop the server and all programs that use the database. Then:

kdb check market-test
"exit code: $LASTEXITCODE"

checked 12 files (78.4 KB) and 1 log segments
DAMAGED: t/4/2026-09: market-test/files/t/4/2026-09.v11.kdb: block 0 (bytes 0-58257) failed its checksum (no good previous version or backup copy)
PROBLEM: t/4/2026-09: marked DAMAGED: market-test/files/t/4/2026-09.v11.kdb: block 0 (bytes 0-58257) failed its checksum
PROBLEM: t/4/2026-09 v11 (current): 1 damaged block(s): 0

Damage found. Fix it with: kdb repair market-test --backups <backups-dir>
exit code: 2

Before a repair, copy the whole damaged folder somewhere safe (for example Copy-Item D:\kdb\market E:\damaged-copy -Recurse). Then nothing you try can make things worse.

Step 3: repair without a backup

The minute table's file has no older version (it was written only once), so the first rung of the ladder does not help:

kdb repair market-test
"exit code: $LASTEXITCODE"

manifest copy: b, last shutdown: clean
replayed 0 transactions after LSN 34, last LSN 34
DAMAGED: t/4/2026-09: market-test/files/t/4/2026-09.v11.kdb: block 0 (bytes 0-58257) failed its checksum (no good previous version or backup copy)
still damaged: t/4/2026-09: marked DAMAGED: market-test/files/t/4/2026-09.v11.kdb: block 0 (bytes 0-58257) failed its checksum
still damaged: t/4/2026-09 v11 (current): 1 damaged block(s): 0

Some damage could not be repaired. Restore a backup with: kdb restore
exit code: 2

Step 4: repair with the backups

kdb repair market-test --backups backups
"exit code: $LASTEXITCODE"

manifest copy: b, last shutdown: clean
replayed 2 transactions after LSN 30, last LSN 34
REPAIRED: t/4/2026-09: market-test/files/t/4/2026-09.v11.kdb: block 0 (bytes 0-58257) failed its checksum -> restored from backup backups/20260923-121259-430 (v11) and replaying the log from LSN 30

Repaired.
exit code: 0

KDB took the file from the backup and then replayed the log from the backup's position (30) to the end. So also the changes made after the backup are back.

Step 5: check again

kdb check market-test
"exit code: $LASTEXITCODE"
kdb sql market-test "SELECT count(*) AS minute_rows FROM ochl_minutes; SELECT time, symbol, close, volume FROM ochl_day WHERE time >= '2026-09-10' ORDER BY symbol, time"

checked 12 files (78.4 KB) and 1 log segments
OK: no damage found
exit code: 0
minute_rows
-----------
        956
(1 row, 0.7 ms)

time                     | symbol | close | volume
-------------------------+--------+-------+-------
2026-09-10T00:00:00.000Z | CITY   | 504.9 | 183040
2026-09-13T00:00:00.000Z | CITY   | 508.2 |  91000
2026-09-10T00:00:00.000Z | COAST  | 376.1 | 186200
2026-09-10T00:00:00.000Z | VOLT   | 191.2 |  41500
(4 rows, 0.2 ms)

All 956 minute rows are back, and so are the two changes made after the backup (the 2026-09-13 row and the volume 41500).

Also try: the damaged file was replaced from the previous version when one exists. Then kdb repair works even without --backups, and the message says using previous version v10 and replaying the log.

When the log is damaged

The log is only needed for changes that are not yet saved in the data files. kdb sql, kdb shell and a server stopped with Ctrl+C save everything when they close. So damage at the end of the log then costs nothing:

# FOR LEARNING ONLY. Damage the end of the log in another copy.
Copy-Item market market-log -Recurse

$log = Get-ChildItem market-log\wal -Filter *.log | Sort-Object Name | Select-Object -Last 1
$fs = [System.IO.File]::OpenWrite($log.FullName)
$null = $fs.Seek(-40, [System.IO.SeekOrigin]::End)
$fs.Write([byte[]](1..32), 0, 32)
$fs.Close()

kdb check market-log
"exit code: $LASTEXITCODE"

checked 12 files (78.4 KB) and 1 log segments
OK: no damage found
exit code: 0

kdb repair cuts the damaged end away and continues the log in a new file. Nothing is lost:

kdb repair market-log --backups backups
"exit code: $LASTEXITCODE"

manifest copy: a, last shutdown: clean
replayed 0 transactions after LSN 34, last LSN 34
dropped an unfinished write at the end of the log (185 bytes)

Nothing to repair.
exit code: 0

After a crash (power cut, killed server) the newest changes may be only in the log. If exactly that end is damaged, KDB cannot tell it from a write the crash cut in half, so it drops those last changes, as if the power had gone a moment earlier. After a crash, check the newest data and load it again if needed.

If log records that KDB still needs are damaged (not only the very end), KDB refuses to open the database and names the damaged log file. The choices, in this order:

1. kdb repair <db> --backups <folder>: when a backup has those log records, KDB copies the good bytes from it.
2. kdb restore from the newest backup with --logs-from <db>: keeps everything up to the damage.
3. kdb repair <db> --accept-log-loss: opens the database and accepts that the changes in the damaged part are lost.

A story: an UPDATE without WHERE

At about 14:05 someone runs an UPDATE and forgets the WHERE. Every close price becomes 0.

(kdb serve --safe-updates refuses such an UPDATE, see chapter 21. kdb sql, used here, does not.)

kdb sql market "INSERT INTO ochl_day VALUES ('2026-09-14', 'CITY', 508, 512, 507, 511, 64000)"

Start-Sleep -Seconds 2
# Remember a time when the data was still good (local time of this computer).
Get-Date -Format "yyyy-MM-dd HH:mm:ss" | Set-Content good-time.txt
Start-Sleep -Seconds 2

# The mistake: an UPDATE without WHERE.
kdb sql market "UPDATE ochl_day SET close = 0"
kdb sql market "SELECT time, symbol, close FROM ochl_day WHERE time >= '2026-09-10' ORDER BY symbol, time"

1 row(s) inserted
59 row(s) updated
time                     | symbol | close
-------------------------+--------+------
2026-09-10T00:00:00.000Z | CITY   |     0
2026-09-13T00:00:00.000Z | CITY   |     0
2026-09-14T00:00:00.000Z | CITY   |     0
2026-09-10T00:00:00.000Z | COAST  |     0
2026-09-10T00:00:00.000Z | VOLT   |     0
(5 rows, 0.2 ms)

(In real life you do not have good-time.txt. You know roughly when the mistake happened, for example "a few minutes after 14:05". Use a time before the mistake.)

Restore to the moment before the mistake

$goodTime = Get-Content good-time.txt
$backup = Get-ChildItem backups -Directory | Sort-Object Name | Select-Object -Last 1

kdb restore "backups\$($backup.Name)" market-restored --logs-from market --until $goodTime
"exit code: $LASTEXITCODE"
kdb sql market-restored "SELECT time, symbol, close FROM ochl_day WHERE time >= '2026-09-10' ORDER BY symbol, time"

stopped at the requested time: changes after LSN 36 are not restored
restored 9 data files from backups\20260923-121259-430 plus 1 newer log segments; the log (up to LSN 36) is replayed on first open
restored into market-restored
exit code: 0
time                     | symbol | close
-------------------------+--------+------
2026-09-10T00:00:00.000Z | CITY   | 504.9
2026-09-13T00:00:00.000Z | CITY   | 508.2
2026-09-14T00:00:00.000Z | CITY   |   511
2026-09-10T00:00:00.000Z | COAST  | 376.1
2026-09-10T00:00:00.000Z | VOLT   | 191.2
(5 rows, 0.3 ms)

• The prices are back. The row for 2026-09-14, made after the backup but before the mistake, is also there.
• --logs-from market takes the newer log from the old database. Without it, you get the backup as it was.
• --until "2026-09-11 14:05" or "2026-09-11 14:05:30" is local time of this computer. Changes in that second are included.
• The target folder must be new or empty:

$backup = Get-ChildItem backups -Directory | Sort-Object Name | Select-Object -Last 1
kdb restore "backups\$($backup.Name)" market-restored
"exit code: $LASTEXITCODE"

error: Already exists: restore target is not empty: market-restored
exit code: 1

Then switch over:

1. Stop the server.
2. Rename the folders: market → market-broken, market-restored → market.
3. Start the server again. Keep market-broken until you are sure.

Check the restored database before you switch:

kdb check market-restored
"exit code: $LASTEXITCODE"

checked 10 files (75.9 KB) and 2 log segments
OK: no damage found
exit code: 0

A backup holds only the current version of each file, so a restored database has no older versions yet. They appear again as data changes.

Daily routine for a trading database

The sample market closes at 15:00 Nepal time and trades Sunday to Thursday. Use your own market hours and days. Do this every trading day:

When a check finds damage:

1. Stop the server.
2. Copy the damaged database folder to a safe place.
3. kdb repair <db> --backups <backups>.
4. kdb check <db>. If it is OK, start the server again.
5. If repair fails: kdb restore <newest backup> <new folder> --logs-from <db>, check it, and switch folders.
6. Find out why (disk errors in Windows Event Viewer, a failing SSD). Replace bad hardware.

Other safety facts

• Backups while the server runs: POST /api/backup and scheduled backups do not stop the server. KDB waits for an open transaction (at most a few seconds), writes a checkpoint, and copies the files while reads and writes go on.
• Disk full: KDB stops accepting writes when less than 64 MB is free, so a full disk never leaves something half written. A program gets HTTP 507 (disk full). Writes work again when there is space, without a restart.
• Old versions: KDB keeps the 2 previous versions of each file (for the repair ladder) and moves older ones to _trash. Files older than 1 hour leave the trash while the server runs. PURGE TRASH frees that space at once.
• Log archive: old log files go to wal_archive (up to 1 GB by default; ALTER DATABASE ... SET WAL_ARCHIVE or the Settings page changes it), for backups and repairs. With it OFF, a damaged file can only be repaired from a backup.
• Killing the server (closing the window, Stop-Process) is safe: it is like a power cut. The next start shows last shutdown: not clean and replays the log. But Ctrl+C is better: it closes the database cleanly.

dump-file (for experts)

Shows the header and checks the blocks of one file:

kdb dump-file market-test\files\t\4\2026-09.v11.kdb

For a damaged file it ends with DAMAGED block 0 and exit code 2. Example from a damaged test file:

file         story/files/t/3/2026.v15.kdb
type         data file
version      1
data         3.1 KB
block size   65536
LSN          34
id           3
created      2026-09-11 13:45:11
blocks       1
digest       bb8fefdd
DAMAGED block 0

Try it yourself

1. Make a backup of market, then list the backups.
2. Delete all rows of live_data by mistake. Restore a new database from the backup with --logs-from and an --until time from before the delete.
3. What exit code does kdb check give on a healthy database, and what on a damaged one?

Next: 10. Moving from QuestDB