Index of every page on this site: llms.txt
All skills
jeremylongshore avatar

/automating-database-backups

@820dfc8
Run it yourself

Automate database backup processes with scheduling, compression, and encryption. Supports PostgreSQL (pg_dump), MySQL (mysqldump), MongoDB (mongodump), and SQLite. Generates production-ready backup scripts with retention policies and restore procedures. Trigger: "automate database backups", "schedule backups", "create backup script", "disaster recovery". Use when working with automating database backups. Trigger with 'automating', 'database', 'backups'.

Use this Skill: https://skilld.dev/gh/jeremylongshore/tons-of-skills-marketplace/automating-database-backups

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

referencessqlite_backup_restore.md

≈1.7k tokens on demand. Your agent reads this file only when SKILL.md points to it.

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).db

sqlite3 .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.gz

sqlite3 .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.gz

VACUUM 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 myapp

From 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.db

Selective 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.sql

Production 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
EOF

Python 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}")

Source: SKILL.md on GitHub

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

2 warnings2mo3 checks · Risk MEDIUM
  • Gen Agent Trust Hub2mo

    The skill automates database backups by generating and scheduling shell scripts. While performing its intended function, it handles sensitive credentials, modifies system persistence via cron, and utilizes sudo for administrative tasks. The script generation logic is vulnerable to command injection if malicious database parameters are provided, and the documentation suggests insecure credential handling practices.

  • Socket2mo

    2 alerts: gptAnomaly

  • Snyk2mo

    Risk: LOW · No issues

Signed by skilld at 820dfc8. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub yesterday.

Activeupdated last month
What it can do
Reads files Edits files Runs commands
version
1.27.0
author
Jeremy Longshore <jeremy@intentsolutions.io>
tags
[
  "database",
  "postgresql",
  "mysql",
  "mongodb"
]
All 10 allowed tools
ReadWriteEditGrepGlobBash(pg_dump:*)Bash(mysqldump:*)Bash(mongodump:*)Bash(cron:*)Bash(gpg:*)
Other metadata
compatibility
Designed for Claude Code

README badge

README badge for jeremylongshore/tons-of-skills-marketplace/automating-database-backups