Scheduled SQLite Backups With a systemd Timer: The 10-Minute Setup

The best backup is the one that runs without you. For a SQLite service on Linux, a systemd timer is the boring, reliable way to get there. Here is the 10-minute setup I use.

The backup script

#!/usr/bin/env bash
set -euo pipefail
DB=/srv/myapp/data/app.db
BACKUP_DIR=/srv/myapp/backups
mkdir -p "$BACKUP_DIR"
STAMP=$(date -u +%Y%m%d-%H%M%S)
# consistent, compacted backup while the service runs
sqlite3 "$DB" "VACUUM INTO '$BACKUP_DIR/app-$STAMP.db'"
# retention: keep the last 14 daily backups
find "$BACKUP_DIR" -name 'app-*.db' -mtime +14 -delete

VACUUM INTO is the key line: it takes a consistent snapshot while the app is running and compacts the file in one step — no stopping the service, no -wal juggling.

The systemd unit + timer

/etc/systemd/system/myapp-backup.service

[Unit]
Description=SQLite backup for myapp

[Service]
Type=oneshot
ExecStart=/srv/myapp/scripts/backup.sh

/etc/systemd/system/myapp-backup.timer

[Unit]
Description=Daily SQLite backup

[Timer]
OnCalendar=daily
Persistent=true

[Install]
WantedBy=timers.target

Then:

sudo systemctl daemon-reload
sudo systemctl enable --now myapp-backup.timer
sudo systemctl list-timers myapp-backup.timer   # verify it is scheduled

Persistent=true means a missed run (machine asleep at the scheduled time) fires as soon as the machine is back.

The part people skip: test the restore

A backup you never restore is a folder of bytes. Add a weekly check:

sqlite3 "$BACKUP_DIR/$(ls -t $BACKUP_DIR | head -1)" "PRAGMA integrity_check;" | grep -q ok

Better: restore into a scratch file and compare row counts. If the restore test is not part of the routine, the routine is incomplete.

That is the whole setup — script, unit, timer, restore test. Run it before you have data you cannot afford to lose. Sizing the database before you start? The estimator below plans the disk you will need.