SQL 100 🛢️ Backup, Restore, and Disaster Recovery Strategies - KandZ

⏱️ Estimated read: 15 min | |

A database backup is not a single artifact. It is a strategy composed of different backup types, each solving a different part of the recovery problem. A full backup captures the entire database at a point in time. A differential backup captures only the pages that changed since the last full backup. A transaction log backup captures the individual transactions that occurred since the last log backup, allowing recovery to a specific moment—a point in time between the full backup and the disaster . The strategy is the combination of these types, scheduled according to the Recovery Point Objective (RPO), which defines how much data loss is acceptable, and the Recovery Time Objective (RTO), which defines how long recovery may take.

The distinction between physical and logical backups is the second fundamental choice. A physical backup copies the database files themselves—data files, WAL or binary logs, control files. It is fast, it can be restored to a specific point in time with transaction logs, and it is the only option for large databases where a logical dump would take hours . A logical backup exports the data as SQL statements or a platform-specific dump. It is portable across versions and platforms, and it allows selective restoration of individual tables, but it cannot support point-in-time recovery on its own because it does not capture the transaction log stream .

Key point: A backup strategy is defined by RPO and RTO, and it combines full backups (the base), differential or incremental backups (the changed pages since the last full backup), and transaction log backups (the individual transactions since the last log backup); point-in-time recovery requires a continuous, unbroken chain of transaction log backups from the base backup to the target moment.

Why a single backup type is never sufficient

The full backup cost problem. A full backup of a 1 TB database takes hours and consumes 1 TB of storage. Running it every hour is impossible. Running it once a week leaves a week of data at risk if the server fails on day six. The solution is to combine a weekly full backup with daily differential backups and frequent transaction log backups. The full backup provides the base. The differential backup captures the changes since the base. The log backups capture the changes since the last differential or log backup . This hierarchy minimizes the time and storage required while keeping the RPO small.

The point-in-time problem. A full backup restored on its own returns the database to the moment the backup was taken. Any transaction committed after that moment is lost. A transaction log backup contains the individual transactions that occurred after the previous log backup. Applying the log backups in sequence replays those transactions, bringing the database forward to a specific moment—the moment before an accidental DELETE, the moment before a failed migration, or the moment before the disk failed . Without the log chain, the database can only be restored to the last full or differential backup, not to an arbitrary point.

The corruption problem. A backup that has never been tested is not a backup. The restore procedure must be verified regularly, or the first discovery of a corrupted backup will be during an actual disaster. The verification includes checking that the backup file is readable, that the restore completes without errors, and that the restored database passes consistency checks. A common pattern is to restore the backup to a staging environment on a schedule and run the application’s test suite against it.

The offsite problem. A backup stored on the same disk as the database is not a disaster recovery strategy. If the disk fails, both the database and the backup are lost. The backup must be copied to a different physical location—a different server, a different data center, or object storage in a different region. The archive_command in PostgreSQL can be configured to copy WAL files to remote storage, and cloud platforms provide managed backup storage that is replicated across availability zones .

a. Full, differential, and transaction log backups

The three backup types form a hierarchy. The full backup is the base. The differential backup reduces the number of full backups needed. The transaction log backup reduces the data loss window to minutes or seconds.

A full backup copies every page of the database to the backup device. It contains enough information to restore the database to the moment the backup completed. It is the starting point for every recovery sequence . In SQL Server, the full backup establishes the beginning of the log chain. In PostgreSQL, the base backup is the physical copy of the data directory taken with pg_basebackup, and it is the starting point for WAL replay .

A differential backup contains only the pages that changed since the last full backup. It is smaller and faster than a full backup, and it reduces the number of full backups required. The recovery sequence is: restore the last full backup, then restore the last differential backup, then apply the transaction log backups . A differential backup is cumulative: each one contains all changes since the full backup, not just the changes since the last differential.

A transaction log backup contains the individual transaction log records generated since the last log backup. It is the mechanism that enables point-in-time recovery. The log chain is the continuous sequence of log backups from the full backup to the current moment. If the chain is broken—a log backup is missing or corrupted—the database cannot be recovered past the break . Log backups also truncate the transaction log, preventing it from growing without bound.

b. Point-in-time recovery: the mechanism

Point-in-time recovery (PITR) restores a database to its state at an arbitrary moment between the base backup and the present. The mechanism differs by engine, but the principle is the same: restore the base backup, then replay the transaction log records up to the target moment.

