sqlite3 database backup script with integrity checking

sqlite3_backup.sh

#!/bin/bash

#
# sqlite3_backup.sh
# Script for backing up sqlite3 database with integrity checking
# Intended for use with cron for regular automated backups
#
# @author <[email protected]>
#

DB="/path/to/file.db"
BKUP_PATH="/path/to/backup/dir"
LOG="/path/to/backup.log"

# check to see if $DB exists
if [ ! -f $DB ]
then
    echo $(date +"%Y-%m-%d %T") -- cannot find the database at the following path: $DB >> $LOG
    exit 1
fi

# check to make sure $BKUP_PATH exists and is writable
if [ ! -d $BKUP_PATH ]
then
	# BKUP_PATH does not exist or is not a directory
    echo $(date +"%Y-%m-%d %T") -- cannot find backup path: $BKUP_PATH >> $LOG
    exit 1
else
	# check to make sure it's writable
	if [ ! -w $DB ]
	then
		echo $(date +"%Y-%m-%d %T") -- BKUP Path is not writable: $BKUP_PATH >> $LOG
		exit 1
	fi
fi

# check to see if md5 file exists
if [ ! -f $BKUP_PATH/$(basename $DB).md5 ]
then
    # md5 file does not exist
    # create md5 of DB and store it in BKUP_PATH
    md5sum $DB > $BKUP_PATH/$(basename $DB).md5
    exit 1
fi

# compare md5 file and DB md5
md5sum --status -c $BKUP_PATH/$(basename $DB).md5
match=$?

if [ $match -ne 0 ]; then
	# if different, do integrity check to make sure it hasn't been corrupted
	echo -ne $(date +"%Y-%m-%d %T --")" " >> $LOG
	sqlite3 -line $DB 'pragma integrity_check;' >> $LOG
	has_integrity=$?
	if [ $has_integrity -eq 0 ]; then
		# overwrite db file and make new checksum
		md5sum $DB > $BKUP_PATH/$(basename $DB).md5
		cp $DB $BKUP_PATH/$(basename $DB).bk
		copied=$?
		if [ $copied -ne 0 ]; then
			#failed to copy
			echo $(date +"%Y-%m-%d %T") -- Error writing backup file to $BKUP_PATH/$(basename $DB).bk >> $LOG
			exit 1
		fi
		echo $(date +"%Y-%m-%d %T") -- $DB has changed! Backup successful. >> $LOG
		exit 0
	else 
		# db is corrupt
		echo $(date +"%Y-%m-%d %T") -- $DB IS CORRUPT! Backup not overwritten. >> $LOG
		exit 1
	fi
fi

Hi... should this part should be if [ ! -w $BKUP_PATH ]?

# check to make sure it's writable
    if [ ! -w $DB ]

This is a great start. But there are a few issues with this script.

You never check to see if the DB Backup exists! You checked everything else (db file, directory, writeable, etc). I'm guessing you already had a db backup (.bk). This is only an issue if you are using the script to perform a first time backup but I'm guessing that's most people looking at this post! Put the following code above the md5 check.

# check to see if backup file exists
if [ ! -f $BKUP_PATH/$(basename $DB).bk ]
then
    cp $DB $BKUP_PATH/$(basename $DB).bk
    copied=$?

    if [ $copied -ne 0 ]
    then
        #failed to copy
        echo $(date +"%Y-%m-%d %T") -- Error writing backup file to $BKUP_PATH/$(basename $DB).bk >> $LOG
        exit 1
    fi

    echo $(date +"%Y-%m-%d %T") -- Wrote backup file to $BKUP_PATH/$(basename $DB).bk >> $LOG
fi

The next issue is a logic flaw. Your script will only initially make a md5sum because it assumes a backup exists. This means it will never make a backup until the md5 changes. You only have an if/else to match != 0 otherwise it is assumed to be corrupt (bad assumption). Just add the following code and you can simply write to the log and skip over.

elif [ $match == 0 ]
then
    echo $(date +"%Y-%m-%d %T") -- $DB has not changed! No backup performed. >> $LOG

