SQLite database rolling backup script with rclone

backup.sh

#!/bin/bash

DB_PATH="/path/to/sqlite/database.db"   # Path to your SQLite database
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")      # Timestamp for the backup
ROLLING_LIMIT=30                        # Number of backups to keep
BACKUP_DIR="/tmp"                       # Temporary local backup directory
BACKUP_NAME="backup_$TIMESTAMP.sqlite"  # Backup filename
REMOTE_NAME="remote"                    # Name of your rclone remote
REMOTE_PATH="backups"                   # Remote bucket or folder

# Ensure the database file exists
if [ ! -f "$DB_PATH" ]; then
  echo "Error: Database file not found at $DB_PATH"
  exit 1
fi

# Create a SQLite backup
sqlite3 "$DB_PATH" ".backup '$BACKUP_DIR/$BACKUP_NAME'"
if [ $? -ne 0 ]; then
  echo "Error: Failed to create SQLite backup."
  exit 1
fi
echo "Backup created: $BACKUP_DIR/$BACKUP_NAME"

# Upload the backup to the remote storage
rclone copy "$BACKUP_DIR/$BACKUP_NAME" "$REMOTE_NAME:$REMOTE_PATH"
if [ $? -ne 0 ]; then
  echo "Error: Failed to upload backup to remote storage."
  exit 1
fi
echo "Backup uploaded to remote: $REMOTE_NAME:$REMOTE_PATH/$BACKUP_NAME"

# Clean up old backups on the remote storage
OLD_BACKUPS=$(rclone lsl "$REMOTE_NAME:$REMOTE_PATH" | grep "backup_" | awk '{print $NF}' | sort | head -n -"$ROLLING_LIMIT")
for OLD_BACKUP in $OLD_BACKUPS; do
  rclone delete "$REMOTE_NAME:$REMOTE_PATH/$OLD_BACKUP"
  if [ $? -eq 0 ]; then
    echo "Deleted old backup: $OLD_BACKUP"
  else
    echo "Error: Failed to delete old backup: $OLD_BACKUP"
  fi
done

# Remove local backup file to save space
rm -f "$BACKUP_DIR/$BACKUP_NAME"
echo "Local backup file removed: $BACKUP_DIR/$BACKUP_NAME"

echo "Backup process completed successfully."