Backup and Recovery

This document provides comprehensive guidance for backup and disaster recovery procedures for the Stratpoint Timesheet Application, ensuring business continuity and data protection across all system components.

Backup Strategy Overview

Backup Architecture

graph TB
    A[Application Data] --> B[Backup Orchestrator]
    C[Database] --> B
    D[File Storage] --> B
    E[Configuration] --> B

    B --> F[Local Backups]
    B --> G[Cloud Storage]
    B --> H[Offsite Storage]

    F --> I[Daily Backups]
    F --> J[Weekly Backups]
    F --> K[Monthly Backups]

    G --> L[AWS S3]
    G --> M[Azure Blob]

    H --> N[Geographic Replication]
    H --> O[Disaster Recovery Site]

Backup Types and Retention

Backup Type	Frequency	Retention	Storage Location
Database Full	Daily	30 days local, 1 year cloud	Local + S3
Database Incremental	Every 6 hours	7 days	Local + S3
Application Files	Daily	30 days local, 6 months cloud	Local + S3
Configuration	Weekly	90 days local, 1 year cloud	Local + S3
System State	Weekly	30 days local, 3 months cloud	Local + S3
Archive	Monthly	7 years	S3 Glacier

Database Backup and Recovery

Automated Database Backup

#!/bin/bash
# scripts/backup/database_backup.sh

set -e

# Configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
source "$SCRIPT_DIR/../config/backup.conf"

# Logging
LOG_FILE="/var/log/backup/database_$(date +%Y%m%d).log"
mkdir -p "$(dirname "$LOG_FILE")"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# Backup configuration
DB_HOST="${DB_HOST:-localhost}"
DB_NAME="${DB_NAME:-stratpoint_timesheet}"
DB_USER="${DB_BACKUP_USER:-timesheet_backup}"
DB_PASSWORD="${DB_BACKUP_PASSWORD}"
BACKUP_DIR="${BACKUP_DIR:-/backups/database}"
S3_BUCKET="${S3_BACKUP_BUCKET:-stratpoint-timesheet-backups}"

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Generate backup filename
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/db_full_$TIMESTAMP.sql"
COMPRESSED_FILE="$BACKUP_FILE.gz"

log "Starting database backup"

# Pre-backup validation
if ! mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -e "SELECT 1;" > /dev/null 2>&1; then
    log "ERROR: Database connection failed"
    exit 1
fi

# Create full backup with consistency
log "Creating full database backup"
mysqldump \
    --host="$DB_HOST" \
    --user="$DB_USER" \
    --password="$DB_PASSWORD" \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --hex-blob \
    --quick \
    --lock-tables=false \
    --add-drop-database \
    --flush-logs \
    --master-data=2 \
    --databases "$DB_NAME" > "$BACKUP_FILE" 2>> "$LOG_FILE"

if [ $? -ne 0 ]; then
    log "ERROR: Database backup failed"
    exit 1
fi

# Compress backup
log "Compressing backup file"
gzip "$BACKUP_FILE"

# Verify backup integrity
log "Verifying backup integrity"
if gunzip -t "$COMPRESSED_FILE" 2>> "$LOG_FILE"; then
    log "Backup compression verified successfully"
else
    log "ERROR: Backup compression verification failed"
    exit 1
fi

# Calculate checksums
MD5_CHECKSUM=$(md5sum "$COMPRESSED_FILE" | awk '{print $1}')
SHA256_CHECKSUM=$(sha256sum "$COMPRESSED_FILE" | awk '{print $1}')

# Create metadata file
METADATA_FILE="$BACKUP_DIR/db_full_$TIMESTAMP.metadata"
cat > "$METADATA_FILE" << EOF
{
    "backup_type": "database_full",
    "timestamp": "$TIMESTAMP",
    "database": "$DB_NAME",
    "file_size": $(stat -c%s "$COMPRESSED_FILE"),
    "md5_checksum": "$MD5_CHECKSUM",
    "sha256_checksum": "$SHA256_CHECKSUM",
    "mysql_version": "$(mysql --version)",
    "backup_method": "mysqldump",
    "compression": "gzip"
}
EOF

# Upload to cloud storage
log "Uploading backup to cloud storage"
aws s3 cp "$COMPRESSED_FILE" "s3://$S3_BUCKET/database/daily/" \
    --storage-class STANDARD_IA \
    --metadata "md5=$MD5_CHECKSUM,sha256=$SHA256_CHECKSUM"

