Skip to content

Backup and Restore

Jump to bottom
Temp edited this page Sep 23, 2025 · 1 revision

Backup & Restore Operations

Last Updated: September 23, 2025 1:48 PM EST

The SQLite MCP Server provides enterprise-grade backup and restore capabilities using SQLite's native backup API for atomic, consistent operations.

🔧 Available Backup Tools

Tool Description
backup_database Create database backups to files
restore_database Restore database from backup files
verify_backup Verify integrity of backup files

💾 Backup Operations

Create Database Backups

backup_database({
  "backup_path": "./backups/database_2025-09-23.db",
  "overwrite": false  // Optional: prevent accidental overwrites
})

Features:

  • Atomic Operations: Uses SQLite's native backup API
  • Consistent Snapshots: Backup reflects a single point in time
  • Directory Creation: Automatically creates backup directories
  • Overwrite Protection: Prevents accidental file overwrites
  • Progress Monitoring: Track backup progress for large databases

Automated Backup Scheduling

// Daily backup with timestamp
backup_database({
  "backup_path": `./backups/daily_backup_${new Date().toISOString().split('T')[0]}.db`,
  "compress": true,
  "verify_after": true
})

// Weekly full backup
backup_database({
  "backup_path": "./backups/weekly/full_backup.db",
  "overwrite": true,
  "metadata": {
    "type": "weekly_full",
    "created_by": "automated_system",
    "retention_days": 90
  }
})

Incremental Backup Strategy

// Create baseline full backup
backup_database({
  "backup_path": "./backups/baseline_full.db",
  "type": "full",
  "create_checkpoint": true
})

// Incremental backup (WAL files)
backup_database({
  "backup_path": "./backups/incremental_001.wal",
  "type": "incremental",
  "since_checkpoint": true
})

🔍 Backup Verification

Verify Backup Integrity

verify_backup({
  "backup_path": "./backups/database_2025-09-23.db"
})

Returns comprehensive verification:

  • File size and checksums
  • SQLite integrity check results
  • Table count and structure validation
  • Schema version compatibility
  • Corruption detection
  • Metadata validation

Advanced Verification

// Deep verification with content sampling
verify_backup({
  "backup_path": "./backups/database_2025-09-23.db",
  "deep_check": true,
  "sample_data": true,
  "check_foreign_keys": true,
  "validate_indexes": true
})

// Verify against source database
verify_backup({
  "backup_path": "./backups/database_2025-09-23.db",
  "compare_with_source": true,
  "tolerance": {
    "row_count_diff": 0,
    "schema_changes": false
  }
})

Backup Health Monitoring

// Check all backups in directory
verify_backup({
  "backup_directory": "./backups/",
  "recursive": true,
  "generate_report": true,
  "cleanup_corrupted": false  // Just report, don't delete
})

🔄 Restore Operations

Basic Database Restore

restore_database({
  "backup_path": "./backups/database_2025-09-23.db",
  "confirm": true  // Required for safety
})

Safety Features:

  • Confirmation Required: Explicit confirm=true prevents accidents
  • Pre-restore Backup: Current database automatically backed up
  • Integrity Verification: Backup validated before restore
  • Atomic Operations: All-or-nothing restore process
  • Rollback Capability: Can revert if restore fails

Advanced Restore Options

// Restore with custom settings
restore_database({
  "backup_path": "./backups/database_2025-09-23.db",
  "confirm": true,
  "pre_restore_backup": "./backups/pre_restore_backup.db",
  "verify_before": true,
  "verify_after": true,
  "preserve_wal": true
})

// Partial restore (specific tables)
restore_database({
  "backup_path": "./backups/database_2025-09-23.db",
  "confirm": true,
  "tables_only": ["users", "orders", "products"],
  "merge_strategy": "replace"  // replace, merge, skip
})

Point-in-Time Recovery

// Restore to specific timestamp
restore_database({
  "backup_path": "./backups/baseline_full.db",
  "confirm": true,
  "apply_wal_files": [
    "./backups/incremental_001.wal",
    "./backups/incremental_002.wal"
  ],
  "stop_at_timestamp": "2025-09-23T10:30:00Z"
})

🛡️ Safety Features

Pre-Restore Safety Checks

// Comprehensive safety validation
restore_database({
  "backup_path": "./backups/database_2025-09-23.db",
  "confirm": true,
  "safety_checks": {
    "verify_backup_integrity": true,
    "check_schema_compatibility": true,
    "validate_foreign_keys": true,
    "estimate_restore_time": true,
    "check_disk_space": true
  }
})

Disaster Recovery Planning

// Test restore capability (dry run)
restore_database({
  "backup_path": "./backups/database_2025-09-23.db",
  "confirm": true,
  "dry_run": true,  // Don't actually restore
  "test_restore_path": "./temp/restore_test.db",
  "cleanup_after_test": true
})

// Emergency restore with minimal checks
restore_database({
  "backup_path": "./backups/emergency_backup.db",
  "confirm": true,
  "emergency_mode": true,  // Skip some safety checks for speed
  "force_restore": true
})

