# SAP HANA Backup SQL Statement Generator

> Source: https://netray.co/tools/sap-hana-backup-sql-statement-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Builds BACKUP DATA statements for a tenant (complete, differential, incremental, to file or Backint), queries to review the backup catalog and its size, and the catalog housekeeping statement.

## Category

sap

## Erp

SAP HANA

## Audience

- SAP HANA DBA
- SAP Basis administrator

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Tenant database name

**Key:** tenant

**Type:** text

**Default:** PRD

#### File backup path and prefix

**Key:** prefix

**Type:** text

**Default:** /hana/backup/data/DB_PRD/COMPLETE_DATA_BACKUP

#### Backint prefix

**Key:** bprefix

**Type:** text

**Default:** COMPLETE_DATA_BACKUP

#### Backup comment

**Key:** comment

**Type:** text

**Default:** Before kernel and support package update

#### Keep catalog entries for (days)

**Key:** keepdays

**Type:** number

**Default:** 30

#### Backup ID to keep from (from query 6)

**Key:** bid

**Type:** text

**Default:** 1729500000000

**Blocks:**

#### Run in SYSTEMDB as a user with BACKUP ADMIN

**Body:** -- 1. Complete data backup to file
BACKUP DATA FOR {{tenant}} USING FILE ('{{prefix}}') COMMENT '{{comment}}';

-- 2. Differential (changes since the last complete backup)
BACKUP DATA DIFFERENTIAL FOR {{tenant}} USING FILE ('{{prefix}}_DIFF');

-- 3. Incremental (changes since the last data backup of any type)
BACKUP DATA INCREMENTAL FOR {{tenant}} USING FILE ('{{prefix}}_INCR');

-- 4. Complete data backup through Backint (third-party tool)
BACKUP DATA FOR {{tenant}} USING BACKINT ('{{bprefix}}') COMMENT '{{comment}}';

#### 5. Last 20 catalog entries for the tenant (run in SYSTEMDB)

**Body:** SELECT TOP 20 ENTRY_ID, ENTRY_TYPE_NAME, BACKUP_ID, SYS_START_TIME, SYS_END_TIME, STATE_NAME, COMMENT
  FROM SYS_DATABASES.M_BACKUP_CATALOG
 WHERE DATABASE_NAME = '{{tenant}}'
 ORDER BY SYS_START_TIME DESC;

-- Size and location of each file of the latest backups
SELECT TOP 50 C.BACKUP_ID, C.ENTRY_TYPE_NAME, F.SOURCE_TYPE_NAME, F.DESTINATION_PATH,
       ROUND(F.BACKUP_SIZE / 1024 / 1024 / 1024, 2) AS SIZE_GB
  FROM SYS_DATABASES.M_BACKUP_CATALOG C
  JOIN SYS_DATABASES.M_BACKUP_CATALOG_FILES F
    ON F.DATABASE_NAME = C.DATABASE_NAME AND F.BACKUP_ID = C.BACKUP_ID
 WHERE C.DATABASE_NAME = '{{tenant}}'
 ORDER BY C.SYS_START_TIME DESC;

#### Run in the tenant {{tenant}}

**Body:** -- Size of the backup catalog (latest catalog backup)
SELECT TOP 1 C.SYS_START_TIME, ROUND(F.BACKUP_SIZE / 1024 / 1024, 2) AS CATALOG_MB
  FROM M_BACKUP_CATALOG C
  JOIN M_BACKUP_CATALOG_FILES F ON F.BACKUP_ID = C.BACKUP_ID
 WHERE F.SOURCE_TYPE_NAME = 'catalog'
 ORDER BY C.SYS_START_TIME DESC;

-- 6. Oldest successful complete data backup inside the retention window
SELECT TOP 1 BACKUP_ID, SYS_START_TIME
  FROM M_BACKUP_CATALOG
 WHERE ENTRY_TYPE_NAME = 'complete data backup'
   AND STATE_NAME = 'successful'
   AND SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP, -{{keepdays}})
 ORDER BY SYS_START_TIME ASC;

-- 7. Delete older catalog entries (catalog only; files stay on disk)
BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID {{bid}};
-- also delete the physical files: add WITH FILE, WITH BACKINT or COMPLETE
-- BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID {{bid}} WITH FILE;

**Notes:** Verify on your SPS in the SAP HANA SQL Reference (Backup and Recovery statements) and the System Views Reference. BACKUP CATALOG DELETE takes a literal backup ID, so run query 6 first and paste the result. In SYSTEMDB, BACKUP CATALOG DELETE FOR <tenant> addresses a tenant catalog. Point-in-time recovery needs log_mode normal and regular log backups in addition to data backups. COMMENT needs HANA 2.0.

