Skip to Content

+++ UPDATE 23.11.2011 +++

After the release of revision 20 (SPS 03) of the HANA database, some more changes concerning command syntax and system object naming had been identified.
The commands in this blog post have been adapted to reflect this.

+++ UPDATE 23.11.2011 +++

+++ UPDATE 24.10.2011 ++++
I’ve been informed that with the current HANA revisions, starting with rev.16 which is available for download in the SAP Service Marketplace since 10th of September, a altered syntax for taking backups is used.
So, to make them easier to follow in the future, I changed the examples in this blog accordingly.
+++ UPDATE 24.10.2011 ++++

When you check the SAP In-Memory Database Backup and Recovery Guide on https://help.sap.com/hana you’ll find on page 13 (4.3.2 Using SQL Commands) a first reference to a command line sql client:
hdbsql.

The example that follows explains how to create a very simple SQL script that can be fed into the hdbsql tool in batch mode.
That way a super simple schedulable backup command is created, e.g. for scheduling via CRON or any other scheduler tool.

This is what we find in the PDF (syntax updated – see update note above):

-----------8<------snip--------8<----------
[...]
Create a file backup.sql with the following content:
connect -n <server name> -i <instance number> -u <user> -p <password>
//
BACKUP DATA USING FILE ('Monday')
//
Store this file with read rights for the crontab user.
Execute the backup by calling the file backup.sql:
/usr/sap/<SID>/HDB<instance number>/exe/hdbsql -I backup.sql
[...]
-----------8<------snip--------8<----------

Of course this is what most customers and partners do and use in their first HANA implementation.
And in the cases I’ve seen, for -u <user> the SYSTEM user was used.
This basically leads to a situation where there is a plain text sql script file that contains the full logon data for the SYSTEM user of your HANA box.

NOT GOOD! NOT AT ALL!

But are there alternatives? Sure there are!

Step 1 (and keep it repeating like a mantra) – Never use user SYSTEM for plain operation tasks like backups.

Instead create a backup operater user that is not allowed to do anything else then performing backups.
Let’s check how to do this step by step:

Logon to the HANA box as SYSTEM. You can do all the steps as well in HANA Studio, but as I assume most of you already are familiar with the user management there, so I present the command line version here (I chose a reddish color for the SYSTEM account sessions, just to make it a bit easier to recognize them – definitively NOT for decorative purposes…):

C:\\Program Files\\SAP\\hdbclient>hdbsql -U S
Welcome to the SAP HANA Database interactive terminal.

Type:  \\h for help with commands
       \\q to quit

hdbsql HAN=> select user() from dummy
CURRENT_USER
"SYSTEM"

1 row selected (0 usec)

So, I’m logged on as SYSTEM user. Great! Let’s go and create the user for backups!
(+++ 24.10.2011 – changed the logon from -U b to -U S more clarity +++)

hdbsql=> create user backop password 123
* 412: invalid password layout
: minimal password length is [8] SQLSTATE: HY000

Ok, I always forget – we have a password rule build into HANA and minimal password lenght is 8 characters.
Let’s try again!

hdbsql HAN=> create user backop password 12345678
* 412: invalid password layout: password has to meet the rule ['A1a'] SQLSTATE: HY000

Grrr… I forgot again… this is really something I don’t like about password rules… but anyway: one more try!

hdbsql HAN=> create user backop password Aa12345678
0 rows affected (0 usec)

Success! Yes! Time for the first coffee break! (Ok, just kiddin’ we’re nearly done…)
Let’s connect with this user and try to take a backup!

hdbsql=> \\connect -n vml3012 -i 00 -u backop -p Aa12345678
Connected to HAN@vml3012:30015
hdbsql HAN=>
hdbsql HAN=> BACKUP DATA USING FILE ('BACKME')
* 414: user is forced to change password: alter password needed for current user SQLSTATE: HY000

This is also something I already had a few support messages for.
The very first time you logon with a specific user to HANA you are forced to change your password.
Unfortunately there usually is no dialogue for this (it’s present only in HANA Studio but which end-user has access to this?), so one either has to have access to a SQL command line (like we happen to have right now) or somebody must have taken care of this, by e.g. implementing a little macro in Excel for this.
Anyhow, there should be a better general solution, but for now just move on and change the password:

hdbsql HAN=> alter user backop password Back1234
0 rows affected (0 usec) 

Worked like a charme. Next step: take the backup!