📋 Backup Strategies

3-2-1 Backup Strategy

// 1. Local daily backups (3 copies)
backup_database({
  "backup_path": "./backups/local/daily_backup.db",
  "overwrite": true,
  "retention": {
    "keep_daily": 7,
    "keep_weekly": 4,
    "keep_monthly": 12
  }
})

// 2. Secondary location (2nd copy)
backup_database({
  "backup_path": "/secondary/storage/backup.db",
  "compress": true,
  "encrypt": true
})

// 3. Off-site backup (1 off-site)
backup_database({
  "backup_path": "./cloud_sync/backup.db",
  "upload_to_cloud": true,
  "cloud_provider": "s3"
})

Production Backup Schedule

// Hot backup during operation
backup_database({
  "backup_path": "./backups/hot_backup.db",
  "allow_concurrent_access": true,  // Don't lock database
  "consistency_check": "eventual",   // Allow slight inconsistency
  "priority": "low"  // Don't impact performance
})

// Cold backup during maintenance window
backup_database({
  "backup_path": "./backups/cold_backup.db",
  "exclusive_lock": true,  // Ensure consistency
  "verify_after": true,
  "compress": true,
  "optimize_before": true  // Run VACUUM first
})

🔧 Advanced Backup Features

Compression and Encryption

// Compressed backup
backup_database({
  "backup_path": "./backups/compressed_backup.db.gz",
  "compress": true,
  "compression_level": 6,  // 1-9, higher = better compression
  "estimate_size": true
})

// Encrypted backup
backup_database({
  "backup_path": "./backups/encrypted_backup.db",
  "encrypt": true,
  "encryption_key": "your-encryption-key",
  "encryption_algorithm": "AES-256"
})

Metadata and Versioning

// Backup with rich metadata
backup_database({
  "backup_path": "./backups/versioned_backup_v1.2.3.db",
  "metadata": {
    "version": "1.2.3",
    "environment": "production",
    "created_by": "backup_system",
    "description": "Pre-deployment backup",
    "tags": ["release", "stable"],
    "retention_policy": "keep_for_1_year",
    "related_tickets": ["TICKET-123", "TICKET-456"]
  }
})

Custom Backup Hooks

// Backup with pre/post hooks
backup_database({
  "backup_path": "./backups/custom_backup.db",
  "pre_backup_hook": {
    "command": "python scripts/pre_backup.py",
    "timeout": 300
  },
  "post_backup_hook": {
    "command": "python scripts/post_backup.py",
    "pass_backup_path": true
  }
})

💡 Real-World Scenarios

Development Environment

// Quick development backup before major changes
backup_database({
  "backup_path": "./dev_backups/before_feature_branch.db",
  "quick_backup": true,
  "skip_verification": true  // Speed over safety in dev
})

// Restore to clean state
restore_database({
  "backup_path": "./dev_backups/clean_state.db",
  "confirm": true,
  "reset_auto_increment": true
})

Staging Environment

// Production data refresh in staging
restore_database({
  "backup_path": "./prod_backups/latest_production.db",
  "confirm": true,
  "sanitize_data": true,  // Remove sensitive data
  "update_config": {
    "environment": "staging",
    "debug_mode": true
  }
})

Production Environment

// Critical production backup before deployment
backup_database({
  "backup_path": "./critical_backups/pre_deployment.db",
  "priority": "critical",
  "verify_immediately": true,
  "create_multiple_copies": 3,
  "notify_on_completion": ["admin@company.com"],
  "block_until_complete": true
})

// Emergency rollback
restore_database({
  "backup_path": "./critical_backups/pre_deployment.db",
  "confirm": true,
  "emergency_mode": true,
  "notify_stakeholders": true
})

🎯 Best Practices

1. Regular Backup Schedule

// Automated daily backups
const backupSchedule = {
  "daily": {
    "time": "02:00",
    "retention": 7
  },
  "weekly": {
    "day": "sunday",
    "time": "01:00", 
    "retention": 4
  },
  "monthly": {
    "day": 1,
    "time": "00:00",
    "retention": 12
  }
};

2. Test Restore Procedures

// Monthly restore test
restore_database({
  "backup_path": "./backups/monthly_backup.db",
  "confirm": true,
  "test_environment": true,
  "validate_data_integrity": true,
  "document_results": true
})

3. Monitor Backup Health

// Regular backup verification
verify_backup({
  "backup_directory": "./backups/",
  "check_all": true,
  "alert_on_corruption": true,
  "cleanup_old_backups": true,
  "retention_policy": "30_days"
})

4. Document Recovery Procedures

// Generate recovery documentation
backup_database({
  "backup_path": "./backups/documented_backup.db",
  "generate_recovery_guide": true,
  "include_environment_info": true,
  "recovery_instructions": "./docs/recovery_procedure.md"
})

📚 Related Pages

💾 Backup Tip: The best backup is the one you've tested restoring from. Regular restore testing ensures your backups are actually usable when you need them most.

Clone this wiki locally