Database Backup and Recovery

A backup is a copy of data that can be used to restore the database after data loss or corruption.

Concept

A backup is a copy of data that can be used to restore the database after data loss or corruption. Recovery is the process of bringing the database back to a consistent, usable state — possibly to a specific point in time.

Threats backups protect against: hardware failure, accidental deletes (DELETE without WHERE), application bugs, ransomware, corruption, site disasters, and bad deployments.

Why It Matters

A database without tested backups is a single mistake away from permanent data loss. Replication is not a backup: a mistaken DROP TABLE replicates instantly.

Syntax

Exact backup commands are DBMS-specific and often run outside SQL (command-line tools, cloud consoles). Representative examples:

# PostgreSQL logical backup / restore
pg_dump -Fc -d shopdb -f shopdb_2026-09-29.dump
pg_restore -d shopdb_restored shopdb_2026-09-29.dump
# PostgreSQL physical base backup (for PITR with WAL archiving)
pg_basebackup -D /backups/base_2026-09-29 -Fp -Xs -P

# MySQL logical backup / restore
mysqldump --single-transaction --routines --triggers shopdb > shopdb_2026-09-29.sql
mysql shopdb < shopdb_2026-09-29.sql

# SQLite
sqlite3 app.db ".backup 'app_2026-09-29.db'"

-- SQL Server (T-SQL)
BACKUP DATABASE shopdb TO DISK = 'D:\backup\shopdb_full.bak' WITH COMPRESSION, CHECKSUM;
BACKUP DATABASE shopdb TO DISK = 'D:\backup\shopdb_diff.bak' WITH DIFFERENTIAL;
BACKUP LOG shopdb TO DISK = 'D:\backup\shopdb_log_1015.trn';
RESTORE DATABASE shopdb FROM DISK = 'D:\backup\shopdb_full.bak' WITH NORECOVERY;
RESTORE DATABASE shopdb FROM DISK = 'D:\backup\shopdb_diff.bak' WITH NORECOVERY;
RESTORE LOG shopdb FROM DISK = 'D:\backup\shopdb_log_1015.trn'
    WITH STOPAT = '2026-09-29 10:14:00', RECOVERY;

-- Oracle: RMAN (Recovery Manager) commands
-- RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
-- RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
-- RMAN> RESTORE DATABASE; RECOVER DATABASE UNTIL TIME "TO_DATE('2026-09-29 10:14','YYYY-MM-DD HH24:MI')";

Example

32.1 Backup types What is copied Restore one backup Changes since the last backup of any type Full + every incremental in order Changes since the last full backup Medium (grows over time) Full + latest differential Transaction log / WAL / archive log Log records of all changes

Replay logs after restoring a base backup Depends on write volume

SQL statements / data export (pg_dump, mysqldump) Slower for large DBs Re-run DDL + load data Portable across versions Data files / blocks Fast for large DBs Copy files back, replay logs Same version/platform Storage/cloud volume snapshot Depends on provider Example weekly schedule:

32.2 Point-in-time recovery (PITR) PITR restores the database to an exact moment — typically just before a mistake.

Requirements: a base backup plus an unbroken chain of transaction logs (PostgreSQL WAL archiving with recovery_target_time, SQL Server FULL recovery model with log backups, MySQL binary logs with mysqlbinlog --stop-datetime, Oracle ARCHIVELOG mode with RMAN).

Often it is safer to restore to a separate server and copy back only the lost rows, rather than rewinding the whole production database.

32.3 RPO and RTO RPO — Recovery Point Objective How much data (measured in time) can we afford to lose?

Backup/log frequency, replication RTO — Recovery Time Objective How long can the system be down?

Restore speed, standby servers, automation Nightly full backup

Full + frequent log backups + hot standby Synchronous replication to another site + backups + automated failover Vehicle test result archive Daily full + hourly logs, off-site copy

32.4 Recovery strategy and disaster recovery A recovery strategy documents how each failure is handled:

Accidental DELETE of some rows PITR to a separate instance; copy rows back Corrupted data file Restore from backup + replay logs Server hardware failure Fail over to standby replica Region/data-center outage

Disaster recovery site / cross-region replica Restore from immutable/offline backups Bad deployment/migration Restore or apply reverse migration

Disaster recovery (DR) plans cover losing an entire site: geographically separate backups, standby databases (PostgreSQL streaming replication, SQL Server Always On availability groups, Oracle Data Guard, MySQL replication/InnoDB Cluster), DNS/connection failover, runbooks, and regular DR drills.

The 3-2-1 rule: keep at least 3 copies of data, on 2 different media/storage types, with 1 copy off-site (and ideally one immutable/offline copy).

32.5 Verifying backups

Sun   FULL
Mon   INCR (changes since Sun)        |  DIFF (changes since Sun)
Tue   INCR (changes since Mon)        |  DIFF (changes since Sun)
Wed   INCR (changes since Tue)        |  DIFF (changes since Sun)
...
Restore to Wednesday night:
  Incremental strategy:  Sun FULL + Mon INCR + Tue INCR + Wed INCR
  Differential strategy: Sun FULL + Wed DIFF
+ transaction/WAL logs every 5–15 minutes for point-in-time recovery

08:00  full/base backup
08:00 → 10:14 normal transactions (logs archived continuously)
10:15  someone runs: DELETE FROM orders;   ← mistake
Recovery:
  1. Restore base backup (08:00 state)
  2. Replay archived logs up to 10:14:59
  3. Open database → orders are back; changes after 10:14:59 must be re-applied or recovered separately

last good backup/log          disaster               service restored
 ──────────●────────────────────────────✖──────────────────────────●──────────►
           |<──── data loss (RPO) ────>|<──── downtime (RTO) ─────>|

"A backup that has never been restored is not a backup."

- Automate periodic test restores to a separate environment.
- Run consistency checks (SQL Server DBCC CHECKDB, pg_amcheck, RMAN VALIDATE).
- Compare row counts / checksums between source and restore.
- Measure actual restore time against the RTO.
- Monitor backup job success and alert on failures.
- Encrypt backups and protect their access credentials.

Expected Result

After a PITR restore of the orders table to 10:14:59 into a separate database shopdb_recovered, the lost rows are copied back:

Row counts before the mistake, after the mistake, and after recovery: 6 → 0 → 6.

-- PostgreSQL example using dblink/postgres_fdw or an exported CSV; conceptually:
INSERT INTO orders
SELECT * FROM recovered.orders r
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_id = r.order_id);

Common Mistakes

Treating replication or RAID as a backup.

Never testing restores.

Keeping backups on the same server/disk as the database.

Not backing up transaction logs (making PITR impossible) or letting logs grow unbounded.

Forgetting to back up users, roles, jobs, and configuration.

mysqldump without --single-transaction on InnoDB (inconsistent snapshot or long locks).

Unencrypted backups containing sensitive data.

Best Practices

Define RPO/RTO with the business; design backups to meet them.

Combine full + differential/incremental + log backups.

Follow 3-2-1; keep an immutable copy.

Automate, monitor, and regularly test restores.

Document runbooks and practice DR drills.

Interview Relevance

Medium (high for DBA/DevOps roles): "Full vs incremental vs differential?", "What is point-in-time recovery?", "Define RPO and RTO", "Is replication a backup?", "How would you recover from an accidental DELETE?".

Practice Questions

With full backups on Sunday and incrementals daily, which files are needed to restore Thursday's state?

Design a backup plan for an RPO of 15 minutes and an RTO of 2 hours.

Explain why replication does not protect against DROP TABLE.

What additional objects besides tables must be included in a backup?

Describe the steps to recover rows deleted accidentally 30 minutes ago.