A database backup is a copy of your data, schema, and configuration captured at a specific point in time, stored separately from the live database so it can be used to reconstruct the database if the original is damaged, corrupted, or destroyed. Restore is the process of taking that backup and rebuilding the database from it. These two operations form the complete safety net for any SQL system, yet they are among the most misunderstood and under-tested activities in database administration.
The gap between understanding backups and running them reliably in production is wide. This post closes that gap by following a single case study through every decision, command, and verification step. By the end, you will have a complete mental model for backup and restore that you can adapt to any SQL database.
The Scenario: Meridian Analytics
Meridian Analytics runs a mid-sized PostgreSQL database that tracks customer engagement metrics for a SaaS platform. The database holds approximately 500 GB of data across transaction tables, user profiles, and a dozen reporting views. Their current backup procedure consists of a single full backup taken every Sunday at 2 AM, stored on the same server as the database itself.
One Tuesday morning, a developer runs a migration script that unintentionally deletes the engagement_events table — three years of customer interaction data. The incident response team has no backup from Monday or Tuesday, and the Sunday backup is 40 hours old. The restore takes six hours because the backup was stored on the same disk as the live database, and restoring it required shutting down the database to free up space.
The outcome: 40 hours of lost data, six hours of downtime, and a permanent ding on the company’s reliability record.
This scenario contains every major failure mode in backup strategy. Let’s go through the fixes in order.
Step 1: Understanding the Backup Types
Before choosing a strategy, you need to know what tools exist. Every major SQL database — PostgreSQL, MySQL, SQL Server, Oracle — offers three fundamental backup approaches. They differ in what they capture, how long they take, and how much data you can lose in a disaster.
Full backups copy the complete database: all data, indexes, schema objects, stored procedures, and configuration. They are the foundation of any strategy because they provide a complete starting point for restoration. The downside is speed and size — a full backup on a 500 GB database takes hours and consumes correspondingly large amounts of storage.
Incremental backups capture only the data that has changed since the last backup of any type. They are fast and small, but they create a chain of dependency: to restore a full database, you need the last full backup plus every incremental backup taken since. Break any link in that chain, and the restore fails.
Differential backups capture everything that changed since the last full backup. They sit between full and incremental in size and speed. Restoring requires only the last full backup plus the most recent differential — a simpler chain than incrementals, at the cost of larger backup files.
For Meridian’s situation, the ideal mix looks like this: a weekly full backup, a daily differential, and transaction log backups every 15 minutes. The transaction logs are the third category, and they deserve special attention.
Transaction log backups record every individual database change (INSERT, UPDATE, DELETE) since the last log backup. They run almost continuously and are tiny compared to full backups. Their purpose: point-in-time recovery. With logs, you can restore the database to any moment — not just the moment a backup finished, but the exact minute, or even second, just before a disaster occurred.
Meridian’s problem was twofold: the Sunday full backup was too old, and they had no logs to fill the gap. The fix requires all three types working together.
Step 2: Designing the Right Backup Schedule
The schedule is the difference between losing 40 hours of data and losing 15 minutes. The math is straightforward: the maximum data loss in a disaster equals the time between your most frequent backups. If you back up logs every 15 minutes, you can never lose more than 15 minutes of committed transactions.
For Meridian, the schedule becomes:
- Full backup: Every Sunday at 2 AM.
- Differential backup: Every day at 2 AM, right after the daily aggregation jobs finish.
- Transaction log backups: Every 15 minutes, around the clock.
Why not run full backups more often? The 500 GB size makes a nightly full backup impractical — it would consume the entire backup window and saturate the disk I/O. Daily differentials capture the day’s changes efficiently. The 15-minute logs capture the final layer of granularity.
Here is the critical rule: every backup type has a purpose in the recovery hierarchy. Full backups establish the baseline. Differentials compress the log chain — instead of replaying hundreds of log files, you restore the differential, then apply only the logs since that differential. Logs provide the point-in-time precision.
The backup schedule is only half of the design. The storage location is the other half.
Step 3: The Storage Location Trap
Meridian stored its only copy of the backup on the same physical disk as the live database. In the incident, restoring required freeing disk space — which meant deleting the very backup needed for the restore.
The principle here is separation: backups must live on a different storage system than the live database. At minimum, use a different physical disk or a network-attached storage volume. Better yet, push backups to a different server or a cloud object store like S3.
This is the 3-2-1 rule: 3 copies of your data, on 2 different storage types, with 1 copy offsite. Applied to Meridian:
- Copy 1: The live database on its primary server.
- Copy 2: The most recent full backup on a separate backup server.
- Copy 3: A cloud-based backup archive, retained for 30 days.
The offsite copy matters for a different failure mode: physical disaster. A fire, flood, or hardware failure that takes down the primary server may also take down a backup server in the same rack. The cloud copy survives independently.
Step 4: Executing the Backup Operations
Now the concrete commands. Every database has its own syntax, but the concepts are identical. For PostgreSQL, the full backup is handled by pg_dump:
pg_dump -h localhost -U backup_user -Fc meridian_db > /backups/meridian_full_$(date +%Y%m%d).dump
The -Fc flag produces a custom-format archive that supports selective restore and compression. For the daily differential in PostgreSQL, the standard tool is pg_basebackup — it produces a full binary copy, and the differential concept is handled by a combination of pg_basebackup plus write-ahead log (WAL) archiving.
A practical differential approach: run pg_basebackup for the weekly full, then rely on continuous WAL archiving for everything after. The WAL files are the transaction logs. Configure WAL archiving in the PostgreSQL config file:
archive_mode = on
archive_command = 'cp %p /backups/wal_archive/%f'
This sends every WAL segment to the backup directory within seconds of it being filled. In PostgreSQL, this effectively makes every change available for point-in-time recovery.
For MySQL, the equivalent tools are mysqldump for a logical full backup and mysqlbackup or the enterprise backup tools for physical backups. SQL Server uses the BACKUP DATABASE and BACKUP LOG statements. The underlying principles — what you’re capturing, where you’re storing it, and how often — remain constant across all of them.
The schedule itself is typically implemented as a cron job or a scheduled task. A simple cron entry for Meridian:
# Full backup every Sunday at 2 AM
0 2 * * 0 /usr/local/bin/run_full_backup.sh
# Differential (WAL archive is always on; this handles daily housekeeping)
0 2 * * 1-6 /usr/local/bin/run_daily_housekeeping.sh
# Log backup every 15 minutes
*/15 * * * * /usr/local/bin/run_log_backup.sh
Each script should do three things: run the backup command, verify the backup file exists and has non-zero size, and log the result. The verification step is where most teams fall short.
Step 5: Verification — The Step Everyone Skips
Here is the uncomfortable truth about backups: a backup that has never been restored is not a backup at all. It is a collection of bytes of unknown validity. Every database administrator has a variant of this story — the backup that looked perfect on disk, right up until the moment it was needed, and then failed to restore.
The verification protocol should be two-tiered.
Tier 1: Automated checks after every backup. Confirm the backup file has a non-zero size, verify the file checksum matches the source data, and check that the backup job completed without error. These checks are cheap and catch the most common failure modes — disk full, permission errors, network timeouts.
Tier 2: Periodic test restores. At least once a month, take the most recent backup and restore it into a fresh, isolated environment. Run a sample query against the restored data. Verify row counts in critical tables. Then discard the test environment. This validates the entire restore path: the backup file, the restore command, and the ability of your team to perform a restore under time pressure.
Meridian would have caught its problem with either tier. The Sunday backup was probably valid; the failure was the schedule and the missing logs. But a test restore would have exposed the disk space issue long before the real incident.
Step 6: The Restore Process in Detail
When disaster strikes, the restore procedure must be scripted and rehearsed. The steps are deterministic; the panic is not. Having a written runbook removes the improvisation.
For Meridian, restoring to the point of the failed migration involves four operations:
Step 6.1: Stop the database. No new writes can occur during a restore. Issue a clean shutdown or place the database in a restricted mode.
pg_ctl stop -D /var/lib/postgresql/data
Step 6.2: Restore the last full backup. This places the database back to the moment the Sunday 2 AM backup completed.
pg_restore -h localhost -U admin -d meridian_db -c /backups/meridian_full_20260713.dump
Step 6.3: Apply the differential. If Meridian used daily differentials via pg_basebackup, the restore merges those changes. In the WAL-based approach, this step is implicit — the archive contains all WAL segments since the full backup.
Step 6.4: Replay the transaction logs to the precise recovery point. This is where point-in-time recovery happens. The goal is to restore up to the moment just before the destructive migration ran.
pg_ctl start -D /var/lib/postgresql/data -o "-P -t '2026-07-15 11:32:45'"
The -t flag (or recovery_target_time in the configuration file) tells PostgreSQL to replay WAL segments only up to that exact timestamp. The migration ran at 11:33 AM; the restore targets 11:32:45 AM, preserving everything committed up to 15 seconds before the disaster.
Without transaction logs, the restore can only reach the last full or differential backup. For Meridian, that meant losing the entire day of data between the Sunday backup and the Tuesday incident. With 15-minute logs, the maximum data loss shrinks to a quarter hour.
Step 7: Applying This to Your Own Database
The Meridian case generalizes to any SQL database, regardless of vendor. Walk through these four questions to design your own backup architecture:
What is the maximum data loss you can tolerate? This determines your log backup frequency. If a minute of lost data is unacceptable, logs must run every minute. If losing a day is tolerable, a daily full backup alone may suffice.
How long can you afford to be down? This determines your backup size and restore complexity. Large full backups take hours to restore. A differential plus logs restore faster than replaying a week of incrementals.
Where will backups live? The 3-2-1 rule is a minimum standard, not an option. The offsite copy is non-negotiable for real disaster protection.
When did you last test a restore? If the answer is “never,” your backup strategy is theoretical. Schedule a full restore test this week.
The Real Cost of Untested Backups
The Meridian incident cost the company roughly $80,000 in engineering time, lost productivity, and customer confidence. The backup solution — a nightly differential, 15-minute logs, and offsite storage — would have cost about $300 per month in additional infrastructure and a few hours of setup time.
The gap between those numbers is the argument for testing, scheduling, and verification. A backup strategy that has never been exercised under realistic conditions is a plan for failure. The restore rehearsal is not a compliance checkbox; it is the moment you discover the disk space issue, the permission problem, or the syntax error that would otherwise surface during an actual outage.
What database are you running, and what is the current gap between your most recent backup and your maximum tolerable data loss? Share those two details and I can sketch a concrete backup schedule and restore runbook for your specific setup.