""" SQLite Database Backup Script Usage: python backup_db.py [backup|restore|list] """ import os import shutil import sqlite3 from datetime import datetime from pathlib import Path # Configuration INSTANCE_DIR = Path(__file__).parent / 'instance' DB_FILE = INSTANCE_DIR / 'machines.db' BACKUP_DIR = INSTANCE_DIR / 'backups' def ensure_backup_dir(): """Create backup directory if it doesn't exist""" BACKUP_DIR.mkdir(parents=True, exist_ok=True) def backup_database(): """Create a timestamped backup of the database""" if not DB_FILE.exists(): print(f"❌ Database not found: {DB_FILE}") return False ensure_backup_dir() timestamp = datetime.now().strftime('%Y%m%d_%H%M%S') backup_file = BACKUP_DIR / f'machines_backup_{timestamp}.db' try: # Method 1: Simple file copy (faster) shutil.copy2(DB_FILE, backup_file) # Get file size size = backup_file.stat().st_size size_mb = size / (1024 * 1024) print(f"✅ Backup created successfully!") print(f" File: {backup_file.name}") print(f" Size: {size_mb:.2f} MB") print(f" Location: {backup_file}") return True except Exception as e: print(f"❌ Backup failed: {e}") return False def backup_database_with_integrity(): """Create a backup using SQLite's built-in backup API (slower but safer)""" if not DB_FILE.exists(): print(f"❌ Database not found: {DB_FILE}") return False ensure_backup_dir() timestamp = datetime.now().strftime('%Y%m%d_%H%M%S') backup_file = BACKUP_DIR / f'machines_backup_{timestamp}.db' try: # Connect to source database source_conn = sqlite3.connect(str(DB_FILE)) # Connect to backup database backup_conn = sqlite3.connect(str(backup_file)) # Perform backup with backup_conn: source_conn.backup(backup_conn) source_conn.close() backup_conn.close() # Get file size size = backup_file.stat().st_size size_mb = size / (1024 * 1024) print(f"✅ Backup created successfully (with integrity check)!") print(f" File: {backup_file.name}") print(f" Size: {size_mb:.2f} MB") print(f" Location: {backup_file}") return True except Exception as e: print(f"❌ Backup failed: {e}") if backup_file.exists(): backup_file.unlink() # Delete partial backup return False def list_backups(): """List all available backups""" ensure_backup_dir() backups = sorted(BACKUP_DIR.glob('machines_backup_*.db'), reverse=True) if not backups: print("📂 No backups found") return print(f"\n📂 Available Backups ({len(backups)} total):") print("=" * 80) for i, backup in enumerate(backups, 1): size = backup.stat().st_size / (1024 * 1024) mtime = datetime.fromtimestamp(backup.stat().st_mtime) # Parse timestamp from filename try: parts = backup.stem.split('_') date_str = parts[-2] time_str = parts[-1] backup_date = datetime.strptime(f"{date_str}_{time_str}", "%Y%m%d_%H%M%S") date_display = backup_date.strftime("%Y-%m-%d %H:%M:%S") except: date_display = mtime.strftime("%Y-%m-%d %H:%M:%S") print(f"{i:2d}. {backup.name}") print(f" Date: {date_display}") print(f" Size: {size:.2f} MB") print() def restore_database(backup_name=None): """Restore database from a backup""" ensure_backup_dir() backups = sorted(BACKUP_DIR.glob('machines_backup_*.db'), reverse=True) if not backups: print("❌ No backups found") return False # If no backup specified, show list and ask if not backup_name: list_backups() print("=" * 80) choice = input("Enter backup number to restore (or 'q' to quit): ").strip() if choice.lower() == 'q': print("❌ Restore cancelled") return False try: index = int(choice) - 1 if index < 0 or index >= len(backups): print("❌ Invalid backup number") return False backup_file = backups[index] except ValueError: print("❌ Invalid input") return False else: # Find backup by name backup_file = BACKUP_DIR / backup_name if not backup_file.exists(): print(f"❌ Backup not found: {backup_name}") return False # Confirm restore print(f"\n⚠️ WARNING: This will replace your current database!") print(f" Current: {DB_FILE}") print(f" Backup: {backup_file.name}") confirm = input("\nType 'yes' to confirm restore: ").strip().lower() if confirm != 'yes': print("❌ Restore cancelled") return False try: # Create a safety backup of current database if DB_FILE.exists(): safety_backup = BACKUP_DIR / f'machines_before_restore_{datetime.now().strftime("%Y%m%d_%H%M%S")}.db' shutil.copy2(DB_FILE, safety_backup) print(f"✅ Safety backup created: {safety_backup.name}") # Restore from backup shutil.copy2(backup_file, DB_FILE) print(f"✅ Database restored successfully!") print(f" Restored from: {backup_file.name}") print(f"\n⚠️ Remember to restart your Flask server!") return True except Exception as e: print(f"❌ Restore failed: {e}") return False def cleanup_old_backups(keep_count=10): """Keep only the most recent N backups""" ensure_backup_dir() backups = sorted(BACKUP_DIR.glob('machines_backup_*.db'), reverse=True) if len(backups) <= keep_count: print(f"✅ No cleanup needed (found {len(backups)} backups, keeping {keep_count})") return to_delete = backups[keep_count:] print(f"\n🗑️ Cleanup: Keeping {keep_count} most recent backups") print(f" Deleting {len(to_delete)} old backups...") for backup in to_delete: try: backup.unlink() print(f" ✓ Deleted: {backup.name}") except Exception as e: print(f" ✗ Failed to delete {backup.name}: {e}") print(f"✅ Cleanup complete!") def main(): """Main entry point""" import sys if len(sys.argv) < 2: print("Usage: python backup_db.py [backup|restore|list|cleanup]") print("\nCommands:") print(" backup - Create a new backup") print(" restore - Restore from a backup") print(" list - List all available backups") print(" cleanup - Delete old backups (keep 10 most recent)") return command = sys.argv[1].lower() if command == 'backup': backup_database() elif command == 'backup-safe': backup_database_with_integrity() elif command == 'restore': backup_name = sys.argv[2] if len(sys.argv) > 2 else None restore_database(backup_name) elif command == 'list': list_backups() elif command == 'cleanup': keep = int(sys.argv[2]) if len(sys.argv) > 2 else 10 cleanup_old_backups(keep) else: print(f"❌ Unknown command: {command}") print(" Use: backup, restore, list, or cleanup") if __name__ == '__main__': main()