Backup and restore SAP HANA using scripts

Making backups is easy, and most administrators do it. But only a successful restore from a backup can confirm that the backup is really good. In this article, we will perform a database restore using only the files from the backup.

There are two possible recovery scenarios. In the first, HANA becomes bad due to human errors, but the server itself is OK. The database can be restored using the HANA Studio's graphical interface (right-click, restore, etc.). Usually, there are no problems with this kind recovery.

In the second case, the reason for the recovery is the server's hardware problems. Of course, I have a backup of whole the server too, but suppose that we install the server from scratch and then install the HANA software. Now we need to restore the database, having only a backup copy. It is this scenario that we will consider.

NOTE:This case is about file storing backup, no BACKINT here.

Taking backup

The backup is performed by the BACKUP DATA command using hdbsql CLI. The backup can be obviously FULL, DIFFERENTIAL (only data changed since the full backup) and INCREMENTAL (data changed since any previous backup). In addition, HANA creates its own backup of current logs, similar to Oracle archive logs, every 15 minutes (by default) to the $DIR_INSTANCE/backup/log directory (by default). They are important for recovery to a certain point in time. Every time HANA performs a backup, it creates a copy of the Backup Catalog in the same ($DIR_INSTANCE/backup/log) location. This file is called like log_backup_0_0_0_0.1520355726710, where a large number is the epoch time for this copy of the catalog. The Catalog contains information about all previously made backups, including the previously mentioned archive logs

This place is easily becomes full if not cleaned by the administrator. I have a script that does compress not recent files and deletes too outdated files. However, the metadata about all of these backups remains in the Backup Catalog, which continues to grow. You can clear it with the hdbsql CLI command:

hdbsql=> BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID XXXX ;

The latest version of script do this automatically for you, leaving history in catalog only last for month.

Creating user BACKUP and passwordless access

For other purposes it is recommended to use other users, than SYSTEM. We will create a user named BACKUP in accordance with the recommendations:

hdbsql=> CREATE ROLE BACKUP_ROLE;
hdbsql=> GRANT BACKUP ADMIN,CATALOG READ,MONITORING to BACKUP_ROLE;
hdbsql=> CREATE USER BACKUP PASSWORD "ComplexPassword" NO FORCE_FIRST_PASSWORD_CHANGE;
hdbsql=> ALTER USER BACKUP DISABLE PASSWORD LIFETIME;
hdbsql=> GRANT BACKUP_ROLE to BACKUP;
hdbsql=> SELECT * FROM "SYS"."USERS"; 
hdbsql=> SELECT * FROM "PUBLIC"."EFFECTIVE_PRIVILEGES" WHERE USER_NAME = 'BACKUP';

You need to configure a connection without a password to the database, otherwise you will have to specify the password in your script, which is worse.

sidadm> hdbuserstore DELETE BACKUP
sidadm> hdbuserstore -i SET BACKUP localhost:30015 BACKUP
sidadm> hdbuserstore LIST
sidadm> hdbsql -U BACKUP "select * from dummy"

And, finally, a backup script itself. Schedule it to run by user <sid>adm as shown in example in scripts header. A destination directory DDIR has to exist and be writable by <sid>adm. Fix location to your need.

#!/bin/bash
# By Oleg Volkov (voleg)
# Copy it to sidadm/bin and run it as sidadm cronjob:
# 2 */6 * * * [ -x $HOME/bin/hana_backup_local.sh ] && $HOME/bin/hana_backup_local.sh 2>&1 | logger -t hana-backup

# Reload environment (important for cron jobs)
source $HOME/.bashrc

# This SQL return previous good full backup id from catalog:
SQL="SELECT top 1 min(to_bigint(BACKUP_ID))
FROM SYS.M_BACKUP_CATALOG
where SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP,-28)
and ENTRY_TYPE_NAME='complete data backup'
and STATE_NAME='successful' ;"

HDBSQL="$DIR_EXECUTABLE/hdbsql -U BACKUP"

DDIR="/hana/backup/raw" ; [ -d $DDIR ] || mkdir -v -m 751 $DDIR || exit 1
STAMP="$(date +%Y%m%d%H)"

# Cleanup old files (to free space before):
find $DDIR -type f -mtime +13 exec rm -fv "{}" \;

TYPE=""
if [ $(date "+%H") = "06" ] ; then
        # Make FULL on Sat (6th weekday)
        if [ $(date "+%w") -ne 6 ] ; then
                TYPE="DIFFERENTIAL"
        fi
else
        TYPE="INCREMENTAL"
fi

