Here’s a basic script to generate database backups for an Sqlite3 database.

Simply copying the .sqlite3 database file may work but there may be issues and data corruption if the database is locked or actively being used.

The better option is to use sqlite3’s built in .timeout and .backup utilities.

We also wanted to save the database file with the day of week included in it’s name.

The sqlite3 database backup shell script

#!/bin/bash

# Get the day of week
_dow="$(date +'%A')"

# open database, wait up to 20 seconds for any activity to end and create a backup file
sqlite3 /path/to/your/database.sqlite3 << EOF
.timeout 20000
.backup /path/to/your/production_${_dow}.sqlite3
EOF

Add execute permissions to the script.

$ chmod +x backup_db.sh

Schedule the backups to run automatically

Then we scheduled the script to run every day with cron.

crontab -e

This opens the crontab editor.

Add the following to the crontab file using the crontab editor:

#Run backup every day at 2:00 am
0 2 * * * /path/to/backup/script.sh

Save the file and run crontab – l to verify the job is saved.

crontab -l

Should return something like:

# m h  dom mon dow   command
0 2 * * * /path/to/your/script/backups/backup_db.sh

• References:
• Sqlite3 man page
• Date man page
• Safely backup sqlite (Stack Overflow)
• Save files with day of week in the name