hdbsql HAN=> BACKUP DATA USING FILE ('BACKME')
* 258: insufficient privilege: Not authorized SQLSTATE: HY000

Dang! We didn’t provide any of the required system privileges yet.

In a separate command line window I logon again as SYSTEM and run the following:

hdbsql -U S

Welcome to the SAP HANA Database interactive terminal.

Type:  \\h for help with commands
       \\q to quit
hdbsql HAN=> grant BACKUP ADMIN to backop
0 rows affected (1 usec)

That was easy cheesy, wan’t it?
Back to our backop-session and try once again:

hdbsql HAN=> BACKUP DATA USING FILE ('BACKME')
0 rows affected (12 usec)

HOOORAY!

We finally did it! Now we’ve got a safe user that can logon to the HANA DB but is only able to create backups.
Very nice.
Still we would have to put the logon data into the backup script file. But we have a second step to go…

Step 2 – Create and use a user store entry

The MaxDB veterans amongst the readers of this blog will already have recognized it:
HDBSQL is a ported version of SQLCLI the command line SQL client for SAPs very own MaxDB.
SQLCLI had a way to store user credentials and logon data called XUSER (I even wrote about it way back…).
And guess what: this had been ported as well.
It’s called hdbuserstore now but works pretty much the same way.

ATTENTION: hdbuserstore is part of the HANA client package ONLY. I wasn’t able to find it on a vanilla installation of HANA, so if you want to use it on the server (e.g. for the backup use case I present here) you’ve to install the HANA client on the server manually.

For this demonstration I simply used the HANA client on my laptop. To start, let’s review the command line options for this tool:

C:\\Program Files\\SAP\\hdbclient>hdbuserstore.exe -h
Usage: hdbuserprofileadm [options] command [arguments]
Options:
-u <USER>       perform operation for other operating system user
-v              verbose mode, print operations done
-h              this help message
Commands:
HELP
Print help message.
SET <KEY> <ENV> <USERNAME> <PASSWORD>
Set a profile entry.
<KEY>       entry key
<ENV>       connection environment (host and port)
<USERNAME>  user name
<PASSWORD>  password
DELETE <KEY>
Delete entry with key <KEY>.
LIST [<KEY> ...]
List entries of store. The passwords are not shown.

In short the programm allows us to create, display and delete sets of logon data.
These sets have a

  • name or <KEY>,
  • the ‘connection environment’ <ENV> which means HANA hostname and port (in my case this is vml3012:30015),
  • of course a <USERNAME>
  • and the <PASSWORD>.

Let’s go straight ahead and create an entry:

hdbuserstore set b vml3012:30015 backop Aa12345678

As you see, I’ve used

  • b as <KEY>,
  • vml3012:3001 as <ENV>,
  • backop as <USERNAME>
  • and Aa12345678 as <PASSWORD>.

I’d wish the hdbuserstore command would provide more feedback in case everything went OK, something along “OK, profile b successfully created!” or similar, but today it’s more along “no news are good news“…

Anyhow, we can review our newly created logon profile:

hdbuserstore list
LIST command for all entries is not yet implemented.

I guess we have to provide exactly what we want to see:

hdbuserstore list b
KEY b
ENV : vml3012:30015
USER: backop

Et voilá, there is our entry.

One thing to keep in mind is that these entries are acessible only by the operating system user that created it.
So in order to use this for the CRONJOB backup, you’d have to logon to the user cron executed the backup script with and create the logon profile there!

Now we put it all together and change the backup script to

BACKUP DATA USING FILE ('Monday')
//

With this file we can now call the backup just like this:

hdbsql -U b -I backup.sql

Again, we don’t get any output here if no errors occur.
This can become a bit annoying, especially when problems occur and debugging of the script should be done.
For that you can add the -f (show the SQL command) parameter and/or the -t (printout debugging information) parameter.
The output then looks like this:

hdbsql -U b -I backup.sql -f
BACKUP DATA USING FILE ('Monday')

or like this:

hdbsql -U b -I backup.sql -f -t

Username  : ""
DB name   : ""
Instance  : "(null)"
server    : ""
xuserkey  : "b"
nullvalue : "?"
fieldsep  : "(null)"
cmdsep    : "//"
CMDTrace  : "TRUE"
autocommit: "TRUE"
noHeader  : "FALSE"
useDBS    : "TRUE"
batch     : "FALSE"
rollbOnErr: "FALSE"
SQL mode  : "(null)"
Command   : "NULL"
BACKUP DATA USING FILE ('Monday')

