Database Backup, Recovery & Hardening Checklist
Full/incremental/differential backups, point-in-time recovery via transaction logs, least-privilege database accounts, and why restore testing matters as much as the backup.
Learning Objectives
- → Compare full, incremental, and differential database backup strategies
- → Explain point-in-time recovery and the role of transaction logs (WAL/binlog)
- → Apply least-privilege principles to database accounts and connections
- → Build a database hardening checklist covering authentication, network exposure, and patching
- → Explain why restore testing is as important as the backup itself
Backup Types: Full, Incremental, Differential
| Type | What it captures | Restore complexity |
|---|---|---|
| Full | The entire database at a point in time | Simplest — one file to restore |
| Incremental | Only changes since the last backup (full or incremental) | Fastest to create, slowest to restore (must replay every incremental in sequence) |
| Differential | Only changes since the last full backup | Middle ground — faster restore than incremental (only full + latest differential needed), slower to create than incremental |
Most production strategies combine these: a weekly full backup plus daily incrementals or differentials, balancing backup window time against restore time.
Point-in-Time Recovery and Transaction Logs
A full backup alone only restores you to the moment it was taken. Point-in-time recovery (PITR) uses the database's transaction log — PostgreSQL's WAL (Write-Ahead Log), MySQL's binlog — to replay every committed transaction after the last full backup, restoring the database to any specific moment (e.g. "5 minutes before the accidental DROP TABLE ran"), not just the last backup's timestamp.
Backup Security: Encryption and Access Control
A backup is a complete copy of your production data — it deserves the same protection as production itself, not less:
- Encrypt backups at rest, especially before shipping them offsite (satisfies the 3-2-1 rule's offsite copy from the BCDR lesson)
- Restrict who can read/restore backups as tightly as who can query production directly
- Never leave backup files in a publicly readable cloud storage bucket (the exact GCS/S3 misconfiguration pattern covered in the cloud security lessons)
Database Hardening Checklist
- Least privilege accounts — application accounts get only the specific grants they need (SELECT/INSERT on specific tables), never a superuser/root database account
- Network exposure — the database should not be directly reachable from the public internet; restrict to application servers via security groups/firewall rules
- Authentication — disable default/blank passwords, enforce strong credentials or certificate-based auth
- Patching — apply database engine security patches on a defined cadence, not "whenever convenient"
- Audit logging — log administrative actions and, for sensitive data, query-level access
- Encryption — encrypt data at rest and require TLS for connections in transit
Why Restore Testing Matters as Much as the Backup
A backup that has never been test-restored is a hypothesis, not a guarantee — corruption, incomplete captures, or format incompatibilities are common and often invisible until the moment you actually need the backup. Schedule periodic restore drills to a non-production environment specifically to validate that backups are actually usable, not just present.
Common Pitfalls
- Backing up regularly but never testing an actual restore until a real incident forces it
- Using an over-privileged database account (root/superuser) for routine application connectivity
- Leaving a database directly internet-reachable "temporarily" during a migration, and forgetting to close it off afterward
- Storing backups unencrypted, treating them as lower-risk than the live production database they're a complete copy of
Differential always references the last full backup only; incremental chains onto whatever backup came immediately before it.
Why is restoring from a chain of incremental backups typically slower than from a differential backup?
The transaction log records every committed change, letting recovery replay forward to any specific moment, not just the last backup's timestamp.
What database component enables restoring to a specific moment rather than just the last backup's timestamp?
An over-privileged application account turns any application-layer bug (like SQL injection) into full database compromise.
Why shouldn't an application use a superuser/root database account for routine connectivity?
💪 Exercises & Challenges
Database Backup, Recovery & Hardening Checklist MCQ
Test your understanding of Database Backup, Recovery & Hardening Checklist.
Design a Backup Schedule and Access Policy
A production database has a 4-hour RPO requirement and limited nightly backup windows. Design a backup type/schedule combination that meets the RPO, and specify the minimum database privileges the app
Diagnose Two Independent Database Failures
A post-incident review of a ransomware attack finds: (1) the production database's service account used by the web application had full superuser/admin privileges, which the attacker leveraged after a