# Back up Docker app SQLite databases without stopping containers

Most self-hosted media apps (Plex, Jellyfin, Sonarr, Radarr, Lidarr, Prowlarr, Bazarr, Tautulli, Overseerr/Jellyseerr, SABnzbd, Calibre and others) keep their state in SQLite files under their config volume. Copying those files with `cp` or `tar` while the app is running can give you a corrupt copy. SQLite's **online backup** API makes a consistent copy while the app keeps running.

## The core idea
```
sqlite3 -readonly /data/sonarr/config/sonarr.db ".timeout 60000" ".backup '/tmp/sonarr.db'"
sqlite3 /tmp/sonarr.db "PRAGMA quick_check;"      # should print: ok
gzip -c /tmp/sonarr.db > /mnt/backup/$(hostname -s)/sonarr/$(date +%Y.%m.%d_%H).sonarr.db.gz
```
`.timeout` makes sqlite wait for the app's write locks instead of failing.

## A script around it
Keep a simple list of `app|path|check` entries at the top, for example:
```
DBS=(
  "plex|$DATA/plex/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db|0"
  "sonarr|$DATA/sonarr/config/sonarr.db|1"
  "radarr|$DATA/radarr/config/radarr.db|1"
)
```
The last field turns the integrity check on or off. Plex's database uses custom tokenizers that make `quick_check` report false errors, so set it to 0 for Plex.

Good habits worth building in:
- **Refuse to run if the backup share isn't mounted:** `mountpoint -q /mnt/backup || exit 1`. Otherwise backups fill the local disk.
- **Check free space** before starting (for example, require 10 GB).
- **Run gently** with `nice -n 19 ionice -c3`.
- **Prevent overlapping runs** with `flock -n`.
- **Write to `file.part`, then `mv`,** so a half-written backup never looks complete.
- **Also copy the app's own backup zips** (the *arr apps write them to `config/Backups`).
- **Add a `--list` option** that shows what would be backed up and writes nothing.
- **Keep N days and delete older copies:** `find ... -name '*.gz' -mtime +14 -delete`.

## Schedule
```
30 1 * * * root /usr/local/bin/backup_docker_dbs.sh >/dev/null 2>&1
```

## Restore
1. `docker stop <container>`
2. Move the live `app.db` aside, and remove `app.db-wal` and `app.db-shm`.
3. `gunzip -c <backup>.gz > app.db`
4. `chown <puid>:<pgid> app.db` (the IDs the container runs as).
5. `docker start <container>`