Backup SQLite

The Writefreely instance running the current website is using SQLite database to store the content. SQLite is an embedded relational database which primarily ships as a library with bindings for a lot of programming languages. It also ships a helper command line utility sqlite3 which can be used to interact with database file. This tool can be used to take backup by calling .backup command.
$ cat backup.sh
!#/bin/sh
sqlite3 /path/to/writefreely.db ".backup /backup/path/backup.db"
$
This is equivalent to SQL dump which can be imported in a new instance if need arises. The backup process can be configured to run everyday using cron daemon. The cron expression for taking backup at midnight 12 is 0 0 * * *. The following line can be added to cronjob to automate the process.
0 0 * * * ~/backup.sh
Cron expressions for different time/frequency can be easily generated using the web-based tool called crontab.guru.

For offsite backups, the SQL dump can be copied to a remote machine using rsync.
$ cat offsite-backup.sh
#!/bin/sh
sqlite3 /path/to/writefreely.db ".backup /backup/path/backup.db"
rsync -acvh /backup/path/ ssh://user@remotehost/remote/backuppath/
$

Comments