MongoDB Backup and Recovery Strategies: SQL-Style Data Protection Patterns

-- SQL database backup patterns
-- Full backup
BACKUP DATABASE production_db 
TO DISK = '/backups/full/production_db_20250823.bak'
WITH INIT, STATS = 10;

-- Transaction log backup for point-in-time recovery
BACKUP LOG production_db
TO DISK = '/backups/logs/production_db_20250823_1400.trn';

-- Differential backup
BACKUP DATABASE production_db
TO DISK = '/backups/diff/production_db_diff_20250823.bak'
WITH DIFFERENTIAL, STATS = 10;

-- Point-in-time restore
RESTORE DATABASE production_db_recovered
FROM DISK = '/backups/full/production_db_20250823.bak'
WITH REPLACE, NORECOVERY;

RESTORE LOG production_db_recovered
FROM DISK = '/backups/logs/production_db_20250823_1400.trn'
WITH RECOVERY, STOPAT = '2025-08-23 14:30:00';

// MongoDB backup challenges
{
  // Large document collections
  "_id": ObjectId("..."),
  "user_data": {
    "profile": { /* large nested object */ },
    "preferences": { /* complex settings */ },
    "activity_log": [ /* thousands of entries */ ]
  },
  "created_at": ISODate("2025-08-23")
}

// Distributed across sharded clusters
// Replica sets with different read preferences
// GridFS files requiring consistent backup
// Indexes that must be rebuilt during restore

# Full database backup
mongodump --host mongodb://localhost:27017 \
          --db production_app \
          --out /backups/logical/20250823

# Specific collection backup
mongodump --host mongodb://localhost:27017 \
          --db production_app \
          --collection users \
          --out /backups/collections/users_20250823

# Compressed backup with query filter
mongodump --host mongodb://localhost:27017 \
          --db production_app \
          --gzip \
          --query '{"created_at": {"$gte": {"$date": "2025-08-01T00:00:00Z"}}}' \
          --out /backups/filtered/recent_data_20250823

-- Export specific data ranges
SELECT * FROM users 
WHERE created_at >= '2025-08-01'
ORDER BY _id
INTO OUTFILE '/backups/users_recent_20250823.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Full table export with consistent snapshot
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT * FROM orders INTO OUTFILE '/backups/orders_20250823.csv';
SELECT * FROM order_items INTO OUTFILE '/backups/order_items_20250823.csv';
COMMIT;

# Filesystem snapshot (requires stopping writes)
db.fsyncLock()
# Take filesystem snapshot here
db.fsyncUnlock()

# Using MongoDB Cloud Manager/Ops Manager
# Automated continuous backup with point-in-time recovery

# Replica set backup from secondary
mongodump --host secondary-replica:27017 \
          --readPreference secondary \
          --db production_app \
          --out /backups/replica/20250823

// Connect to secondary replica for backup
const client = new MongoClient(uri, {
  readPreference: 'secondary'
});

// Verify replica set status
const status = await client.db('admin').command({ replSetGetStatus: 1 });
console.log('Secondary lag:', status.members[1].optimeDate);

// Perform backup only if lag is acceptable
const maxLagMinutes = 5;
const lagMinutes = (new Date() - status.members[1].optimeDate) / 60000;

if (lagMinutes <= maxLagMinutes) {
  // Proceed with backup
  console.log('Starting backup from secondary...');
} else {
  console.log('Secondary lag too high, waiting...');
}

#!/bin/bash
# Production backup script with SQL-style logging

BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/mongodb/$BACKUP_DATE"
LOG_FILE="/logs/backup_$BACKUP_DATE.log"

# Function to log with timestamp
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S'): $1" | tee -a $LOG_FILE
}

# Create backup directory
mkdir -p $BACKUP_DIR

# Start backup process
log_message "Starting MongoDB backup to $BACKUP_DIR"

# Backup each database
for db in production_app analytics_db user_logs; do
    log_message "Backing up database: $db"

    mongodump --host mongodb-replica-set/primary:27017,secondary1:27017,secondary2:27017 \
              --readPreference secondary \
              --db $db \
              --gzip \
              --out $BACKUP_DIR \
              >> $LOG_FILE 2>&1

    if [ $? -eq 0 ]; then
        log_message "Successfully backed up $db"
    else
        log_message "ERROR: Failed to backup $db"
        exit 1
    fi
