Database Backup and Recovery Strategies

Database backup and recovery protects against data loss from hardware failures, software bugs, human errors, and security incidents. A robust backup strategy defines what to back up, how often, where to store backups, and how to restore them. The goal isn't just creating backups; it's ensuring reliable recovery when disaster strikes.

Recovery time objective (RTO) defines how quickly you need to restore service. Recovery point objective (RPO) defines how much data loss is acceptable. A financial system might require RPO of minutes and RTO of an hour. A development database might accept RPO of 24 hours and RTO of a day. Your backup strategy must meet these objectives.

Backup Types

Full backups capture the entire database. They're self-contained and simple to restore but large and slow to create. Full backups are the foundation of any backup strategy.

# PostgreSQL full backup
pg_dump -Fc -f /backups/mydb_$(date +%Y%m%d_%H%M%S).dump mydb

# MySQL full backup
mysqldump --single-transaction --routines --triggers \
  mydb > /backups/mydb_$(date +%Y%m%d_%H%M%S).sql

# Compressed MySQL backup
mysqldump --single-transaction mydb | gzip > /backups/mydb_$(date +%Y%m%d_%H%M%S).sql.gz

Incremental backups capture only changes since the last backup. They're smaller and faster but require the full backup plus all increments to restore. Point-in-time recovery depends on incremental backups.

# PostgreSQL WAL archiving for point-in-time recovery
# postgresql.conf
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'

# Restore to specific timestamp
pg_restore -d mydb base_backup.dump
# Then replay WAL to target time
recovery_target_time = '2024-01-15 14:30:00'

Differential backups capture changes since the last full backup. They're larger than incrementals but require only the full backup plus the latest differential to restore. This balances backup speed with restore simplicity.

Backup Strategies

The 3-2-1 rule provides a foundation: maintain 3 copies of data, on 2 different storage types, with 1 copy offsite. This protects against single points of failure.

class BackupService
{
    public function performBackup(): void
    {
        $filename = 'backup_' . now()->format('Y-m-d_His') . '.dump';

        // Create backup
        $localPath = $this->createBackup($filename);

        // Copy to secondary storage
        $this->copyToNas($localPath, $filename);

        // Copy to offsite cloud storage
        $this->uploadToS3($localPath, $filename);

        // Verify backup integrity
        $this->verifyBackup($localPath);

        // Update backup metadata
        BackupRecord::create([
            'filename' => $filename,
            'size' => filesize($localPath),
            'checksum' => hash_file('sha256', $localPath),
            'locations' => ['local', 'nas', 's3'],
        ]);
    }

    private function createBackup(string $filename): string
    {
        $path = storage_path("backups/$filename");

        Process::run([
            'pg_dump', '-Fc',
            '-f', $path,
            config('database.connections.pgsql.database'),
        ])->throw();

        return $path;
    }
}

Retention policies balance storage costs against recovery needs. Keep recent backups for quick recovery, older backups for compliance or historical recovery:

class BackupRetentionService
{
    public function applyRetention(): void
    {
        // Keep all backups from last 7 days
        // Keep daily backups for 30 days
        // Keep weekly backups for 90 days
        // Keep monthly backups for 1 year

        $now = now();

        BackupRecord::query()
            ->where('created_at', '<', $now->subDays(7))
            ->whereNotIn('id', $this->getDailyBackups(30))
            ->whereNotIn('id', $this->getWeeklyBackups(90))
            ->whereNotIn('id', $this->getMonthlyBackups(365))
            ->each(fn ($backup) => $this->deleteBackup($backup));
    }

    private function getDailyBackups(int $days): array
    {
        // Keep first backup of each day
        return BackupRecord::query()
            ->where('created_at', '>=', now()->subDays($days))
            ->get()
            ->groupBy(fn ($b) => $b->created_at->format('Y-m-d'))
            ->map(fn ($group) => $group->first()->id)
            ->values()
            ->toArray();
    }
}

Automated Backup Scheduling

Reliable backups require automation. Manual backups are forgotten; automated backups run consistently.

// app/Console/Kernel.php
protected function schedule(Schedule $schedule): void
{
    // Full backup daily at 2 AM
    $schedule->job(new FullDatabaseBackup())
        ->dailyAt('02:00')
        ->withoutOverlapping()
        ->onFailure(fn () => $this->alertOps('Full backup failed'));

    // Transaction log backup every 15 minutes
    $schedule->job(new TransactionLogBackup())
        ->everyFifteenMinutes()
        ->withoutOverlapping();

    // Backup verification daily
    $schedule->job(new VerifyRecentBackups())
        ->dailyAt('06:00');

    // Retention cleanup weekly
    $schedule->job(new ApplyBackupRetention())
        ->weekly();
}

Cloud-managed databases often provide automated backups. Configure retention and verify you can restore:

# AWS RDS backup configuration
Resources:
  Database:
    Type: AWS::RDS::DBInstance
    Properties:
      BackupRetentionPeriod: 30
      PreferredBackupWindow: "02:00-03:00"
      EnablePerformanceInsights: true
      DeletionProtection: true
      # Enable automated backups to S3 for cross-region
      # disaster recovery

Backup Verification

Backups that can't be restored are useless. Regular verification ensures backups are complete and restorable.

class BackupVerificationService
{
    public function verifyBackup(BackupRecord $backup): VerificationResult
    {
        // 1. Verify file exists and checksum matches
        $path = $this->downloadBackup($backup);

        if (hash_file('sha256', $path) !== $backup->checksum) {
            return VerificationResult::failed('Checksum mismatch');
        }

        // 2. Attempt restore to test database
        $testDb = 'backup_test_' . uniqid();

        try {
            DB::statement("CREATE DATABASE $testDb");

            Process::run([
                'pg_restore',
                '-d', $testDb,
                '--no-owner',
                $path,
            ])->throw();

            // 3. Verify data integrity
            $rowCounts = $this->getRowCounts($testDb);
            $this->validateRowCounts($rowCounts, $backup);

            return VerificationResult::success();

        } finally {
            DB::statement("DROP DATABASE IF EXISTS $testDb");
            unlink($path);
        }
    }
}

Schedule regular restore tests. Quarterly disaster recovery drills verify the entire recovery process, including documentation and team readiness.

Recovery Procedures

Recovery procedures should be documented, tested, and accessible during outages. When disaster strikes isn't the time to figure out recovery steps.

class DatabaseRecoveryService
{
    public function recoverToPointInTime(Carbon $targetTime): void
    {
        Log::info('Starting point-in-time recovery', [
            'target_time' => $targetTime,
        ]);

        // 1. Find appropriate base backup
        $baseBackup = BackupRecord::query()
            ->where('type', 'full')
            ->where('created_at', '<=', $targetTime)
            ->orderByDesc('created_at')
            ->firstOrFail();

        // 2. Stop application traffic
        $this->enableMaintenanceMode();

        // 3. Restore base backup
        $this->restoreFullBackup($baseBackup);

        // 4. Apply transaction logs to target time
        $this->applyTransactionLogs($baseBackup->created_at, $targetTime);

        // 5. Verify recovery
        $this->verifyRecovery();

        // 6. Resume traffic
        $this->disableMaintenanceMode();

        Log::info('Recovery completed', [
            'base_backup' => $baseBackup->id,
            'target_time' => $targetTime,
        ]);
    }

    private function restoreFullBackup(BackupRecord $backup): void
    {
        $path = $this->downloadBackup($backup);

        // PostgreSQL restore
        Process::run([
            'pg_restore',
            '-d', config('database.connections.pgsql.database'),
            '--clean',
            '--if-exists',
            $path,
        ])->throw();
    }
}

Document recovery runbooks with step-by-step instructions:

# Database Recovery Runbook

## Prerequisites
- Access to backup storage (S3, NAS)
- Database admin credentials
- Application deployment access

## Steps

1. Assess the situation
   - What data was lost/corrupted?
   - What is the recovery target time?
   - Estimate recovery duration

2. Notify stakeholders
   - Enable maintenance page
   - Send status update to team

3. Identify recovery point
   - List available backups: `php artisan backup:list`
   - Select backup preceding the incident

4. Perform recovery
   - Full restore: `php artisan backup:restore --backup=<id>`
   - Point-in-time: `php artisan backup:restore --target-time="2024-01-15 14:30:00"`

5. Verify recovery
   - Check row counts
   - Verify recent transactions
   - Test critical functionality

6. Resume service
   - Disable maintenance mode
   - Monitor for issues
   - Send all-clear notification

Disaster Recovery Considerations

Regional disasters require cross-region backups. If your primary region is unavailable, you need backups stored elsewhere.

class CrossRegionBackupService
{
    public function replicateBackup(BackupRecord $backup): void
    {
        // Copy to secondary region
        Storage::disk('s3_backup_secondary')->put(
            $backup->filename,
            Storage::disk('s3_backup_primary')->get($backup->filename)
        );

        // Update record with secondary location
        $backup->update([
            'locations' => array_merge($backup->locations, ['s3_secondary']),
        ]);
    }
}

For critical systems, consider continuous replication in addition to backups:

# PostgreSQL streaming replication
primary_conninfo = 'host=primary.example.com port=5432 user=replicator'
restore_command = 'cp /wal_archive/%f %p'
recovery_target_timeline = 'latest'

Conclusion

Database backup and recovery protects your most valuable asset: data. Define RPO and RTO based on business requirements. Implement automated backups with appropriate retention. Verify backups regularly through test restores. Document recovery procedures and practice them.

The cost of a robust backup strategy is small compared to the cost of data loss. When disaster strikes, you'll be grateful for every backup you took and every recovery drill you ran.