SQLite Backup and Restore Guide
Backup Methods
File Copy (Simple)
SQLite databases are single files, so simple copy works when database is not being written to:
# Ensure no writes during copy
cp /path/to/database.db /backup/database_$(date +%Y%m%d).dbsqlite3 .backup Command (Safe)
Uses SQLite's online backup API - safe during active connections:
sqlite3 /path/to/database.db ".backup '/backup/database.db'"With Compression:
sqlite3 /path/to/database.db ".backup '/dev/stdout'" | gzip > /backup/database.db.gzsqlite3 .dump Command (SQL Export)
Exports as SQL statements - portable and inspectable:
sqlite3 /path/to/database.db .dump > /backup/database.sql
# With compression
sqlite3 /path/to/database.db .dump | gzip > /backup/database.sql.gzVACUUM INTO (SQLite 3.27+)
Creates optimized copy:
sqlite3 /path/to/database.db "VACUUM INTO '/backup/database_optimized.db'"Backup Comparison
| Method | During Writes | Portable | Size | Speed |
|---|---|---|---|---|
| File copy | Risky | Yes | Original | Fast |
| .backup | Safe | Yes | Original | Fast |
| .dump | Safe | Very | Larger | Slower |
| VACUUM INTO | Safe | Yes | Optimized | Medium |
Restore Methods
From File Copy
# Stop application
systemctl stop myapp
# Replace database
cp /backup/database.db /path/to/database.db
# Fix permissions
chown app:app /path/to/database.db
chmod 644 /path/to/database.db
# Start application
systemctl start myappFrom SQL Dump
# Create new database from dump
sqlite3 /path/to/database_new.db < /backup/database.sql
# From compressed
gunzip -c /backup/database.sql.gz | sqlite3 /path/to/database_new.dbSelective Restore
Single Table from Dump:
# Extract CREATE TABLE and INSERT statements for specific table
grep -E "^(CREATE TABLE|INSERT INTO) \"?users" /backup/database.sql > users.sql
sqlite3 /path/to/database.db < users.sqlProduction Backup Script
#!/bin/bash
set -euo pipefail
# Configuration
DB_PATH="/var/lib/myapp/database.db"
BACKUP_DIR="/var/backups/sqlite"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/database_${DATE}.db"
LOG_FILE="${BACKUP_DIR}/backup.log"
mkdir -p "$BACKUP_DIR"
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}
log "Starting backup of $DB_PATH"
# Check database exists
if [ ! -f "$DB_PATH" ]; then
log "ERROR: Database not found: $DB_PATH"
exit 1
fi
# Check database integrity before backup
if ! sqlite3 "$DB_PATH" "PRAGMA integrity_check;" | grep -q "^ok$"; then
log "WARNING: Database integrity check failed"
fi
# Perform backup using online backup API
if sqlite3 "$DB_PATH" ".backup '$BACKUP_FILE'" 2>> "$LOG_FILE"; then
SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
log "Backup completed: $BACKUP_FILE ($SIZE)"
# Verify backup
if sqlite3 "$BACKUP_FILE" "PRAGMA integrity_check;" | grep -q "^ok$"; then
log "Backup verification passed"
else
log "ERROR: Backup verification failed"
exit 1
fi
# Compress backup
gzip "$BACKUP_FILE"
log "Compressed to: ${BACKUP_FILE}.gz"
else
log "ERROR: Backup failed"
exit 1
fi
# Cleanup old backups
log "Cleaning backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -name "*.db.gz" -mtime "+$RETENTION_DAYS" -delete
log "Backup process completed"Incremental Backup Strategy
SQLite doesn't have built-in incremental backup, but you can use these strategies:
WAL Mode Backup
# Enable WAL mode (run once)
sqlite3 /path/to/database.db "PRAGMA journal_mode=WAL;"
# Checkpoint before backup to merge WAL into main file
sqlite3 /path/to/database.db "PRAGMA wal_checkpoint(TRUNCATE);"
# Then backup
sqlite3 /path/to/database.db ".backup '/backup/database.db'"Change Tracking with Triggers
-- Create change log table
CREATE TABLE IF NOT EXISTS change_log (
id INTEGER PRIMARY KEY,
table_name TEXT,
row_id INTEGER,
action TEXT,
changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Create trigger for each table
CREATE TRIGGER users_changes AFTER INSERT OR UPDATE OR DELETE ON users
BEGIN
INSERT INTO change_log (table_name, row_id, action)
VALUES ('users', COALESCE(NEW.id, OLD.id),
CASE WHEN NEW.id IS NULL THEN 'DELETE'
WHEN OLD.id IS NULL THEN 'INSERT'
ELSE 'UPDATE' END);
END;Performance Optimization
Before Backup
# Optimize database
sqlite3 /path/to/database.db "VACUUM;"
sqlite3 /path/to/database.db "ANALYZE;"WAL Mode for Concurrent Access
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;Database Integrity Check
# Quick integrity check
sqlite3 /path/to/database.db "PRAGMA quick_check;"
# Full integrity check
sqlite3 /path/to/database.db "PRAGMA integrity_check;"
# Check foreign keys
sqlite3 /path/to/database.db "PRAGMA foreign_key_check;"Troubleshooting
| Issue | Solution |
|---|---|
| "database is locked" | Wait for transactions, use WAL mode |
| Backup too slow | Use .backup instead of .dump |
| Corrupted backup | Run integrity check on source first |
| Permission denied | Check file and directory permissions |
| Disk full | Clean old backups, check df -h |
| Large WAL file | Run PRAGMA wal_checkpoint(TRUNCATE) |
Restore Verification
# After restore, verify
sqlite3 /path/to/restored.db <<EOF
PRAGMA integrity_check;
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;
.tables
EOFPython Backup Script
#!/usr/bin/env python3
"""SQLite backup with verification."""
import sqlite3
import shutil
from pathlib import Path
from datetime import datetime
def backup_database(source: str, backup_dir: str) -> str:
"""Create verified backup of SQLite database."""
source_path = Path(source)
backup_path = Path(backup_dir) / f"{source_path.stem}_{datetime.now():%Y%m%d_%H%M%S}.db"
# Ensure backup directory exists
backup_path.parent.mkdir(parents=True, exist_ok=True)
# Use SQLite backup API
source_conn = sqlite3.connect(source)
backup_conn = sqlite3.connect(str(backup_path))
with backup_conn:
source_conn.backup(backup_conn)
source_conn.close()
# Verify backup
verify_conn = sqlite3.connect(str(backup_path))
result = verify_conn.execute("PRAGMA integrity_check").fetchone()
verify_conn.close()
if result[0] != "ok":
raise RuntimeError(f"Backup integrity check failed: {result}")
return str(backup_path)
if __name__ == "__main__":
import sys
if len(sys.argv) != 3:
print(f"Usage: {sys.argv[0]} <source.db> <backup_dir>")
sys.exit(1)
backup_file = backup_database(sys.argv[1], sys.argv[2])
print(f"Backup created: {backup_file}")