Finally most people probably have a lot more than 1 db and potentially in multiple directories. Add a find command at the beginning of the script to check for files in a directory and write them to a file. Then put the whole script into a do while loop and run it over until it reads all backups found in said file.

find /directory/to/search -maxdepth 3 -name '*.db' > /path/to/output

while read p; do

DB=$p

... rest of script ...

done</path/to/output.txt

You also don't need to exit 0 when you get the expected output. This forces you to re-run the script over and over just to step through successful cases.

All together the script would look something like this.

find /directory/to/search -maxdepth 3 -name '*.db' > /path/to/output.txt

while read p; do

DB=$p
BKUP_PATH="/path/to/backups"
LOG="/path/to/backups/backup.log"

# check to see if $DB exists
if [ ! -f $DB ]
then
echo $(date +"%Y-%m-%d %T") -- cannot find the database at the following path: $DB >> $LOG
exit 1
fi

# check to make sure $BKUP_PATH exists and is writable
if [ ! -d $BKUP_PATH ]
then
# BKUP_PATH does not exist or is not a directory
echo $(date +"%Y-%m-%d %T") -- cannot find backup path: $BKUP_PATH >> $LOG
exit 1
else
# check to make sure it's writable
if [ ! -w $DB ]
then
    echo $(date +"%Y-%m-%d %T") -- BKUP Path is not writable: $BKUP_PATH >> $LOG
    exit 1
fi
fi

# check to see if backup file exists
if [ ! -f $BKUP_PATH/$(basename $DB).bk ]
then
cp $DB $BKUP_PATH/$(basename $DB).bk
copied=$?

if [ $copied -ne 0 ]
then
    #failed to copy
    echo $(date +"%Y-%m-%d %T") -- Error writing backup file to $BKUP_PATH/$(basename $DB).bk >> $LOG
    exit 1
fi

echo $(date +"%Y-%m-%d %T") -- Wrote backup file to $BKUP_PATH/$(basename $DB).bk >> $LOG
fi

# check to see if md5 file exists
if [ ! -f $BKUP_PATH/$(basename $DB).md5 ]
then
# md5 file does not exist
# create md5 of DB and store it in BKUP_PATH
md5sum $DB > $BKUP_PATH/$(basename $DB).md5
echo $(date +"%Y-%m-%d %T") -- Wrote MD5Sum to $BKUP_PATH >> $LOG

# compare md5 file and DB md5
md5sum --status -c $BKUP_PATH/$(basename $DB).md5
match=$?

if [ $match -ne 0 ]
then
   # if different, do integrity check to make sure it hasn't been corrupted
   echo -ne $(date +"%Y-%m-%d %T --")" " >> $LOG
   sqlite3 -line $DB 'pragma integrity_check;' >> $LOG
   has_integrity=$?

   if [ $has_integrity -eq 0 ]
   then
      # overwrite db file and make new checksum
      md5sum $DB > $BKUP_PATH/$(basename $DB).md5
      echo $(date +"%Y-%m-%d %T") -- Updated MD5Sum to $BKUP_PATH >> $LOG
      cp $DB $BKUP_PATH/$(basename $DB).bk
      copied=$?

      if [ $copied -ne 0 ]
      then
          #failed to copy
          echo $(date +"%Y-%m-%d %T") -- Error writing backup file to $BKUP_PATH/$(basename $DB).bk >> $LOG
          exit 1
      fi

      echo $(date +"%Y-%m-%d %T") -- $DB has changed! Backup successful. >> $LOG
   fi
elif [ $match == 0 ]
then
    echo $(date +"%Y-%m-%d %T") -- $DB has not changed! No backup performed. >> $LOG
else
    # db is corrupt
    echo $(date +"%Y-%m-%d %T") -- $DB IS CORRUPT! Backup not overwritten. >> $LOG
    exit 1
fi
fi

done</path/to/output.txt

which lang is being used ?

@pkatzman the last else should be moved before 'elif' ???

@coderjolly its a bash script.

This does not work on macOS; md5sum does not exist.