Backup and Recovery: Scheduling Scripts – by the SAP HANA Academy

The SAP HANA Academy has published a new video in the series SAP HANA SPS 7 Backup and Recovery.

Backup and Recovery – Scheduling Scripts | SAP HANA

SAP HANA Studio includes a convenient Backup Wizard to make ad hoc backups, for example, before a system upgrade, or before an overnight large data load. However, for daily scheduled backups in the middle of the night, this wizard is not suitable.

For scheduled backups, there are several options:

• DBA Planning Calender [transaction DB13] in DBA Cockpit [ transaction DBACOCKPIT ], see SAP Netweaver documentation
• SAP HANA XS Job Scheduler, see the video by Thomas Jung Job Scheduling on the SAP HANA Academy and  SAP HANA Developer Guide Tutorial: Scheduling an XS Job, p. 415.
• SAP HANA client-based: hdbsql on a Windows computer using Task Manager. This approach is nicely demonstrated in this SCN blog by Rajesh
  (please note that a user key should be used in the batch command and never user name with password, as correctly remarked by Lars Breddemann in the commentary)
• SAP HANA server-based: hdbsql with the Suse Linux crontab.

In this video you can learn how to implement the last option: scheduling scripts using  cron.

Low privileged user

To run scheduled scripts, a dedicated user is created with the BACKUP OPERATOR system privilege. As documented in the SQL Reference, this system privilege only authorizes the use of the BACKUP command and nothing more. The privilege was introduced in SPS 6, and “allows you to implement a finer-grained separation of duties if this is necessary in your organization.” (What’s New in SAP HANA Platform, Release Notes, p. 41).

Below the script we used to create the user. The default HANA security configuration will require the password to be changed on first logon. As this concerns a technical user with no interactive logon, we have disabled password lifetime. The security requirements of your organization may differ.

create user backup_operator password Initial1;
grant backup operator to backup_operator;
alter user backup_operator disable password lifetime;

The SQL file is attached to this document.

Use User Store Key for save computing

Next, we create a user store key. You should never enter a password on the command line on a Linux system as it will be recorded in the history file. With hdbuserstore you can generate a key to securely store the connection information for a particular system and a particular user. See an early discussion on secure computing with HANA by Lars Breddemann. How to avoid recording passwords to the history file, is discussed in this SUSE conversation.

The -i flag is for interactive. The tool will prompt the user to enter the password. The key can have any name you want. We used a simple key: “backup”, as hdbuserstore does not like underscores. Next parameter is the system host name and TCP port. The port is the regular indexserver (SQL) port with format 3 + system instance number + 15. The last parameter is for the user.

hdbuserstore -i SET backup hana:30115 backup_operator

Backup Script

Next step is to create a backup script. You can find several examples in the SAP Notes and by SAP partners:

• SAP Note 1950261 SAP HANA Database Backup Policy Recommendations and Regular Backup Script
• SAP Note 1651055 – Scheduling SAP HANA Database Backups in Linux
• Symantec TECH Note 209343 Scheduling HANA backups from NetBackup

Unfortunately, the Symantec note and SAP Note 1950261 do not implement hdbuserstore but require a password in the backup script. As discussed, this is not a recommended approach.

SAP Note 1651055  does implement hdbuserstore but then proposes a shell script so sophisticated that it requires the user to have the more powerful BACKUP ADMIN system privileges and not the recommended BACKUP OPERATOR. Or at least, this is my guess – the script contains over 1000 lines of code.

Although the script is extensively documented, unless you are a skilled BASH shell programmer you may be a little challenged here.

In our implementation we have deliberately kept it simple. The script file is attached to this document.

#!/bin/bash
# define backup prefix
TIMESTAMP="$(date +\%F\_%H\%M)"
BACKUP_PREFIX="SCHEDULED"
BACKUP_PREFIX="$BACKUP_PREFIX"_"$TIMESTAMP"
# source HANA environment
. /usr/sap/shared/DB1/HDB01/hdbenv.sh
# execute command with user key
# asynchronous runs job in background and returns prompt
hdbsql -U backup "backup data using file ('$BACKUP_PREFIX') ASYNCHRONOUS"

As recommended by the SAP HANA Administration Guide (p. 286), one needs to use unique name prefixes for each data backup, e.g. a unique timestamp, otherwise, an existing data backup with the same name will be overwritten by the next data backup. In the BASH script we use the Linux shell command date with a certain format mask. The environment is sourced so we do not need to provide the full path to the hdbsql command. You do need to adapt the path to the hdbenv.sh script, of course, unless you install SAP HANA to /usr/sap/shared and choose DB1 as SID.

Note that the procedure documented in the SAP HANA Administration Guide (p. 305), includes the requirement to install the SAP HANA client for hdbuserstore and hdbsql. This section will be updated as the requirement is not correct: any regular SAP HANA appliance installation performed with the Unified Installer (as of SPS 5) or with Lifecycle Manager (since SPS 7)  includes the client;  plus both tools are also part of the server installation (at least as of version SPS 7).

Schedule with cron

Finally, the cron scheduler is discussed. In case you are not familiar with cron, the SLES KB article 3842311 How to schedule scripts or commands with cron on SuSE Linux  or the SLES 11 Administration Guide on the cron package maybe helpful.

Here we have a sample crontab. Syntax is minute, hour, day, month, and day of week, followed by the command.

[ 0 0 * * * ] translates to minute zero, hour zero, any day, any month, any day of week, would result in a daily backup a midnight, while [14 17 17 1 5 ] translates to 5:14 pm on 17-JAN Friday.

As this syntax may be a bit obscure for the non-initiated, SLES also provides a convenient way to schedule hourly, daily, weekly or monthly scripts: just drop the script in the appropriate directory!

This provides no control on the time of execution for the end-user, although the system administrator could specify exact times (using crontab, yes).

Another advantage of using the cron.daily approach could be that you do not need to know how to work with editors like VI on Linux. Just write your backup script on a WIndows computer in Notepad and transfer the script using FTP, WinSCP, or the file copy tool of your liking.

Otherwise the default editor on SLES for crontab is VI, so keep your reference at hand:

[ i ] for insert

[ H ] Move one character to the left

[ J ] Move one line down

[ : ] [ w ] to write and [ q ] to quit.

Is the iPhone generation still paying attention … 😉

SAP HANA Academy

Denys van Kempen