DB2 - Backup and Recovery

DB2 - Modern Backup and Recovery Strategies

This chapter covers the essential strategies and commands for backing up and recovering a Db2 database, a critical skill for any database administrator.

Core Concepts: RPO, RTO, and Logging

A robust backup strategy is built on two key metrics: Recovery Point Objective (RPO), which is the maximum acceptable amount of data loss, and Recovery Time Objective (RTO), which is the maximum acceptable downtime. Your choice of logging method in Db2 directly impacts your ability to meet these objectives.

At a high level, a backup is a complete copy of your database data and metadata at a specific point in time. Recovery is the process of using that backup, along with transaction logs, to restore the database to a consistent state.

Circular Logging (Default)

In this mode, log files are treated as a ring buffer. When the last log file is full, Db2 overwrites the first one. This is simple and requires minimal disk space for logs. However, it only supports crash recovery and offline backups. You cannot perform a point-in-time recovery. This mode is suitable for development or test environments where data loss is not critical.

Archive Logging (Production Standard)

In this mode, Db2 archives log files to a separate location when they become full, instead of overwriting them. This creates a complete history of all transactions. Archive logging is a prerequisite for online backups and point-in-time recovery, making it essential for production databases where minimizing data loss (low RPO) is crucial.

Performing Database Backups

Db2 offers flexible backup options to suit different needs.

Offline (Cold) Backup

An offline backup requires the database to be deactivated, ensuring no active connections. This creates a perfectly consistent snapshot. It’s the simplest form of backup but requires application downtime.

# Ensure no applications are connected
db2 list applications
# If there are connections, force them off
db2 force application all

# Deactivate the database
db2 deactivate db sample

# Perform the backup
db2 backup database sample to /backup/location

# Backup successful. The timestamp for this backup image is: 20231027103000

Online (Hot) Backup

An online backup can be taken while the database is active and serving users. This is possible only if the database is configured for archive logging. It provides high availability by eliminating the need for downtime for backups.

First, let’s enable archive logging. This change requires an offline backup to be taken immediately after.

# Check current logging status
db2 get db cfg for sample | grep LOGARCHMETH1
# It will show 'LOGARCHMETH1 = OFF' for circular logging.

# Create a directory for archived logs
mkdir -p /db2/logs/archive

# Update the database configuration to enable archive logging
db2 update db cfg for sample using LOGARCHMETH1 'DISK:/db2/logs/archive'

# IMPORTANT: You must take an offline backup immediately after enabling archive logging.
db2 backup database sample to /backup/location

Once archive logging is enabled and the initial backup is complete, you can perform online backups.

# Perform an online backup, compressing the image and including necessary logs
db2 backup database sample online to /backup/location compress include logs

# Backup successful. The timestamp for this backup image is: 20231027110000

Verifying and Managing Backups

A backup is useless if it’s corrupted. Always verify your backup images.

# Verify the integrity of a backup file
db2ckbkp /backup/location/SAMPLE.0.db2inst1.DBPART000.20231027110000.001
# Image Verified.

# List the history of backups taken for a database
db2 list history backup all for sample

Restoring and Recovering the Database

Restoring is the process of bringing the database back from a backup image. The RESTORE command is used for this.

# Restore the database from the latest backup in a directory
db2 restore database sample from /backup/location

# Or restore from a specific backup image using its timestamp
db2 restore database sample from /backup/location taken at 20231027110000

After restoring from an online backup, the database will be in a ‘rollforward pending’ state. This means you must apply the archived transaction logs to bring it to a consistent state. This is called rollforward recovery.

# Rollforward to the end of the available logs
db2 rollforward db sample to end of logs and stop

# To recover to a specific point in time (e.g., just before a user error)
db2 rollforward db sample to 2023-10-27-12.00.00.000000 using local time and stop

Best Practices and Advanced Topics

Automation: Script your backup procedures and schedule them using cron (Linux/UNIX) or Task Scheduler (Windows).
Regular Testing: Periodically test your recovery procedure by restoring a backup to a different server to ensure your backups are valid and you know the process.
Cloud Storage: Modern strategies often involve backing up to cloud object storage (like AWS S3 or IBM COS) for off-site protection and durability.
Incremental Backups: For very large databases, consider incremental or delta backups, which only back up data that has changed since the last full backup, saving time and space.
High Availability (HADR): For near-zero RPO and RTO, Db2’s High Availability Disaster Recovery (HADR) feature provides a standby database that is kept in sync with the primary through log shipping. This is the gold standard for mission-critical systems.