Skip to Content
Author's profile photo Denys van Kempen

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

Assigned tags

      18 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Willem Lourens
      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

      Author's profile photo Denys Kempen
      Denys Kempen
      Blog Post Author

      Thanks Willem,

      That is great info!

      Author's profile photo Willem Lourens
      Willem Lourens

      Hi,

      Is it possible to backup the logs from command line?

      Author's profile photo Denys Kempen
      Denys Kempen
      Blog 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.

      Author's profile photo Willem Lourens
      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.

      Author's profile photo Former Member
      Former Member

      Hi willem,

      I am getting authorization error while doing backup as below

      authorization error.JPG

      Author's profile photo Kartik Natarajan
      Kartik Natarajan

      Hi Denys,

      Do you have script for C-shell.

      regards,

      Kartik

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi,

      See the BACKUP DATA Statement (Backup and Recovery) in the SAP HANA SQL and System Views Reference: https://help.sap.com/saphelp_hanaplatform/helpdata/en/75/a06c444e9a4b3287a46a6a40b4ee69/content.htm

      BACKUP DATA [<backup_delta>] [FOR <database_name>]
         <data_backup_definition> [<additional_option_list>]
      
      <backup_delta> ::= INCREMENTAL | DIFFERENTIAL 
      
      BACKUP DATA INCREMENTAL USING FILE;
      BACKUP DATA DIFFERENTIAL USING FILE;
      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi David,

      Could you try

      su -ndbadm -c "/usr/sap/NDB/HDB00/exe/hdbcons mm gc -f”
      Author's profile photo Former Member
      Former Member

      Hi Denys, Its Done!! Thanks

      Author's profile photo Former Member
      Former Member

      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.

       

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Doug,

      Certainly sounds like it. You can attach it to your comment/question.

      Maybe add a header with your e-mail so people can contact you (and add a disclaimer not to put the dog in the microwave)

      You might find this post interesting as well: https://blogs.sap.com/2014/11/27/new-and-final-version-of-backup-script/

      Thanks,
      Denys

      Author's profile photo Saravanan Venkatachalam
      Saravanan Venkatachalam

      Dear Experts,

      In my Server log backups are happening. log backup services not running.

      please provide the steps to start the services

       

      Regards,

      Saravanan

      Author's profile photo Umair Tambe
      Umair Tambe

      Hi,

      Thanks for these useful steps!

      Can you we use same procedure in multi tenant Database ? what will be additional steps ?

       

       

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Umair,

      A few things might have changed since 2014 ...

      Former SAP HANA product manager, Richard Bremer, wrote on the topic that writing a backup scripts was "one of the most stupid things I’ve done in my life".

      These script tend to require a lot of maintenance, might not address rules and exceptions very well, etc.

      In other words, it is "as-is", "use at your own risk" ,etc.

      There are some great tools like SAP HANA cockpit and many third-party tools that can be used for backups. Most importantly, these tools will also provide support in case you ever need to restore a backup.

      When using scripts, the support will have to come from you. In case you decide to rely on homebrew scripts for backup and recovery, make sure your management understands the risks and supports this decision.

       

       

       

      Author's profile photo Umair Tambe
      Umair Tambe

      Hi Denys,

       

      Thanks for your reply , I will try to use SAP HANA cockpit as per your suggestion 🙂