PostgreSQL uses continuous archiving of WAL (write-ahead log) files. The wal_level must be set to replica or logical, archive_mode must be on, and archive_command must copy each completed WAL segment to the archive location . The recovery process restores the base backup, sets restore_command to fetch WAL files from the archive, and specifies a recovery_target_time or recovery_target_lsn in postgresql.conf. PostgreSQL replays WAL records until the target is reached and then promotes the database to read-write .

MySQL uses binary log files. Binary logging must be enabled before the base backup is taken. The recovery process restores the base backup (a physical snapshot or a mysqldump output), then applies the binary logs using mysqlbinlog filtered by time or position. The command mysqlbinlog --start-datetime="2026-10-18 10:00:00" binlog.000001 | mysql replays only the transactions that occurred after the specified moment . The binary logs must be stored on a different physical device from the data files; otherwise, a disk failure destroys both the data and the logs needed to recover it .

SQL Server uses transaction log backups and the STOPAT clause. The recovery sequence is: restore the last full backup with WITH NORECOVERY, restore the last differential backup with WITH NORECOVERY, then restore each transaction log backup with WITH STOPAT = '2026-10-18T10:00:00', RECOVERY. The STOPAT clause tells SQL Server to replay transactions only up to the specified moment and then recover the database . The recovery model must be FULL or BULK_LOGGED; the SIMPLE model does not support log backups and therefore does not support point-in-time recovery .

Oracle uses RMAN with archived redo logs. The RECOVER DATABASE UNTIL TIME command applies archived logs and online redo to bring the database to the specified moment. RMAN’s incremental backups can be used to update a base image copy, reducing the amount of redo that must be applied during recovery .

c. Disaster recovery: beyond the database

A backup strategy protects the data. A disaster recovery strategy protects the service. The distinction matters when the failure is not a corrupted table but a lost data center, a ransomware attack, or a cloud region outage.

The offsite requirement. The backup must be stored in a location that is not affected by the failure that destroys the primary. For on-premises databases, this means a different server or a tape archive sent offsite. For cloud databases, this means a different region or a different provider’s object storage. The archive_command in PostgreSQL can invoke a script that uploads WAL files to S3 or another object store, and the script should handle retries and logging .

The recovery time objective. The RTO determines how quickly the service must be restored. A cold standby—a server that is provisioned but not running—has a longer RTO than a warm standby—a replica that is running and can be promoted. A hot standby—a synchronous replica—has the shortest RTO but the highest cost. The choice depends on the business requirement, not on the technical preference.

The restore test. The only way to know that a disaster recovery plan works is to test it. The test should simulate the failure: restore the backup to a clean environment, apply the logs, verify the data, and measure the time it takes. A plan that has never been tested is a plan that will fail when it is needed.

Complete Example Session

The following session demonstrates a complete backup and point-in-time recovery sequence in PostgreSQL, from the base backup through WAL archiving to the recovery to a specific moment.

-- ============================================
-- PART 1: CONFIGURE WAL ARCHIVING
-- ============================================
-- postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'
-- %p is the WAL file path, %f is the filename
-- The archive directory must exist and be writable

-- ============================================
-- PART 2: TAKE THE BASE BACKUP
-- ============================================
-- From the shell, as the postgres user:
-- pg_basebackup -D /var/lib/postgresql/backup -Ft -z -P
-- -D is the destination, -Ft is tar format, -z is gzip, -P is progress
-- This creates a physical copy of the data directory

-- ============================================
-- PART 3: GENERATE SOME DATA (SIMULATED)
-- ============================================
CREATE TABLE accounts (id INT PRIMARY KEY, balance INT);
INSERT INTO accounts VALUES (1, 1000), (2, 2000);
-- Note the current timestamp: 2026-10-18 10:00:00

-- ============================================
-- PART 4: A DISASTER OCCURS
-- ============================================
-- At 10:05:00, someone runs:
DROP TABLE accounts;
-- The table and its data are gone

