How to backup, restore and recover sap hana ? Tips and examples.
Backup, restore and recover sap hana step by step
Like any applications, sap hana data has to be saved. Hana backup is done at regular interval which would be normaly done on a daily basis. Recovery and restoration would happened occasionnaly and would be performed automatically. The hana adminitrator would take over the restoration procedure whenever the automated recovery fails.
Within companies a certain group of people or teams are assigned to perform very specific tasks. There is a specialised backup team which maintains and follows up sap hana backups on a continuous basis.
Free Sap Hana backup and recovery procedures
Sap hana backup is an essential requirement for any system. Whether it is a development or a production environment you do not want to lose any daily work and data. You have to make sure there is a valid backup done at regular time and you are able to restore your hana tenant databases from it.
Using hana sql tool "hdbsql" requires some security connexion. You can connect to a tenant database using a user name and a password, but the procedure is not secured as username and password will be visible and hard coded in the command line. To get round the issue, it is adviced to create a secured user key which will be used instead within the command line.
$> echo "backup data using FILE ('TBW-FULL-BACKUP')" > fullbackup.sql $> hdbsql -U AUSERSECUREDKEY -I fullbackup.sql
Tenant database TBW Full backup Type File Backup dir and File Prefix
$> hdbsql -U AUSERSECUREDKEY "backup data FOR TBW using FILE ('/apr/backup/data/DB_TBW/COMPLETE_DATA_BACKUP','TBW') COMMENT 'Full tenant database TBW data backup of Type FILE'"
Full SystemDb backup Type File Backup dir
$> hdbsql -U AUSERSECUREDKEY "backup data using FILE ('/apr/backup/data/SYSTEMDB/INC_DATA_BACKUP') COMMENT 'Full SYSTEMDB data backup of Type FILE'"
Full backup Type backint File Prefix
$> hdbsql -U AUSERSECUREDKEY "backup data using backint ('2016-12-16-DEV-TBW-FULL-BACKUP') COMMENT 'Full data backup of Type Backint'"
How to backup Hana database?
All DATA BACKUP can be executed in various ways using either Sql scripts, Sap hana Cockpit or Sap Hana studio.
LOGS BACKUP occur automatically when parameters "log_mode" is set to "normal" and "enable_auto_log_backup" is set to 'yes'
What are the different backup procedures in sap hana?
Standard backup methods exist in sap hana. Incremental, Differential, Full backup can be scheduled like any other type of databases backup.
With Sap Hana, you can save your database into file using the following syntax : backup data USING FILE('/adirectory/data/','a-file-prefix'). In that case, you have to make sure the target directory has plenty of space available.
You can also use a third-party backup tools and change the command line syntax to : backup data USING BACKINT.
How to Backup SAP HANA Database using Command Line?
Full tenant backup Type File
Non Secured connexion : $> /usr/sap/[SID]/HDB[nn]/exe/hdbsql -i [nn] -n [hostname]:3[nn]13 -d SystemDB -u SYSTEM -p [password] "BACKUP DATA FOR [tenant_DB] USING FILE ('Prefix-YYYY-MM-DD')
Secured connexion : $> /usr/sap/[SID]/HDB[nn]/exe/hdbsql -i [nn] -U [user-stored-key] "BACKUP DATA FOR [tenant_DB] USING FILE ('a-File-Prefix')
Full tenant backup Type File
Os level command line
$> #!/bin/bash $> export DATE_TIME=`date '+%d.%m.%Y-%Hh%M'` $> /usr/sap/HPC/HDB00/exe/hdbsql -i 00 -n linux06:30013 -d SystemDB -u SYSTEM -p APwd01 << EOF BACKUP DATA FOR TBW USING FILE ('$DATE_TIME') EOF
How to perform an incremental backup in sap hana?
Systemdb Incremental backup Type File
$> hdbsql -U AUSERSECUREDKEY "backup data INCREMENTAL using FILE ('/apr/backup/data/SYSTEMDB/INC_DATA_BACKUP')"
Tenant TBW Incremental backup Type File backup dir
$> hdbsql -U AUSERSECUREDKEY "backup data INCREMENTAL FOR TBW using FILE ('/apr/backup/data/TBW/INC_DATA_BACKUP')"
Incremental backup Type backint Type File backup dir
$> hdbsql -U AUSERSECUREDKEY "backup data INCREMENTAL using backint ('/apr/backup/data/TBW/INC_DATA_BACKUP','2016-12-23-DEV_TBW_INCREM_BACKUP')"
How to perform a differential backup in sap hana?
Differential backup Type backint
$> hdbsql -U AUSERSECUREDKEY "backup data DIFFERENTIAL using backint ('2016-12-23-DEV_TBW_DIFF_BACKUP')"
How to find sap hana backup and restoration references?
How to check and retrieve sap hana backup details?
The following sap hana sql scripts show various hana backup details. The important type of hana backups are the data backup and the log backup. Log backups are performed automatically when log-mode and automatic log backup are set. see also : show log mode and see also : show automatic log backup . Data backup has to be triggered either via hana Studio or via a third party software such as DD boost.
How to check hana backup status?
The following table provides SQL scripts to check current active sap hana backup, also to check whether all hana data backup have been completed successfully and whether hana log backups are occuring or not. A script shows the latest completed sap hana backup id which would be very useful when it is combined with other SQL queries. In case of a crash, a script provides all necessary backup files needed to recover a tenant database. Keep in mind, that type of script has to be executed at the end of each backup as you may not be able to run any script after a sap hana database crash.
Show Active backup
SELECT BACKUP_ID, ENTRY_TYPE_NAME, STATE_NAME,SYS_END_TIME from "SYS"."M_BACKUP_CATALOG" where ENTRY_TYPE_NAME='complete data backup' and STATE_NAME='running' order by 'SYS_START_TIME' desc;
Check log backup status
SELECT ENTRY_TYPE_NAME,STATE_NAME,SYS_END_TIME from "SYS"."M_BACKUP_CATALOG" where ENTRY_TYPE_NAME='log backup' order by 'SYS_END_TIME' desc;
Check all complete data backup status
SELECT ENTRY_TYPE_NAME,STATE_NAME,SYS_END_TIME,SYS_START_TIME from "SYS"."M_BACKUP_CATALOG" where ENTRY_TYPE_NAME='complete data backup' and STATE_NAME='successful' order by 'SYS_END_TIME' desc;
Show log segment status
SELECT * from M_LOG_SEGMENTS;
Show Backup ID number
SELECT to_bigint(BACKUP_ID), SYS_END_TIME, SYS_START_TIME, ENTRY_TYPE_NAME, STATE_NAME FROM SYS.M_BACKUP_CATALOG where ENTRY_TYPE_NAME != 'log backup' order by 'SYS_END_TIME' desc;
Show latest backup file names for a recovery
SELECT DESTINATION_PATH FROM M_BACKUP_CATALOG_FILES WHERE BACKUP_ID >= (SELECT TOP 1 BACKUP_ID FROM M_BACKUP_CATALOG WHERE STATE_NAME = 'successful' AND ENTRY_TYPE_NAME = 'complete data backup' ORDER BY UTC_START_TIME desc) ;
Writing shell script is not always easy for beginners. It is why we decided to include in this section shell script examples regarding sap hana backup. You can use those hana backup scripts as such or you can update them to fit your company needs.
In the section below you will find a sap hana data backup script and a purge sap hana backup catalog.