done

# Verify backup integrity
log_message "Verifying backup integrity"
find $BACKUP_DIR -name "*.bson.gz" -exec gzip -t {} \; >> $LOG_FILE 2>&1

if [ $? -eq 0 ]; then
    log_message "Backup integrity verified"
else
    log_message "ERROR: Backup integrity check failed"
    exit 1
fi

# Calculate backup size
BACKUP_SIZE=$(du -sh $BACKUP_DIR | cut -f1)
log_message "Backup completed: $BACKUP_SIZE total size"

# Cleanup old backups (keep last 7 days)
find /backups/mongodb -type d -mtime +7 -exec rm -rf {} \; 2>/dev/null
log_message "Cleanup completed: removed backups older than 7 days"

// Understanding MongoDB oplog for point-in-time recovery
db.oplog.rs.find().sort({ts: -1}).limit(5).pretty()

// Sample oplog entry
{
  "ts": Timestamp(1692796800, 1),
  "t": NumberLong(1),
  "h": NumberLong("1234567890123456789"),
  "v": 2,
  "op": "u",  // update operation
  "ns": "production_app.users",
  "o2": { "_id": ObjectId("...") },
  "o": { "$set": { "last_login": ISODate("2025-08-23T14:30:00Z") } }
}

// Find oplog entry at specific time
db.oplog.rs.find({
  "ts": { 
    "$gte": Timestamp(
      Math.floor(new Date("2025-08-23T14:30:00Z").getTime() / 1000), 0
    ) 
  }
}).limit(1)

-- Analyze transaction log for point-in-time recovery
SELECT 
  log_date,
  operation_type,
  database_name,
  table_name,
  transaction_id
FROM transaction_log
WHERE log_date >= '2025-08-23 14:30:00'
  AND log_date <= '2025-08-23 14:35:00'
ORDER BY log_date ASC;

-- Find last full backup before target time
SELECT 
  backup_file,
  backup_start_time,
  backup_end_time
FROM backup_history
WHERE backup_type = 'FULL'
  AND backup_end_time < '2025-08-23 14:30:00'
ORDER BY backup_end_time DESC
LIMIT 1;

#!/bin/bash
# Point-in-time recovery script

TARGET_TIME="2025-08-23T14:30:00Z"
RECOVERY_DB="production_app_recovered"
BACKUP_PATH="/backups/logical/20250823"

echo "Starting point-in-time recovery to $TARGET_TIME"

# Step 1: Restore from full backup
echo "Restoring from full backup..."
mongorestore --host localhost:27017 \
             --db $RECOVERY_DB \
             --drop \
             $BACKUP_PATH/production_app

# Step 2: Apply oplog entries up to target time
echo "Applying oplog entries up to $TARGET_TIME"

