Backup & Recovery Drill

A backup is only valuable if it can be successfully restored. Many teams discover their backups are corrupted, incomplete, or that the recovery process is undocumented only when they actually need to recover data. Regular backup recovery drills are a critical practice for ensuring business continuity.

Why Recovery Drills Matter

Risk	Consequence	How Drills Help
Corrupted backup files	Unable to recover any data	Verifies backup integrity
Undocumented recovery process	Panic and excessive downtime during incidents	Produces a standard operating procedure
Incomplete backup data	Critical data missing	Reveals overlooked backup targets
Staff turnover	Only specific people know how to restore	All team members can execute recovery
Environment differences	Backup cannot be restored in test environment	Exposes compatibility issues

Golden Rule

An untested backup is no backup at all. Perform a full recovery drill at least once per quarter.

Pre-Drill Preparation

Environment Setup

Prepare a test server with a configuration similar to production:

# Confirm test server basics
cat /etc/redhat-release
uname -r
df -h
free -h

Tool Installation

Ensure the test environment has the necessary recovery tools:

dnf install -y \
  borgbackup \
  mysql \
  postgresql \
  tar \
  rsync \
  gzip \
  xz

Inventory Backups

Document the backup sources to be verified:

# List available backup archives
# Borg backups
borg list /backup/borg-repo

# Or list remote backups
borg list ssh://backup@backup-server/repo

# Record the latest backup timestamp
borg info /backup/borg-repo::latest

Phase 1: Backup Integrity Verification

Before restoring, confirm that the backup files themselves are intact.

Borg Backup Verification

# Verify repository integrity
borg check /backup/borg-repo

# Verify a specific archive
borg check --archives-only /backup/borg-repo::2026-03-24_0200

# Deep verification (includes data chunk validation, takes longer)
borg check --verify-data /backup/borg-repo

tar Backup Verification

# Test tar archive integrity
tar -tzf /backup/full-backup-20260324.tar.gz > /dev/null
echo "Exit code: $?"
# Exit code 0 means success

# If GPG encrypted
gpg --decrypt /backup/full-backup-20260324.tar.gz.gpg | tar -tz > /dev/null

rsync Backup Verification

# Compare source and backup for differences
rsync -avnc /source/path/ /backup/rsync-mirror/ > /tmp/rsync-diff.txt
cat /tmp/rsync-diff.txt

# Empty diff means consistent

Phase 2: File Recovery Test

2.1 Restore System Configuration Files

# Create a temporary restore directory
mkdir -p /tmp/restore-test

# Extract specific directories from Borg
cd /tmp/restore-test
borg extract /backup/borg-repo::latest etc/

# Compare critical configuration files
diff /tmp/restore-test/etc/ssh/sshd_config /etc/ssh/sshd_config
diff /tmp/restore-test/etc/firewalld/ /etc/firewalld/ -r

2.2 Restore Application Data

# Extract application directory from Borg
cd /tmp/restore-test
borg extract /backup/borg-repo::latest var/www/myapp/

# Verify file count and size
find /tmp/restore-test/var/www/myapp -type f | wc -l
du -sh /tmp/restore-test/var/www/myapp

2.3 Verify File Permissions and Attributes

After restoration, verify that file permissions are correct:

# Check permissions on critical directories
ls -la /tmp/restore-test/etc/ssh/
ls -la /tmp/restore-test/var/www/myapp/

# Check SELinux contexts
ls -Z /tmp/restore-test/var/www/myapp/

2.4 Cleanup

rm -rf /tmp/restore-test

Phase 3: MySQL / MariaDB Recovery Test

3.1 Prepare the Test Environment

# Install MySQL/MariaDB on the test server
dnf install -y mysql-server
systemctl start mysqld

# Create a dedicated test database
mysql -u root -e "CREATE DATABASE restore_test;"

3.2 Restore a mysqldump Backup

# Inspect the backup file
ls -lh /backup/mysql/mydb-20260324.sql.gz
zcat /backup/mysql/mydb-20260324.sql.gz | head -30

# Restore into the test database
zcat /backup/mysql/mydb-20260324.sql.gz | mysql -u root restore_test

3.3 Verify Data Consistency

# Check table count
mysql -u root -e "SELECT COUNT(*) AS table_count FROM information_schema.tables WHERE table_schema='restore_test';"

# Check row counts per table
mysql -u root -e "
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema='restore_test'
ORDER BY table_name;
"

# Spot-check critical data
mysql -u root -e "SELECT COUNT(*) FROM restore_test.users;"
mysql -u root -e "SELECT COUNT(*) FROM restore_test.orders;"

