Backup SQLite

Published on 22 December, 2019
Tags: selfhosted

SQLite is one of the most popular embedded relational database. The SQLite native libraries or language bindings are available for most popular languages. SQLite files can be accessed either interactively or using shell scripts using the command-line utility.

SQLite database does not run a daemon on the system. Instead, it runs as part of the application. It stores the data in a single file. For backup, copying the data file is not the best approach, as the process might still be writing the data. SQLite utility implements a dot command (.backup) to simplify the process. The command takes a snapshot of the current data and writes it to a newer file in a consistent way. The backup is a new copy of the data file that is also an SQLite database.

The WriteFreely instance running this website uses an SQLite database. I've written the following shell script to take the backup of the database. To automate the daily backups, I've configured Cron to run the script at midnight.

!#/bin/sh

sqlite3 /path/to/writefreely.db ".backup /backup/path/backup.db"