echo " ** Doing ${TYPE:-FULL} backup"
$HDBSQL "BACKUP DATA $TYPE USING FILE ('$DDIR/$STAMP');" || { echo "Backup failed, script aborted" ; exit 1 ; }

if [ -z $TYPE ] ; then
        # Clean backup catalog from outdated backups:
        IDFILE=$(mktemp)
        $HDBSQL -o $IDFILE -a $SQL
        BACKUPID=$(cat $IDFILE)
        rm -f $IDFILE

        if [ -z $BACKUPID -o 'x'$BACKUPID = 'x?'  ] ; then
                echo " ** No backup to purge from catalog"
        else
                echo " ** BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $BACKUPID"
                $HDBSQL "BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $BACKUPID"
        fi
fi

echo " ** Saving backup catalog"
BCATALOG=$(ls -1tr $DIR_INSTANCE/backup/log/log_backup_0_0_0_0*|tail -1)
cp -av $BCATALOG ${DDIR}/

Schedule it run hourly or less frequent. It is pre-configured to run a FULL backup in Sat at 06:xx, DIFF at 06:xx on rest days and INC in any other cases.

It remains only to take care of the backup to the tape directory /hana/backup/raw.

Restoring

As we said, we have a fresh installed HANA server ready to restore the database on it.

Restore /hana/backup/raw directory with latest files.

Restore from FULL backup

Locate the most recent FULL backup:

/hana/backup/raw # ls -1tr | egrep -v "log_backup|differential|incremental"
2018030119_databackup_0_1
2018030119_databackup_4_1
2018030119_databackup_2_1
2018030119_databackup_1_1
2018030119_databackup_3_1

As you can see, the prefix for the full backup in our case is 2018030119. Indeed, as you remember from the script, buckup done USING FILE ('$DDIR/$STAMP'). At the time of recovery, almost the same syntax will be used. First stop the database:

sidadm> HDB stop

Then, create a command file for restore using detected prefix:

sidadm> echo "RECOVER DATA USING FILE ('/hana/backup/raw/2018030119') CLEAR LOG" > $DIR_INSTANCE/work/recoverInstance.sql

Now clear HANA's log and trace files so that it's easier to read them, then run the instance:

sidadm> cdtrace && rm *
sidadm> HDB start

If you received the final OK, the restore was completed, otherwise check the file named like nameserver_HOSTNAME.30001.003.trc.

You can get the following error:
[5294]{-1}[-1/-1] 2018-03-04 21:34:55.913776 e Backup BackupMgr_Manager.cpp(06575) : RECOVER DATA finished with error: [448] recovery could not be completed, [110092] Recovery failed in nameserver startup: check services against topology failed: mismatch: 1 scriptservers configured in source topology, 0 in destination system

In this case, configure scriptserver start file, like:

sidadm> echo -e "[scriptserver]\ninstances = 1" > /usr/sap/$SAPSYSTEMNAME/SYS/global/hdb/custom/config/daemon.ini
then repeat all the steps: stop database, create recover command file, clean trace files and start the instance.

Restore from latest backup

First we need to restore the FULL backup. Other types of backup will attempt to roll forward volumes that still not exist in the initial database. If you have not yet restored FULL, return to the section above and do that.

Restoring DIFF and INC backups required Backup Catalog avaliable. Find last avaliable restored catalog, extract epoch time from name and convert it to date:

/hana/backup/raw> ls -1tr log_backup_0_0_0_0*|tail -1
log_backup_0_0_0_0.1520355726710
/hana/backup/raw> ls -1tr log_backup_0_0_0_0*|awk -F. '{t=$2}END{printf"%d\n",t/1000}'
1520355726
/hana/backup/raw> date -d @$(ls -1tr log_backup_0_0_0_0*|awk -F. '{t=$2}END{printf"%d\n",t/1000}') "+%Y-%m-%d %H:%M:%S"
2018-03-06 19:02:06

The last date will be UNTIL TIMESTAMP. Stop database:

sidadm> HDB stop

Then, create a command file for restore using detected UNTIL TIMESTAMP :

sidadm> echo "RECOVER DATABASE UNTIL TIMESTAMP '2018-03-06 19:02:06' USING DATA PATH ('/hana/backup/raw/') CLEAR LOG USING LOG PATH ('$DIR_INSTANCE/backup/log','/hana/backup/raw') " > $DIR_INSTANCE/work/recoverInstance.sql

Now clear HANA's log and trace files so that it's easier to read them, then run the instance:

sidadm> cdtrace && rm *
sidadm> HDB start

Updated on Wed Mar 7 23:12:06 IST 2018 More documentations here