# Check latest record timestamp (confirms backup point in time)
mysql -u root -e "SELECT MAX(created_at) AS latest_record FROM restore_test.orders;"

3.4 Restore XtraBackup Physical Backup (If Used)

# Prepare the backup
xtrabackup --prepare --target-dir=/backup/xtrabackup/20260324

# Restore to a test directory
systemctl stop mysqld
xtrabackup --copy-back --target-dir=/backup/xtrabackup/20260324 --datadir=/var/lib/mysql-test

# Fix ownership
chown -R mysql:mysql /var/lib/mysql-test

# Start with the test data directory
mysqld --datadir=/var/lib/mysql-test --port=3307 &

# Verify
mysql -P 3307 -u root -e "SHOW DATABASES;"

3.5 Cleanup

mysql -u root -e "DROP DATABASE restore_test;"

Phase 4: PostgreSQL Recovery Test

4.1 Prepare the Test Environment

# Install PostgreSQL on the test server
dnf install -y postgresql-server
postgresql-setup --initdb
systemctl start postgresql

4.2 Restore a pg_dump Backup

# Inspect the backup
ls -lh /backup/postgresql/mydb-20260324.sql.gz
zcat /backup/postgresql/mydb-20260324.sql.gz | head -30

# Create the test database and user
sudo -u postgres createdb restore_test
sudo -u postgres createuser --no-password testuser

# Restore
zcat /backup/postgresql/mydb-20260324.sql.gz | sudo -u postgres psql restore_test

4.3 Restore Custom Format Backup

# If using pg_dump -Fc format
sudo -u postgres pg_restore -d restore_test /backup/postgresql/mydb-20260324.dump

# Restore only a specific table
sudo -u postgres pg_restore -d restore_test -t users /backup/postgresql/mydb-20260324.dump

4.4 Verify Data Consistency

# Check table count
sudo -u postgres psql restore_test -c "
SELECT COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'public';
"

# Check row counts per table
sudo -u postgres psql restore_test -c "
SELECT schemaname, relname AS table_name, n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY relname;
"

# Spot-check data
sudo -u postgres psql restore_test -c "SELECT COUNT(*) FROM users;"
sudo -u postgres psql restore_test -c "SELECT MAX(created_at) FROM orders;"

# Verify index integrity
sudo -u postgres psql restore_test -c "REINDEX DATABASE restore_test;"

4.5 Point-in-Time Recovery (PITR) with WAL

# Prepare the restore directory
mkdir -p /var/lib/pgsql/restore-test

# Restore the base backup
tar xzf /backup/postgresql/base-20260324.tar.gz -C /var/lib/pgsql/restore-test/

# Configure recovery parameters
cat > /var/lib/pgsql/restore-test/postgresql.auto.conf << 'EOF'
restore_command = 'cp /backup/postgresql/wal/%f %p'
recovery_target_time = '2026-03-24 12:00:00'
recovery_target_action = 'promote'
EOF

# Create the recovery signal file
touch /var/lib/pgsql/restore-test/recovery.signal

# Fix ownership
chown -R postgres:postgres /var/lib/pgsql/restore-test/

# Start the recovery instance on a different port
sudo -u postgres pg_ctl start -D /var/lib/pgsql/restore-test -o "-p 5433"

# Verify the recovery point
psql -p 5433 -U postgres -c "SELECT pg_last_xact_replay_timestamp();"

4.6 Cleanup

sudo -u postgres dropdb restore_test
# If a PITR test instance was started
sudo -u postgres pg_ctl stop -D /var/lib/pgsql/restore-test
rm -rf /var/lib/pgsql/restore-test

Phase 5: Document the Results

Every drill must produce a detailed report. Use the following template:

Drill Report Template

===== Backup Recovery Drill Report =====
Date: 2026-03-25
Performed by: [Name]
Environment: Test server restore-test-01

--- Backup Integrity Verification ---
[PASS/FAIL] Borg repository check
[PASS/FAIL] Data chunk integrity
Notes:

--- File Recovery ---
[PASS/FAIL] System configuration files restored
[PASS/FAIL] Application data restored
[PASS/FAIL] File permissions correct
Notes:

--- MySQL Recovery ---
[PASS/FAIL] Backup file loaded successfully
[PASS/FAIL] Table count matches: Production XX tables, Restored XX tables
[PASS/FAIL] Row counts match
[PASS/FAIL] Latest data timestamp as expected
Recovery time: XX minutes
Notes:

--- PostgreSQL Recovery ---
[PASS/FAIL] Backup file loaded successfully
[PASS/FAIL] Table count matches: Production XX tables, Restored XX tables
[PASS/FAIL] Row counts match
[PASS/FAIL] Index rebuild successful
Recovery time: XX minutes
Notes:

--- Issues Discovered ---
1. [Describe issue and impact]
2. [Describe issue and impact]

--- Remediation Actions ---
1. [Specific action and owner]
2. [Specific action and owner]

--- Next Drill Planned ---
Date: 2026-06-25
Scope: [Full / Incremental / Specific system]

Phase 6: Establish a Quarterly Drill Schedule

Drill Calendar

Quarter	Month	Drill Scope	Estimated Duration
Q1	March	Full recovery drill (files + databases)	4 hours
Q2	June	Database recovery focus + PITR test	2 hours
Q3	September	Full recovery drill (including disaster recovery scenario)	4 hours
Q4	December	File recovery + annual backup strategy review	3 hours

Automated Reminders

# Create a quarterly drill reminder cron job
cat > /etc/cron.d/backup-drill-reminder << 'EOF'
# Fires on the 1st of each quarter month at 09:00 (cron does not skip weekends/holidays; a real "first business day" needs a systemd timer or a script check)
0 9 1 3,6,9,12 * root echo "Backup recovery drill reminder: Please complete the quarterly drill this month." | mail -s "[ACTION] Quarterly Backup Recovery Drill" ops-team@example.com
EOF

Automated Verification Script

In addition to manual drills, automate daily backup validation:

cat > /usr/local/bin/backup-verify.sh << 'SCRIPT'
#!/bin/bash
set -euo pipefail

LOG="/var/log/backup-verify.log"
ALERT_EMAIL="ops-team@example.com"
ERRORS=0

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

log "===== Starting backup verification ====="

# 1. Check Borg backup
if borg check /backup/borg-repo 2>> "$LOG"; then
    log "[PASS] Borg repository integrity check passed"
else
    log "[FAIL] Borg repository integrity check failed"
    ((ERRORS++))
fi

# 2. Check latest backup age (should be less than 25 hours)
LATEST=$(borg list --sort-by timestamp --last 1 --format '{time}' /backup/borg-repo)
LATEST_TS=$(date -d "$LATEST" +%s)
NOW_TS=$(date +%s)
AGE_HOURS=$(( (NOW_TS - LATEST_TS) / 3600 ))

if [ "$AGE_HOURS" -lt 25 ]; then
    log "[PASS] Latest backup is ${AGE_HOURS} hours old"
else
    log "[FAIL] Latest backup is stale: ${AGE_HOURS} hours old"
    ((ERRORS++))
fi

# 3. Check backup size (should not be zero or abnormally small)
LATEST_SIZE=$(borg info /backup/borg-repo::latest --json | python3 -c "import sys,json; print(json.load(sys.stdin)['archives'][0]['stats']['original_size'])")
if [ "$LATEST_SIZE" -gt 1048576 ]; then
    log "[PASS] Backup size is normal: $LATEST_SIZE bytes"
else
    log "[FAIL] Backup size is abnormal: $LATEST_SIZE bytes"
    ((ERRORS++))
fi

# Report results
if [ "$ERRORS" -gt 0 ]; then
    log "Verification complete, found ${ERRORS} issue(s)"
    tail -20 "$LOG" | mail -s "[ALERT] Backup verification failed (${ERRORS} errors)" "$ALERT_EMAIL"
else
    log "Verification complete, all checks passed"
fi
SCRIPT

chmod +x /usr/local/bin/backup-verify.sh

Schedule daily automated verification:

cat > /etc/cron.d/backup-verify << 'EOF'
0 6 * * * root /usr/local/bin/backup-verify.sh
EOF

Common Issues and Solutions

Issue	Cause	Solution
Database fails to start after restore	Version mismatch	Ensure test environment uses the same database version as production
Incorrect file permissions after restore	tar extraction didn’t preserve attributes	Use tar -xpf to preserve permissions; run restorecon -Rv after restore
Borg reports repository corruption	Interrupted backup or disk failure	Run borg check --repair; check backup disk health
Recovery takes too long	Large data volume	Consider incremental restore strategies; use faster storage media
Missing WAL files	Archiving misconfiguration	Check archive_command settings; ensure archive directory has sufficient space

Summary

Backup recovery drills are not a one-time task but an ongoing operational practice. Key takeaways:

1. Verify first - An unverified backup cannot be trusted
2. Document everything - Every drill should produce an actionable runbook
3. Drill regularly - At least once per quarter for a full drill
4. Involve everyone - Do not depend on a single person
5. Continuously improve - Review and optimize the process after each drill