Skip to Content

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 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







Screen Shot 2014-01-28 at 12.40.13.png

Backup Script

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

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.

Screen Shot 2014-01-28 at 13.37.23.png

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).

Screen Shot 2014-01-28 at 13.39.51.png

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

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. Willem Lourens

    Hi,

    Thanks for a simple but very effective backup script.

    I implemented the NetBackup script on a multitenant environment using “::= BACKUP DATA FOR <database_name> …”  for each database. The backup scripts are called from NetBackup so can database backups can now scheduled by the Backup App.

    Something that was not mentioned in the Symantec link, is that you need to set HANA Parameter data_backup_parameter_file to a valid .utl file for Netbackup to work, and also you need to perform this links for backint:

    ln -s /usr/openv/netbackup/bin/backint /usr/sap/<SID>/SYS/global/hdb/opt/hdbbackint” + “ln -s /usr/openv/netbackup/bin/backint /usr/sap/<SID>/SYS/global/hdb/backint

    Kind Regards

    (0) 
        1. Denys van Kempen Post author

          Yes, it’s possible to trigger a log backup using the HANA management console “hdbcons”:

          hdbcons -e hdbindexserver ‘log backup –iw’

          With this call, the open log segment of the associated service will be closed and backed up. If there is more than this log segment to be backed up, the call will return only when all segments were backed up.

          However, it is a support tool, as documented in the Administration Guide:

          hdbcons is a command line tool with which commands can be executed against running processes using a separate communication channel. It is intended for problem analysis by SAP HANA development support.

          I would not use for any other purpose.

          (0) 
          1. Willem Lourens

            Hi,

            Thanks for the feedback.  I will then rather not create a script for the log backups and leave the scheduling to Netbackup as on the other database types.

            (0) 
  2. Jyoti Arya

    Hello

    I would like to know how can we add the backup_type in the script. We would like to implement differential backup on daily basis and full backup once a week for our production server. What do we need to add in the script for backup type parameter

    Thanks

    Jyoti

    (0) 
  3. David Almazan

    Hi Denys,

    Im trying to create a script that runs the garbage collector utility but I cant achieve this, I have the next instruction:

    su -ndbadm -c /usr/sap/NDB/HDB00/exe/hdbcons “mm gc -f”

    It opens the hdbcons but it dont execute the mm gc -f do you have any idea?

    Regards!!
    David

    (1) 
  4. Doug Rainey

    Denys,
    I have a bash script that automates many of the backup functions like: deleting old backups based on date or number of backups, discovering multi tenant databases and backup them all up, support for Incremental and Differential, failure detection and alerting via EMail, use of the secure user store as per your video, etc…  It works as shown below.  Is meant to be run via cron.  Do you think there would be interest in posting this somewhere?

    uh1adm@xxxxsqlv001:/usr/sap/UH1/scripts> hana_database_backup -h

    usage: hana_database_backup -d number of days to keep backup before delete -D databases to backup -n number of backups to keep -x databases to exlude -I -F
    All parameters are optional
    -d defaults to 7
    -n defaults to 7
    -D Comma separated, no spaces, defaults to all databases
    -X Comma separated, no spaces, valid only if -D not specified
    -P Prune only, no backup performed.
    -B Backup only, no prune performed.
    -I An incremental backup will be performed.
    -F A differentail backup will be performed.

     

    (1) 

Leave a Reply