Backup & Recovery Architecture: Types, Catalog & Point-in-Time Recovery (PITR)
A definitive operational handbook for SAP HANA backup and recovery, covering Complete Data Backups, Differential/Incremental backups, automatic Redo Log archiving, Backup Catalog integrity, Storage Snapshots, and Point-in-Time Recovery (PITR).
Advanced (Senior DBA)Backup & Recovery~22 min studyHANA 2.0 (All SPS)
Core Learning Objectives
- 1.Master the 4 backup types: Full Data, Differential, Incremental, and Redo Log backups.
- 2.Understand how automated redo log archiving manages log segments (Writing, Closed, Backed Up, Free).
- 3.Explain Backup Catalog architecture and catalog housekeeping.
- 4.Execute a production-grade Point-in-Time Recovery (PITR) of a tenant database from SystemDB.
- 5.Understand SAP HANA Storage Snapshots and third-party Backint integration.
Prerequisites:hana-internals-persistence-savepointshana-architecture-multitenant-mdc
Definition & Architectural Rationale
Technical Definition:
Backup and recovery in SAP HANA provides the mathematical foundation for achieving near-zero Recovery Point Objective (RPO) and minimal Recovery Time Objective (RTO) through complete data backups combined with continuous automated redo log shipping and replay.
Why this component exists:
Hardware failures, storage array corruption, ransomware, accidental user drops (e.g. DROP TABLE), and logical data corruptions require the ability to restore the database to an exact past second prior to the incident.
Internal Working & Mechanics
1. Backup Types:
- Complete Data Backup: Full baseline image of all used data blocks in /hana/data. Unallocated space is skipped.
- Differential Backup: Captures all data pages changed since the LAST COMPLETE DATA BACKUP.
- Incremental Backup: Captures only data pages changed since the LAST DATA BACKUP (whether complete, differential, or incremental).
- Redo Log Backup: Automatic continuous archiving of closed log segments from /hana/log. Triggers whenever a 512 MB/1 GB segment fills up, or when the timer parameter log_backup_timeout_s (default: 900 seconds / 15 minutes) expires.
2. Redo Log Segment Lifecycle:
- WRITING: Currently receiving active committed transactions.
- CLOSED: Full or timeout elapsed; closed for new writes.
- BACKED UP: Log backup engine has streamed the segment to backup destination (/backup/log or Backint) and recorded the entry in the Backup Catalog.
- FREE: Segment is cleared and available for reuse by the database engine.
3. Backup Catalog (M_BACKUP_CATALOG):
- Maintains the complete historical tree of all data backups, log backups, LSN sequences, and timestamps.
- Stored internally in the database catalog and written to disk as backup_catalog.log in the log backup destination to ensure recovery is possible even if all database disks are obliterated!
4. Step-by-Step Point-in-Time Recovery (PITR) Lifecycle:
In HANA 2.0 MDC, recovering a tenant is initiated from SystemDB:
- Step 1: Stop the target tenant: ALTER SYSTEM STOP DATABASE PRD;
- Step 2: SystemDB reads the backup catalog to verify available backups and required redo logs.
- Step 3 (Restore): The complete data backup is restored into the tenant data volume (/hana/data).
- Step 4 (Replay): The engine replays differential/incremental backups and all log backups up to the exact requested UTC timestamp: RECOVER DATABASE FOR PRD UNTIL TIMESTAMP '2026-10-23 04:30:00'.
- Step 5 (Reset Logs): Uncommitted transactions past that timestamp are rolled back; log sequence numbers are reset.
- Step 6: Start the tenant: ALTER SYSTEM START DATABASE PRD;
Architecture Flow Diagram
Mermaid DiagramBackup & Recovery Architecture: Types, Catalog & Point-in-Time Recovery (PITR)
Architectural Flow: PITR restores the base full backup, applies incremental/differential deltas, and replays continuous log backups up to the exact target second before uncommitted transactions are rolled back.
Production SQL Diagnostics
Execute Complete Data Backup via SQLSafe (Read-Only)
Triggers a full baseline data backup of the current database to a designated filesystem destination.
Both
Privileges: BACKUP ADMIN
Target Engine: HANA 2.0 (All SPS)
BACKUP DATA USING FILE ('/backup/data/HDB_COMPLETE_MANUAL');
DBA Interpretation: Initiates a non-blocking full data backup. Database transactions continue unhindered during backup execution.
Execute Point-in-Time Recovery (PITR) from SystemDBModifying (State Change)
Restores a tenant database to a specific UTC timestamp using full data backup and sequential log backups.
SystemDB
Privileges: DATABASE ADMIN in SystemDB
Target Engine: HANA 2.0 (All SPS)
-- Step 1: Stop the tenant container
ALTER SYSTEM STOP DATABASE PRD;
-- Step 2: Recover database to exact timestamp
RECOVER DATABASE FOR PRD UNTIL TIMESTAMP '2026-10-23 04:30:00'
USING DATA PATH ('/backup/data')
USING LOG PATH ('/backup/log')
CHECK ACCESS USING FILE;
-- Step 3: Start the tenant after successful recovery
ALTER SYSTEM START DATABASE PRD;
DBA Interpretation: CHECK ACCESS validates that all backup files and consecutive log files up to 04:30:00 are readable before commencing data restoration.
Troubleshooting & Root Cause Analysis
Production Best Practices & Operational Risks
Mandatory Best Practices
- Never store database backups on the same physical disks as /hana/data or /hana/log; use dedicated NFS mounts, SAN LUNs, or Backint tools.
- Regularly validate point-in-time recovery on a sandbox or quality instance to test recovery duration and RTO assumptions.
- Always keep log_backup_timeout_s set between 900s (15m) and 1800s (30m) to guarantee maximum 15-minute data loss exposure (RPO).