That’s it once again!

As always I hope you liked the hands-on style of my blog and that you share your experiences with HANA.

To report this post you need to login first.

16 Comments

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

  1. Stefan Koehler
    I see the next patent lawsuit … this SQL syntax seems to be pretty well known … now we all know what was done with the data, that was downloaded by TomorrowNow … ahh just kidding πŸ˜›

    Great blog – keep on blogging Lars. As we don’t have any HANA access –  i will be looking for HANA technical stuff as much as possible :-))

    Best Regards
    Stefan

    (0) 
    1. Lars Breddemann Post author
      πŸ™‚ Hi Stefan,

      sure – since the actual Syntax of ALTER SYSTEM is that critically important this was the bit where we really wanted to do it like the real DB guys… hehehe

      I guess it’ll become interesting to keep on blogging about details of such a quick moving target like HANA is it right now…

      Cheers,
      Lars

      (0) 
  2. Vigneswararao Vankayala
    I have doubt in step 1 follow

    hdbsql HAN=> select user() from dual
    CURRENT_USER
    “SYSTEM”

    that means we are logon to System user? or need to supply password also ?

    (0) 
    1. Lars Breddemann Post author
      Hi Vigneswararao,

      sharp eyes of yours!
      Before I setup the userstore entry ‘b’ for user BACKOP I had it setup for SYSTEM.

      This is why I didn’t have to provide any logon data and yet was logged on to HANA as SYSTEM.

      regards,
      Lars

      (0) 
    2. Witalij Rudnicki
      Seeing this I actually reminded how shocked was I when saw DUAL table for the first time in HANA db! SAP using historical Oracle table (http://en.wikipedia.org/wiki/DUAL_table#History) for own purposes?? Without even renaming it to let’s say TRIPLE to show that HANA is a step forward?? πŸ˜‰ And then I realized that if one is targeting Oracle databases for migration to HANA db, the presense of this table sure thing will make porting a bit simplier. Good move. Lucky SAP that Oracke hasn’t patented DUAL table πŸ™‚
      (0) 
      1. Lars Breddemann Post author
        well, I guess this is rather MaxDB heritage in this case.
        All DBMS vendors nowadays have a way to guarantee a select statement returns just one tuple (e.g. SYSDUMMY1 in DB2).
        Inventing a new name for it would probably not really pay off for anybody…

        Concernig the porting aspect – there’s a long track history of Oracle migrations to say, MS SQL where no DUAL table exist. One way to tackle with this sitution quickly was/is to re-create a table resembling the DUAL table on MS SQL and change the code later on.

        (0) 
    1. Lars Breddemann Post author
      HI Mark,

      of course you can start a petition – but I already know that very capable colleagues are working hard on the documentation.
      From what I’ve seen so far, they’re doing pretty good πŸ™‚
      This of course won’t stop me from writing a blog occasionaly…

      best regards,
      Lars

      (0) 
  3. Michelle Crapo
    Hints are wonderful things especially with new technologies.   It is nice to have the “Class” / Document training.  It is even better to listen to someone with great experience in Hana.

    Thank you!  Keep blogging!

    Michelle

    (0) 
    1. Lars Breddemann Post author
      Hi Vitaliy,

      the BACKOP user as I presented it here is really stripped down to the minimum.
      To use it in HANA Studio the MONITORING role must be granted to it as well, since HANA Studio wants to display information to the user …

      If you do this, it works just as it does from the command line.

      cheers, Lars

      (0) 
  4. M. RAHAMAN

    Hi Lars,

    We are using version 04 of the script attached to 165105.It seems it is used to take databackup only.How to take log backup using script?We have any other script for that?

    Thanks,

    Mofizur

    (0) 
    1. Lars Breddemann Post author

      Hey there,

      please read the sap note and the attached documentation properly.

      If you still face problems after that, please open a separate discussion thread – this has no direct relation to this blog post.

      – Lars

      (0) 
        1. Lars Breddemann Post author

          Well, it looks like the user credentials have been saved under key BACKUPADMIN and not BACKUP_ADMIN.

          This is just the key under which hdbuserstore accesses the stored credentials; it’s not the DB user name.

          I agree this is possibly confusing but if you change the key value either in the hdbuserstore or the backup script configuration, then this should work.

          (0) 

Leave a Reply