Back Up SQLite Daily with Cron
To back up a SQLite database every day, use the sqlite3 .backup command from cron rather than copying the file. .backup produces a consistent snapshot even while the database is in use, and you write it to a dated file.
The crontab line
Run crontab -e and add (1 AM daily):
0 1 * * * /usr/bin/sqlite3 /var/data/app.db ".backup '/backups/app-$(date +\%F).db'" >> /var/log/sqlite-backup.log 2>&1
Note: escape the % in date +%F as \% — in crontab an unescaped % ends the command.
Why .backup, not cp
Copying the .db file while a write is in progress can produce a corrupt backup. The .backup command uses SQLite's online backup API for a consistent snapshot.
Compress and rotate
Gzip the result and prune old copies: add 0 2 * * * find /backups -name 'app-*.db' -mtime +14 -delete to keep two weeks.
FAQ
What is the cron command to back up SQLite daily?
0 1 * * * sqlite3 /path/app.db ".backup '/backups/app-$(date +\%F).db'" runs at 1 AM. Escape % as \% in crontab.
Can I just copy the SQLite file in cron?
Not safely while it's in use — a copy mid-write can be corrupt. Use the sqlite3 .backup command, which takes a consistent online snapshot.
Why does my dated SQLite backup filename break?
The % in date +%F is special in crontab. Escape each one as \% so the date substitution works.