HANA Backup Automation

      Comments Off on HANA Backup Automation

Now a days, most of the customers were using HANA environments for there reporting and analytical activities.

Sometimes, we don’t want to login to each and every database to check the backup status. In order to automate this boring activity, we need to be familiar with database tables and basic understanding of SQL queries.

All the HANA database backup information can be accessible from the BACKUP CATALOG tables.

M_BACKUP_CATALOG – Contains BACKUP ID, STATUS ,START & END TIME

M_BACKUP_CATALOG_FILES – BACKUP ID and Size of the backup volume files

If we are running SQL queries for each column using SIMPLE SELECT state from single table will lead us to the wrong entries during complex test cases. In order to have accurate information, we need to use complex SQL queries.

SQL Query for entire details of the database:

select id,start_time,end_time,status,sum(b_Size) as "Size in GB" from (select c.backup_id as id ,c.sys_start_time as start_time,c.sys_end_time as end_time,c.state_name as status,round(f.backup_size/1024/1024/1024,2) as b_Size
from m_backup_catalog_files f, m_backup_catalog c where
f.source_type_name in ('volume','topology') and f.service_type_name in ('indexserver','nameserver','xsengine')
and c.backup_id = f.backup_id
and c.entry_type_name in ('complete data backup','incremental data backup')
order by f.backup_id desc) group by id,start_time,end;

You can download the SQL file from here.

Running the SQL statement in HANA database:

We need to run the SQL from the Solution Manager for all the HANA databases.

If you don’t have solution manager, create DB connection in ABAP systems and run the SQL query from the SQL Editor.

SQL Editor

Note: CATALOG READ privilege need to provided to the database user maintained in the DB connection

We can perform the same activity in HANA Cockpit which has in-built functionality to run SQL queries on all databases which are connected to HANA cockpit.