## Content

**Intro:** This generator writes the SQL for SAP HANA 2.0 data backups of a tenant (complete, differential, incremental, to file or Backint) and the queries to check the backup catalog, its size and clean it up. Fill in tenant name, paths and retention.

**Sections:**

#### How to use it

**Body:** Enter the tenant name, the file path and prefix or the Backint prefix, a comment and the number of days of catalog history to keep. Run the BACKUP DATA statements from SYSTEMDB with a user holding BACKUP ADMIN (BACKUP OPERATOR can run backups but not delete catalog entries). Use query 5 to confirm the backup is successful and see file sizes. Then connect to the tenant, check the catalog size, run query 6 to find the oldest complete backup you want to keep, paste its BACKUP_ID into the field and run statement 7 to remove older entries.

**Bullets:**

- Never delete the catalog entry of the newest successful complete backup.
- Keep at least one recoverable complete backup plus its logs off the host.
- Test a restore regularly; a backup that never restored is a hope.

#### Complete, differential and incremental

**Body:** A complete data backup contains all data pages and is the base for every recovery. A differential backup holds everything changed since the last complete backup, so recovery needs the complete plus the latest differential. An incremental backup holds changes since the last data backup of any kind, so recovery needs the complete plus every incremental in sequence. Differentials grow over the week; incrementals stay small but make recovery chains longer. Log backups are separate and automatic when log_mode is normal; they provide point-in-time recovery between data backups.

#### Why the backup catalog size matters

**Body:** The backup catalog records every data and log backup. After each backup HANA writes the whole catalog as its own backup, which is why it appears in M_BACKUP_CATALOG_FILES with SOURCE_TYPE_NAME catalog. With log backups every 15 minutes and no housekeeping, the catalog can grow to hundreds of megabytes, and every log backup then also writes that catalog, slowing log backup and filling the backup area. Each tenant and SYSTEMDB has its own catalog, so check each. HANA Cockpit retention policies or the HANACleaner script can automate the cleanup this generator does by hand.

#### Gotchas

**Body:** BACKUP CATALOG DELETE does not accept a subquery: find the ID, then paste it. Without WITH FILE, WITH BACKINT or COMPLETE, only catalog records are removed and the files stay, so storage is not freed. Deleting the wrong range can remove the only full backup a recovery would need. Backint prefixes are relative to the tool configuration, not a file system path. Schedule backups from HANA Cockpit or DBA Cockpit (DB13) rather than cron where possible so they are logged and visible to the whole team.

**Cta:** If you want your Basis team to ask your HANA systems about backup status and catalog size in plain English, on your own servers, we can set that up.

**Faq:**

#### What is the SQL to back up a SAP HANA tenant database?

**Answer:** From SYSTEMDB run BACKUP DATA FOR <tenant> USING FILE ('<path and prefix>'); for a complete backup, or USING BACKINT ('<prefix>') for a third-party tool. Add DIFFERENTIAL or INCREMENTAL after BACKUP DATA for delta backups. Check the result in SYS_DATABASES.M_BACKUP_CATALOG.

#### How do I check the size of the SAP HANA backup catalog?

**Answer:** Join M_BACKUP_CATALOG with M_BACKUP_CATALOG_FILES on BACKUP_ID and filter SOURCE_TYPE_NAME = 'catalog'; the BACKUP_SIZE of the latest row is the current catalog size. SAP Note 1969700 also contains a HANA_Backups_CatalogSize script. Check each tenant and SYSTEMDB separately.

#### How do I delete old entries from the HANA backup catalog?

**Answer:** Find the BACKUP_ID of the oldest complete backup you want to keep, then run BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID <id>. Add WITH FILE, WITH BACKINT or COMPLETE to also delete the backup files. You need BACKUP ADMIN. HANA Cockpit retention policies can do this on a schedule.

#### Where is the SAP HANA database administration guide?

**Answer:** The SAP HANA Administration Guide and the SAP HANA SQL Reference Guide are on help.sap.com under SAP HANA Platform, per SPS. Backup syntax is in the SQL Reference under Backup and Recovery statements, and the catalog views are in the System Views Reference.

## Search terms

sap hana backup sql, sap hana size of backup catalog, sap hana backup catalog delete, backup data for tenant, sap hana differential backup sql, sap hana database administration guide, sap hana admin guide

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json