Database Backup and Recovery: Planning for Data Reliability

Previously in this course, we explored Entity Lifecycle Management to track state changes. Now, we shift our focus from day-to-day operations to "what happens when things go wrong"—specifically, how we design our database to survive corruption, human error, or infrastructure failure.

Defining Your Recovery Objectives

Before you write a single backup script, you must define the business constraints. You cannot design a backup strategy without knowing the two core metrics of data protection:

• Recovery Point Objective (RPO): The maximum tolerable period in which data might be lost from an IT service due to a major incident. If your RPO is 1 hour, you must have a backup strategy that guarantees at least hourly snapshots or transaction log shipping.
• Recovery Time Objective (RTO): The targeted duration of time within which a business process must be restored after a disaster. If your RTO is 15 minutes, you cannot rely on restoring a 1TB database from cold storage; you need hot-standby replicas or rapid point-in-time recovery (PITR).

When working on a SaaS project, these aren't just technical choices; they are product features. A premium enterprise tier might demand an RPO of zero (no data loss) and an RTO of minutes, while a free tier might tolerate a daily backup (24-hour RPO).

Structuring Data for Easier Backups

Backup reliability is often a direct result of how you structure your data. If your database is one massive, monolithic blob, restoration takes longer and is more prone to failure.

1. Separate Static and Dynamic Data

If you have large assets like user-uploaded images or logs, store them in object storage (like S3) rather than the database. This keeps your database backup small and fast, significantly improving your RTO.

2. Logical Partitioning

As we discussed in Handling Large Data Sets, partitioning tables by time (e.g., audit_logs by month) allows you to back up or archive older, static partitions separately, reducing the impact of a full database restore.

3. Auditability

Backups are useless if the data inside them is corrupted. Ensure you have Modeling Audit Trails in place so that during recovery, you can verify if the restored state matches the expected business outcome.

Worked Example: Designing for Point-in-Time Recovery

Most modern databases (PostgreSQL, MySQL) rely on "Write-Ahead Logging" (WAL) for PITR. To enable this, your schema must remain consistent.

Imagine our SaaS user table. If we perform a bulk update, we want to ensure we don't accidentally corrupt the state.

SQL
-- Example: A safe way to update subscription status that leaves a trail
-- This allows us to use transaction logs to recover to the exact second 
-- before a bad deployment or human error.

BEGIN;

UPDATE subscriptions 
SET status = 'canceled', 
    updated_at = NOW() 
WHERE account_id = 123;

-- We log the action to a separate audit table
INSERT INTO audit_logs (event_type, account_id, change_details)
VALUES ('STATUS_CHANGE', 123, 'Canceled via user dashboard');

COMMIT;

By wrapping these in a single transaction, the database ensures that the backup logs treat the update and the audit log entry as an atomic unit. If you need to recover to a specific point, you can replay these logs with confidence.

Practice Exercise

1. Define your RPO/RTO: For your current SaaS project, assume you have 10,000 active users. Write down what your target RPO and RTO would be.
2. Schema Audit: Look at your database schema. Do you have any tables that are growing uncontrollably? Determine if those tables should be partitioned or moved to an archival strategy to improve your backup speed.

Common Pitfalls

• Testing Only the Backup: A backup is not a backup until it has been restored. Regularly test your restoration process to ensure your documentation and scripts actually work.
• Ignoring Transaction Logs: Many beginners focus only on "Full Dumps." If you don't back up your transaction logs, you cannot achieve a low RPO.
• Storing Backups on the Same Server: Never store your backups on the same disk or even the same cloud availability zone as the primary database. If the hardware fails, you lose both the data and the recovery path. For more on this, review Designing for Disaster Recovery: RTO, RPO, and Resilience.

FAQ

Q: Should I back up the whole database every night? A: It depends on the size. For small databases, yes. For large databases, use a strategy of weekly full backups combined with daily incremental backups or continuous transaction log archiving.

Q: How do I know if my schema design is "backup-friendly"? A: If your schema relies heavily on complex TRIGGER logic that creates side effects across many tables, recovery becomes difficult. Keep your data structure clean and your transactions atomic.

Q: Does using Handling Soft Deletes count as a backup? A: No. Soft deletes protect against accidental row deletion by the application, but they do not protect against total database failure or data corruption. Always maintain off-site backups.

Recap

Reliable data protection requires a clear understanding of RPO and RTO. By keeping your database schema lean, utilizing transaction logs, and separating dynamic data from static assets, you ensure that your recovery process is fast and predictable.

Up next: Designing for Multi-tenancy — comparing shared vs. isolated database strategies.