aws s3 cp "$METADATA_FILE" "s3://$S3_BUCKET/database/daily/"

# Weekly and monthly backups
if [ $(date +%u) -eq 7 ]; then
    log "Creating weekly backup copy"
    aws s3 cp "$COMPRESSED_FILE" "s3://$S3_BUCKET/database/weekly/"
    aws s3 cp "$METADATA_FILE" "s3://$S3_BUCKET/database/weekly/"
fi

if [ $(date +%d) -eq 01 ]; then
    log "Creating monthly backup copy"
    aws s3 cp "$COMPRESSED_FILE" "s3://$S3_BUCKET/database/monthly/" \
        --storage-class GLACIER
    aws s3 cp "$METADATA_FILE" "s3://$S3_BUCKET/database/monthly/"
fi

# Cleanup old local backups
log "Cleaning up old local backups"
find "$BACKUP_DIR" -name "db_full_*.sql.gz" -mtime +30 -delete
find "$BACKUP_DIR" -name "db_full_*.metadata" -mtime +30 -delete

# Log completion
BACKUP_SIZE=$(du -h "$COMPRESSED_FILE" | cut -f1)
log "Database backup completed successfully"
log "Backup file: $COMPRESSED_FILE"
log "Backup size: $BACKUP_SIZE"
log "MD5: $MD5_CHECKSUM"
log "SHA256: $SHA256_CHECKSUM"

# Send notification
send_notification "Database backup completed" "Size: $BACKUP_SIZE, File: $(basename $COMPRESSED_FILE)"

Database Recovery Procedures

#!/bin/bash
# scripts/recovery/database_recovery.sh

set -e

# Configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
source "$SCRIPT_DIR/../config/backup.conf"

# Parameters
BACKUP_FILE="$1"
RECOVERY_TYPE="${2:-full}"  # full, point-in-time
RECOVERY_TIME="$3"          # For point-in-time recovery

if [ -z "$BACKUP_FILE" ]; then
    echo "Usage: $0 <backup_file> [recovery_type] [recovery_time]"
    echo "Recovery types: full, point-in-time"
    echo "Recovery time format: 'YYYY-MM-DD HH:MM:SS'"
    exit 1
fi

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

# Pre-recovery validation
log "Starting database recovery process"
log "Backup file: $BACKUP_FILE"
log "Recovery type: $RECOVERY_TYPE"

if [ "$RECOVERY_TYPE" = "point-in-time" ] && [ -z "$RECOVERY_TIME" ]; then
    log "ERROR: Recovery time required for point-in-time recovery"
    exit 1
fi

# Verify backup file
if [ ! -f "$BACKUP_FILE" ]; then
    log "ERROR: Backup file not found: $BACKUP_FILE"
    exit 1
fi

# Verify backup integrity
log "Verifying backup integrity"
if [[ "$BACKUP_FILE" == *.gz ]]; then
    if ! gunzip -t "$BACKUP_FILE"; then
        log "ERROR: Backup file integrity check failed"
        exit 1
    fi
else
    if ! head -n 1 "$BACKUP_FILE" | grep -q "MySQL dump"; then
        log "ERROR: Invalid backup file format"
        exit 1
    fi
fi

# Stop application services
log "Stopping application services"
systemctl stop timesheet-app
systemctl stop timesheet-queue
systemctl stop nginx

# Create recovery database
RECOVERY_DB="${DB_NAME}_recovery_$(date +%Y%m%d_%H%M%S)"
log "Creating recovery database: $RECOVERY_DB"

mysql -u root -p << EOF
CREATE DATABASE $RECOVERY_DB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
EOF

# Restore from backup
log "Restoring database from backup"
if [[ "$BACKUP_FILE" == *.gz ]]; then
    gunzip -c "$BACKUP_FILE" | sed "s/$DB_NAME/$RECOVERY_DB/g" | mysql -u root -p
else
    sed "s/$DB_NAME/$RECOVERY_DB/g" "$BACKUP_FILE" | mysql -u root -p
fi

