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
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
Thanks Willem,
That is great info!
Hi,
Is it possible to backup the logs from command line?
Yes, it’s possible to trigger a log backup using the HANA management console “hdbcons”:
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:
I would not use for any other purpose.
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.
Hi willem,
I am getting authorization error while doing backup as below
Hi Denys,
Do you have script for C-shell.
regards,
Kartik
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
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
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
Hi David,
Could you try
Hi Denys, Its Done!! Thanks
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.
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
Dear Experts,
In my Server log backups are happening. log backup services not running.
please provide the steps to start the services
Regards,
Saravanan
Hi,
Thanks for these useful steps!
Can you we use same procedure in multi tenant Database ? what will be additional steps ?
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.
Hi Denys,
Thanks for your reply , I will try to use SAP HANA cockpit as per your suggestion 🙂
Hallo ,
How to create backup Pollices with in a Backup Script. Like I have one Requirement
Full backup should run on sunday and rest day differential or incremental backup should run , how to achieve this ?
suggestions are welcome.
Regards,
Arpit
Hi Arpit,
May I suggest to post your question(s) to the SAP Community forum? The community is monitored 24/7 by the topic area experts both from SAP, its partners, and customers, which also allows for knowledge sharing.
I monitor several tags and try to respond when I can. Suggest to tag with 'sap hana'
Thanks for contributing!
https://answers.sap.com