Generate Robust Database Backup Scripts

This skill provides expertise in creating reliable, production-ready database backup scripts across MySQL, PostgreSQL, MongoDB, and SQL Server, implementing comprehensive backup strategies with proper error handling, compression, encryption, retention policies, and monitoring. Core backup principles cover the 3-2-1 rule (3 copies of data, 2 different media types, 1 offsite), Recovery Time Objective and Recovery Point Objective definitions, backup types (full, incremental, differential, point-in-time recovery), and transactional consistency during backup operations. Critical script components cover pre-flight checks and system health verification, atomic backup operations with proper locking, compression and encryption for storage efficiency and security, backup integrity verification, automated cleanup with configurable retention, and comprehensive logging/alerting.

The MySQL backup script pattern demonstrates a full production bash script: connection and disk-space pre-checks, mysqldump with --single-transaction --routines --triggers --all-databases --master-data=2 --flush-logs for a consistent full dump, compression via pigz and AES-256-CBC encryption via openssl, integrity verification by decrypting and gzip-testing the encrypted archive, JSON metadata generation (timestamp, hostname, database list, size, SHA-256 checksum), retention-based cleanup of old backups, and an EXIT trap that sends a Slack alert on any failure. The PostgreSQL backup pattern covers point-in-time recovery support: pg_basebackup with fast checkpoint and tar/gzip output, forcing a WAL file switch via pg_switch_wal() to archive the current WAL segment, and generating a recovery configuration file documenting the exact restore procedure (stop the service, move the data directory, extract the base backup, create a recovery signal file).

Use this skill when building or hardening a production database backup script that needs proper error handling, encryption, integrity verification, and retention management - not a bare mysqldump cron job. It is well suited to production MySQL or PostgreSQL instances needing point-in-time recovery capability. It is not meant for development databases with no real recovery requirement, or for backup strategies already handled by a managed database service's built-in backup feature.

Input: the database system (MySQL, PostgreSQL, MongoDB, SQL Server), retention requirements, and encryption/alerting preferences.

Output: a complete, production-ready backup script with pre-checks, compression/encryption, integrity verification, retention cleanup, and failure alerting. Example MySQL dump command with consistency flags:

mysqldump -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" \
    --single-transaction \
    --routines --triggers \
    --all-databases \
    --master-data=2 \
    --flush-logs > "$TEMP_DUMP"

Works with mysqldump/pg_basebackup for database dumps, pigz/gzip for compression, openssl for AES-256 encryption, and Slack webhooks for failure alerting.

DBAs and infrastructure engineers building production database backup automation, and teams that need encrypted, verified, retention-managed backups with point-in-time recovery capability.