# Convert target time to timestamp
TARGET_TIMESTAMP=$(node -e "
  const date = new Date('$TARGET_TIME');
  const timestamp = Math.floor(date.getTime() / 1000);
  console.log(timestamp);
")

# Replay oplog entries
mongorestore --host localhost:27017 \
             --db $RECOVERY_DB \
             --oplogReplay \
             --oplogLimit "$TARGET_TIMESTAMP:0" \
             $BACKUP_PATH/oplog.bson

echo "Point-in-time recovery completed"

// Coordinate backup across sharded cluster
const shards = [
  { name: 'shard01', host: 'shard01-replica-set' },
  { name: 'shard02', host: 'shard02-replica-set' },
  { name: 'shard03', host: 'shard03-replica-set' }
];

// Stop balancer to ensure consistent backup
await mongosClient.db('admin').command({ balancerStop: 1 });

try {
  // Backup config servers first
  console.log('Backing up config servers...');
  await backupConfigServers();

  // Backup each shard concurrently
  console.log('Starting shard backups...');
  const backupPromises = shards.map(shard => 
    backupShard(shard.name, shard.host)
  );

  await Promise.all(backupPromises);
  console.log('All shard backups completed');

} finally {
  // Restart balancer
  await mongosClient.db('admin').command({ balancerStart: 1 });
}

// Automated backup configuration
const backupConfig = {
  clusterId: "64f123456789abcdef012345",
  snapshotSchedule: {
    referenceHourOfDay: 2,      // 2 AM UTC
    referenceMinuteOfHour: 0,
    restoreWindowDays: 7
  },
  policies: [
    {
      frequencyType: "DAILY",
      retentionUnit: "DAYS",
      retentionValue: 7
    },
    {
      frequencyType: "WEEKLY", 
      retentionUnit: "WEEKS",
      retentionValue: 4
    },
    {
      frequencyType: "MONTHLY",
      retentionUnit: "MONTHS", 
      retentionValue: 12
    }
  ]
};

-- Monitor backup success rates
SELECT 
  backup_date,
  database_name,
  backup_type,
  status,
  duration_minutes,
  backup_size_mb
FROM backup_log
WHERE backup_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY backup_date DESC;

-- Alert on backup failures
SELECT 
  database_name,
  COUNT(*) as failure_count,
  MAX(backup_date) as last_failure
FROM backup_log
WHERE status = 'FAILED'
  AND backup_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY database_name
HAVING failure_count > 0;

// Document recovery procedures with time estimates
const recoveryProcedures = {
  "single_node_failure": {
    rto: "5 minutes",
    rpo: "0 seconds", 
    steps: [
      "Replica set automatic failover",
      "Update application connection strings",
      "Monitor secondary promotion"
    ]
  },
  "datacenter_failure": {
    rto: "30 minutes",
    rpo: "5 minutes",
    steps: [
      "Activate disaster recovery site", 
      "Restore from latest backup",
      "Apply oplog entries",
      "Update DNS/load balancer",
      "Verify application connectivity"
    ]
  },
  "data_corruption": {
    rto: "2 hours", 
    rpo: "1 hour",
    steps: [
      "Stop write operations",
      "Identify corruption scope",
      "Restore from clean backup",
      "Apply selective oplog replay",
      "Validate data integrity"
    ]
  }
};

-- Regular recovery testing schedule
CREATE TABLE recovery_tests (
  test_id SERIAL PRIMARY KEY,
  test_date DATE,
  test_type VARCHAR(50),
  database_name VARCHAR(100),
  backup_file VARCHAR(255),
  restore_time_minutes INTEGER,
  data_validation_passed BOOLEAN,
  notes TEXT
);

-- Track recovery test results
INSERT INTO recovery_tests (
  test_date,
  test_type, 
  database_name,
  backup_file,
  restore_time_minutes,
  data_validation_passed,
  notes
) VALUES (
  CURRENT_DATE,
  'POINT_IN_TIME_RECOVERY',
  'production_app',
  '/backups/mongodb/20250823/production_app',
  45,
  true,
  'Successfully recovered to 14:30:00 UTC'
);

-- Track backup inventory
CREATE TABLE mongodb_backups (
  backup_id VARCHAR(50) PRIMARY KEY,
  database_name VARCHAR(100),
  backup_type VARCHAR(20), -- 'LOGICAL', 'BINARY', 'SNAPSHOT'
  backup_date TIMESTAMP,
  file_path VARCHAR(500),
  compressed BOOLEAN,
  size_bytes BIGINT,
  status VARCHAR(20),
  retention_days INTEGER
);

-- Backup validation queries
SELECT 
  database_name,
  backup_type,
  backup_date,
  size_bytes / 1024 / 1024 / 1024 AS size_gb,
  CASE 
    WHEN backup_date >= CURRENT_TIMESTAMP - INTERVAL '24 hours' THEN 'CURRENT'
    WHEN backup_date >= CURRENT_TIMESTAMP - INTERVAL '7 days' THEN 'RECENT' 
    ELSE 'OLD'
  END AS freshness
FROM mongodb_backups
WHERE status = 'COMPLETED'
ORDER BY backup_date DESC;

-- Find gaps in backup schedule
WITH backup_dates AS (
  SELECT 
    database_name,
    DATE(backup_date) AS backup_day
  FROM mongodb_backups
  WHERE backup_type = 'LOGICAL'
    AND status = 'COMPLETED'
    AND backup_date >= CURRENT_DATE - INTERVAL '30 days'
),
expected_dates AS (
  SELECT 
    db_name,
    generate_series(
      CURRENT_DATE - INTERVAL '30 days',
      CURRENT_DATE,
      INTERVAL '1 day'
    )::DATE AS expected_day
  FROM (SELECT DISTINCT database_name AS db_name FROM mongodb_backups) dbs
)
SELECT 
  ed.db_name,
  ed.expected_day,
  'MISSING_BACKUP' AS alert
FROM expected_dates ed
LEFT JOIN backup_dates bd ON ed.db_name = bd.database_name 
                         AND ed.expected_day = bd.backup_day
WHERE bd.backup_day IS NULL
ORDER BY ed.db_name, ed.expected_day;

# Encrypted backup with SSL/TLS
mongodump --host mongodb-cluster.example.com:27017 \
          --ssl \
          --sslCAFile /certs/ca.pem \
          --sslPEMKeyFile /certs/client.pem \
          --username backup_user \
          --password \
          --authenticationDatabase admin \
          --db production_app \
          --gzip \
          --out /encrypted-backups/20250823

# Encrypt backup files at rest
gpg --cipher-algo AES256 --compress-algo 2 --symmetric \
    --output /backups/encrypted/production_app_20250823.gpg \
    /backups/mongodb/20250823/production_app

-- Audit backup compliance
SELECT 
  database_name,
  COUNT(*) as backup_count,
  MIN(backup_date) as oldest_backup,
  MAX(backup_date) as newest_backup,
  CASE 
    WHEN MAX(backup_date) >= CURRENT_DATE - INTERVAL '1 day' THEN 'COMPLIANT'
    ELSE 'NON_COMPLIANT'
  END AS compliance_status
FROM mongodb_backups
WHERE backup_date >= CURRENT_DATE - INTERVAL '90 days'
  AND status = 'COMPLETED'
GROUP BY database_name;

-- Generate compliance report
SELECT 
  'MongoDB Backup Compliance Report' AS report_title,
  CURRENT_DATE AS report_date,
  COUNT(DISTINCT database_name) AS total_databases,
  COUNT(*) AS total_backups,
  SUM(size_bytes) / 1024 / 1024 / 1024 AS total_backup_size_gb
FROM mongodb_backups
WHERE backup_date >= CURRENT_DATE - INTERVAL '30 days'
  AND status = 'COMPLETED';

// Implement incremental backups based on timestamps
const lastBackup = await db.collection('backup_metadata').findOne(
  { type: 'INCREMENTAL' },
  { sort: { timestamp: -1 } }
);

const incrementalQuery = {
  $or: [
    { created_at: { $gt: lastBackup.timestamp } },
    { updated_at: { $gt: lastBackup.timestamp } }
  ]
};

// Backup only changed documents
const changedDocuments = await db.collection('users').find(incrementalQuery);

-- Automated backup retention management
DELETE FROM mongodb_backups 
WHERE backup_date < CURRENT_DATE - INTERVAL '90 days'
  AND backup_type = 'LOGICAL';

-- Archive old backups to cold storage
UPDATE mongodb_backups 
SET storage_tier = 'COLD_STORAGE',
    file_path = REPLACE(file_path, '/hot-storage/', '/archive/')
WHERE backup_date BETWEEN CURRENT_DATE - INTERVAL '365 days' 
                      AND CURRENT_DATE - INTERVAL '90 days'
  AND storage_tier = 'HOT_STORAGE';

-- Validate restored data integrity
SELECT 
  COUNT(*) as total_users,
  COUNT(DISTINCT email) as unique_emails,
  MIN(created_at) as oldest_user,
  MAX(created_at) as newest_user
FROM users;

-- Compare counts between original and restored database
SELECT 
  'users' as collection_name,
  (SELECT COUNT(*) FROM production_app.users) as original_count,
  (SELECT COUNT(*) FROM production_app_backup.users) as backup_count;

-- Verify referential integrity after restore
SELECT 
  o.order_id,
  o.user_id,
  'Missing user reference' as issue
FROM orders o
LEFT JOIN users u ON o.user_id = u._id  
WHERE u._id IS NULL
LIMIT 10;