-- ============================================
-- PART 5: RESTORE THE BASE BACKUP
-- ============================================
-- Stop PostgreSQL
-- Extract the base backup to the data directory
-- rm -rf /var/lib/postgresql/data/*
-- tar -xzf /var/lib/postgresql/backup/base.tar.gz -C /var/lib/postgresql/data

-- ============================================
-- PART 6: CONFIGURE RECOVERY TARGET
-- ============================================
-- Create recovery.signal (PostgreSQL 12+) in the data directory
-- postgresql.auto.conf:
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2026-10-18 10:04:00'
-- The target is one minute before the DROP TABLE
-- PostgreSQL will replay WAL until this moment

-- ============================================
-- PART 7: START AND VERIFY
-- ============================================
-- Start PostgreSQL
-- It enters recovery mode, applies WAL, and promotes to read-write
-- Verify the table exists:
SELECT * FROM accounts;
-- Output: id 1 balance 1000, id 2 balance 2000
-- The DROP TABLE at 10:05:00 was not replayed

-- ============================================
-- PART 8: PREVENT RECURRENCE
-- ============================================
-- The recovery worked because:
-- 1. WAL archiving was enabled before the backup
-- 2. The base backup was taken
-- 3. The archive directory was on a different disk
-- 4. The recovery target was before the disaster

The eight parts demonstrate the full sequence: WAL archiving configuration, base backup, data generation, disaster, base restore, recovery target configuration, recovery verification, and the conditions that made recovery possible. What the session demonstrates is that point-in-time recovery depends on a continuous chain of WAL files from the base backup to the target moment, and the recovery target must be set before the destructive operation.

Quick Reference

Backup Types

Type	Content	Purpose
Full	All database pages	Base for recovery
Differential	Pages changed since last full	Reduce full backup frequency
Transaction log	Individual transactions since last log	Point-in-time recovery

Point-in-Time Recovery Mechanisms

Engine	Mechanism	Target Specification
PostgreSQL	WAL archiving	`recovery_target_time`, `recovery_target_lsn`
MySQL	Binary logs	`--start-datetime`, `--stop-datetime`
SQL Server	Transaction log backups	`WITH STOPAT = 'time'`
Oracle	Archived redo logs	`RECOVER DATABASE UNTIL TIME`

RPO and RTO

Term	Meaning	Determines
RPO	Recovery Point Objective	How much data loss is acceptable
RTO	Recovery Time Objective	How long recovery may take

Critical Requirements

Requirement	Why
Full recovery model	Log backups unavailable in SIMPLE
WAL/binary logging enabled	Required for PITR
Archive on separate storage	Disk failure must not destroy logs
Continuous log chain	Missing log breaks recovery
Regular restore test	Untested backup is not a backup

Best Practices

✅ Do This:

-- Enable WAL archiving before the base backup
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'

-- Take a base backup regularly
pg_basebackup -D /backup -Ft -z

-- Test the restore procedure on a schedule
-- Restore to a staging environment and run tests

-- Store backups offsite
archive_command = 'aws s3 cp %p s3://bucket/%f'

❌ Don’t Do This:

-- Store backups on the same disk as the database
-- (disk failure destroys both)

-- Use SIMPLE recovery model if PITR is required
-- (log backups are impossible)

-- Assume a backup works without testing it
-- (corruption is discovered during a disaster)

-- Delete log backups before verifying the full backup
-- (breaks the log chain)

Common Pitfalls

Pitfall	Why It Happens	Fix
Cannot recover past a point	Missing log backup	Verify log chain integrity
Backup corrupt	Not tested	Regular restore tests
Log chain broken	Log backup skipped	Monitor log backup success
RPO too large	Infrequent log backups	Increase log backup frequency
RTO too long	Cold standby only	Warm standby or replica

Real-World Examples

1. PostgreSQL base backup

pg_basebackup -D /backup -Ft -z -P

2. PostgreSQL WAL archive

archive_command = 'cp %p /archive/%f'

3. PostgreSQL recovery target

recovery_target_time = '2026-10-18 10:04:00'

4. MySQL binary log recovery

mysqlbinlog --stop-datetime="2026-10-18 10:04:00" binlog.000001 | mysql

5. SQL Server point-in-time restore

RESTORE LOG db FROM DISK = 'log.trn' WITH STOPAT = '2026-10-18T10:04:00', RECOVERY;

6. SQL Server restore with NORECOVERY

RESTORE DATABASE db FROM DISK = 'full.bak' WITH NORECOVERY;

7. Oracle RMAN recovery

RECOVER DATABASE UNTIL TIME '2026-10-18:10:04:00';

8. Verify backup

pg_verifybackup /backup

9. Offsite copy

aws s3 cp /archive/ s3://backup-bucket/ --recursive

10. Restore test

Restore to staging, run application tests, verify data integrity

Visual

Backup Hierarchy

┌──────────────────────────────────────────────┐
│  FULL BACKUP (Sunday)                        │
│  ┌────────────────────────────────────────┐  │
│  │ All database pages                     │  │
│  └────────────────────────────────────────┘  │
│         │                                    │
│         ▼                                    │
│  DIFFERENTIAL (Monday-Thursday)              │
│  ┌────────────────────────────────────────┐  │
│  │ Pages changed since Sunday             │  │
│  └────────────────────────────────────────┘  │
│         │                                    │
│         ▼                                    │
│  TRANSACTION LOG (every 15 min)              │
│  ┌────────────────────────────────────────┐  │
│  │ Individual transactions                │  │
│  │ → Point-in-time recovery               │  │
│  └────────────────────────────────────────┘  │
└──────────────────────────────────────────────┘

Point-in-Time Recovery

┌──────────────────────────────────────────────┐
│  Full backup (Sunday 1 PM)                   │
│         │                                    │
│         ▼                                    │
│  Log backups applied in sequence             │
│  → Monday, Tuesday, Wednesday...             │
│         │                                    │
│         ▼                                    │
│  STOP at target time (before the disaster)   │
│         │                                    │
│         ▼                                    │
│  Database recovered to that moment           │
└──────────────────────────────────────────────┘

The Log Chain

┌──────────────────────────────────────────────┐
│  Full → Log1 → Log2 → Log3 → ... → Now       │
│         │      │      │                      │
│         ▼      ▼      ▼                      │
│  Continuous chain required for PITR          │
│  Missing a log breaks the chain              │
│  Recovery stops at the last continuous log   │
└──────────────────────────────────────────────┘

RPO and RTO

┌──────────────────────────────────────────────┐
│  RPO (data loss)                             │
│  ├─ 0 → synchronous replication, expensive   │
│  ├─ 15 min → log backups every 15 min        │
│  └─ 24 hours → daily full backup             │
│                                              │
│  RTO (downtime)                              │
│  ├─ minutes → hot standby                    │
│  ├─ hours → warm standby                     │
│  └─ days → cold restore from backup          │
└──────────────────────────────────────────────┘

Summary

Item	Value
Full backup	Base for recovery
Differential	Changed pages since full
Transaction log	Individual transactions since last log
Point-in-time recovery	Restore base, replay logs to target
PostgreSQL PITR	WAL archiving + `recovery_target_time`
MySQL PITR	Binary logs + `--stop-datetime`
SQL Server PITR	Log backups + `WITH STOPAT`
RPO	Acceptable data loss
RTO	Acceptable downtime
Log chain	Continuous sequence from full backup

Key takeaways:

A backup strategy is a hierarchy, not a single backup. The full backup is the base. The differential backup reduces the number of full backups needed. The transaction log backup enables point-in-time recovery and keeps the log size manageable .
Point-in-time recovery requires a continuous log chain. The database can only be recovered to a moment that is covered by an unbroken sequence of log backups from the base backup. A missing log breaks the chain, and recovery stops at the break .
The log chain must be stored separately from the data. If the disk that holds the database also holds the logs, a disk failure destroys both the data and the ability to recover it. The logs must be archived to a different device or to remote storage .
The recovery model determines what is possible. SQL Server’s SIMPLE model does not support log backups, so it cannot support point-in-time recovery. PostgreSQL’s wal_level must be set to replica or higher, and archive_mode must be on .
The RPO and RTO drive the schedule. A small RPO requires frequent log backups. A small RTO requires a warm or hot standby. The schedule is determined by the business requirement, not by the technical convenience.
An untested backup is not a backup. The restore procedure must be verified on a schedule. The test includes restoring to a clean environment, applying the logs, and verifying the data. A plan that has never been tested will fail during a disaster.

Remember: Backup and disaster recovery are not the same thing. A backup is a copy of the data. A disaster recovery strategy is the plan for restoring the service when the primary environment is destroyed. The backup is the foundation, but the strategy includes the offsite copy, the standby server, the recovery procedure, and the test that proves it works. The RPO and RTO are the requirements that shape the strategy. The full backup is the base, the differential reduces the number of full backups, and the transaction log enables point-in-time recovery. The log chain is the fragile part—it must be continuous, it must be stored separately, and it must be verified. When the disaster happens, the recovery is not a matter of if the backup exists, but whether the chain is complete and the procedure has been practiced.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!