# Point-in-time recovery
if [ "$RECOVERY_TYPE" = "point-in-time" ]; then
    log "Performing point-in-time recovery to: $RECOVERY_TIME"

    # Find and apply binary logs
    BINLOG_DIR="/var/log/mysql"
    BACKUP_TIME=$(mysql -u root -p -e "SELECT backup_time FROM $RECOVERY_DB.backup_metadata ORDER BY id DESC LIMIT 1;" -s -N)

    for binlog in $(ls -1 "$BINLOG_DIR"/mysql-bin.* | sort); do
        log "Processing binary log: $binlog"

        mysqlbinlog \
            --start-datetime="$BACKUP_TIME" \
            --stop-datetime="$RECOVERY_TIME" \
            --database="$DB_NAME" \
            "$binlog" | sed "s/$DB_NAME/$RECOVERY_DB/g" | mysql -u root -p
    done
fi

# Validate recovered data
log "Validating recovered data"
TABLE_COUNT=$(mysql -u root -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$RECOVERY_DB';" -s -N)
log "Recovered tables: $TABLE_COUNT"

# Data consistency checks
mysql -u root -p "$RECOVERY_DB" << EOF
-- Check for orphaned records
SELECT 'Orphaned timelogs' as check_type, COUNT(*) as count
FROM timelogs t
LEFT JOIN users u ON t.user_id = u.id
WHERE u.id IS NULL;

SELECT 'Orphaned project assignments' as check_type, COUNT(*) as count
FROM project_users pu
LEFT JOIN users u ON pu.user_id = u.id
LEFT JOIN projects p ON pu.project_id = p.id
WHERE u.id IS NULL OR p.id IS NULL;
EOF

# Prompt for confirmation
read -p "Recovery validation complete. Replace production database? (yes/no): " confirm
if [ "$confirm" != "yes" ]; then
    log "Recovery cancelled by user"
    mysql -u root -p -e "DROP DATABASE $RECOVERY_DB;"
    exit 1
fi

# Replace production database
log "Replacing production database"
mysql -u root -p << EOF
DROP DATABASE $DB_NAME;
CREATE DATABASE $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
EOF

mysqldump -u root -p "$RECOVERY_DB" | mysql -u root -p "$DB_NAME"

# Cleanup recovery database
mysql -u root -p -e "DROP DATABASE $RECOVERY_DB;"

# Start application services
log "Starting application services"
systemctl start nginx
systemctl start timesheet-app
systemctl start timesheet-queue

# Verify application
log "Verifying application functionality"
sleep 10
if curl -f http://localhost/health > /dev/null 2>&1; then
    log "Application health check passed"
else
    log "WARNING: Application health check failed"
fi

log "Database recovery completed successfully"
send_notification "Database recovery completed" "Recovery type: $RECOVERY_TYPE"

Application File Backup

Application File Backup Script

#!/bin/bash
# scripts/backup/application_backup.sh

set -e

# Configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
source "$SCRIPT_DIR/../config/backup.conf"

APP_DIR="${APP_DIR:-/var/www/timesheet}"
BACKUP_DIR="${BACKUP_DIR:-/backups/application}"
S3_BUCKET="${S3_BACKUP_BUCKET:-stratpoint-timesheet-backups}"

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Generate backup filename
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/app_files_$TIMESTAMP.tar.gz"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

log "Starting application file backup"

# Create file list for backup
INCLUDE_PATTERNS=(
    "storage/app/*"
    "storage/logs/*"
    "public/uploads/*"
    ".env*"
    "config/*"
)

EXCLUDE_PATTERNS=(
    "storage/framework/cache/*"
    "storage/framework/sessions/*"
    "storage/framework/views/*"
    "node_modules/*"
    "vendor/*"
    ".git/*"
    "*.log"
)

# Build tar command
TAR_CMD="tar -czf $BACKUP_FILE -C $APP_DIR"

# Add include patterns
for pattern in "${INCLUDE_PATTERNS[@]}"; do
    TAR_CMD="$TAR_CMD --include='$pattern'"
done

# Add exclude patterns
for pattern in "${EXCLUDE_PATTERNS[@]}"; do
    TAR_CMD="$TAR_CMD --exclude='$pattern'"
done

TAR_CMD="$TAR_CMD ."

# Execute backup
log "Creating application file backup"
eval "$TAR_CMD"

# Verify backup
log "Verifying backup integrity"
if tar -tzf "$BACKUP_FILE" > /dev/null 2>&1; then
    log "Backup verification successful"
else
    log "ERROR: Backup verification failed"
    exit 1
fi

# Calculate checksums
MD5_CHECKSUM=$(md5sum "$BACKUP_FILE" | awk '{print $1}')
SHA256_CHECKSUM=$(sha256sum "$BACKUP_FILE" | awk '{print $1}')

# Upload to cloud storage
log "Uploading to cloud storage"
aws s3 cp "$BACKUP_FILE" "s3://$S3_BUCKET/application/daily/" \
    --metadata "md5=$MD5_CHECKSUM,sha256=$SHA256_CHECKSUM"

# Cleanup old backups
find "$BACKUP_DIR" -name "app_files_*.tar.gz" -mtime +30 -delete

BACKUP_SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
log "Application file backup completed: $BACKUP_SIZE"

Disaster Recovery Procedures

Complete System Recovery

#!/bin/bash
# scripts/recovery/disaster_recovery.sh

set -e

# Configuration
RECOVERY_TYPE="$1"  # full, partial
RECOVERY_DATE="$2"  # YYYY-MM-DD

if [ -z "$RECOVERY_TYPE" ]; then
    echo "Usage: $0 <recovery_type> [recovery_date]"
    echo "Recovery types: full, partial"
    exit 1
fi

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

log "Starting disaster recovery: $RECOVERY_TYPE"

# Step 1: Infrastructure preparation
log "Preparing infrastructure"
./scripts/setup/prepare_infrastructure.sh

# Step 2: Database recovery
log "Recovering database"
if [ -n "$RECOVERY_DATE" ]; then
    LATEST_BACKUP=$(aws s3 ls s3://$S3_BUCKET/database/daily/ | grep "$RECOVERY_DATE" | tail -1 | awk '{print $4}')
else
    LATEST_BACKUP=$(aws s3 ls s3://$S3_BUCKET/database/daily/ | tail -1 | awk '{print $4}')
fi

aws s3 cp "s3://$S3_BUCKET/database/daily/$LATEST_BACKUP" /tmp/
./scripts/recovery/database_recovery.sh "/tmp/$LATEST_BACKUP" full

# Step 3: Application recovery
log "Recovering application files"
if [ -n "$RECOVERY_DATE" ]; then
    LATEST_APP_BACKUP=$(aws s3 ls s3://$S3_BUCKET/application/daily/ | grep "$RECOVERY_DATE" | tail -1 | awk '{print $4}')
else
    LATEST_APP_BACKUP=$(aws s3 ls s3://$S3_BUCKET/application/daily/ | tail -1 | awk '{print $4}')
fi

aws s3 cp "s3://$S3_BUCKET/application/daily/$LATEST_APP_BACKUP" /tmp/
tar -xzf "/tmp/$LATEST_APP_BACKUP" -C /var/www/timesheet/

# Step 4: Configuration recovery
log "Recovering configuration"
./scripts/recovery/configuration_recovery.sh

# Step 5: Service startup
log "Starting services"
systemctl start mysql
systemctl start redis
systemctl start nginx
systemctl start timesheet-app
systemctl start timesheet-queue

# Step 6: Verification
log "Verifying system recovery"
./scripts/verification/system_health_check.sh

log "Disaster recovery completed"
send_notification "Disaster recovery completed" "Type: $RECOVERY_TYPE, Date: $RECOVERY_DATE"

Recovery Testing

#!/bin/bash
# scripts/testing/recovery_test.sh

set -e

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

log "Starting recovery test"

# Create test environment
TEST_ENV="recovery_test_$(date +%Y%m%d_%H%M%S)"
mkdir -p "/tmp/$TEST_ENV"

# Test database recovery
log "Testing database recovery"
LATEST_BACKUP=$(aws s3 ls s3://$S3_BUCKET/database/daily/ | tail -1 | awk '{print $4}')
aws s3 cp "s3://$S3_BUCKET/database/daily/$LATEST_BACKUP" "/tmp/$TEST_ENV/"

# Verify backup integrity
gunzip -t "/tmp/$TEST_ENV/$LATEST_BACKUP"

# Test application file recovery
log "Testing application file recovery"
LATEST_APP_BACKUP=$(aws s3 ls s3://$S3_BUCKET/application/daily/ | tail -1 | awk '{print $4}')
aws s3 cp "s3://$S3_BUCKET/application/daily/$LATEST_APP_BACKUP" "/tmp/$TEST_ENV/"

# Verify application backup integrity
tar -tzf "/tmp/$TEST_ENV/$LATEST_APP_BACKUP" > /dev/null

# Cleanup
rm -rf "/tmp/$TEST_ENV"

log "Recovery test completed successfully"

This comprehensive backup and recovery system ensures data protection and business continuity for the Stratpoint Timesheet Application with automated procedures, integrity verification, and disaster recovery capabilities.