CODEWITHSUNDEEP
hanadatabase-backups
Ricky
Ricky

Reputation: 2750

How to take backup by schema wise in SAP HANA

I am performing a backup in HANA using

hdbsql HDB=> BACKUP DATA USING FILE ('/backup/HDB/data/BACKUP_FULL_20170514/SAPHANA')

Instead of this I would like to have a backup by schema wise and make it as a cronjob. How to take backup by schema wise in SAP HANA?

Upvotes: 2

Views: 4677

Answers (3)

David Lopes
David Lopes

Reputation: 559

I use this simple commands:

In SHH go to your backup folder, create your folder to contain the backup, give that folder permissions to HANA Studio write in there:

cd /hana/bkps/
mkdir BACKUP_FLD_180101
chmod -R 777 BACKUP_FLD_180101/

Next go to HANA STUDIO make your backup with:

export "FLD_PRD"."*" AS BINARY INTO '/hana/bkps/BACKUP_FLD_180101' with replace threads 10

DONE!!

Upvotes: -1

djk
djk

Reputation: 973

You can export a schema with this SQL command:

EXPORT "MY_SCHEMA".* AS BINARY INTO '/tmp/my_schema' WITH REPLACE;

See EXPORT documentation for more options.

Tipp: Do not export to a mounted Windows file system or NAS, because the export can contain case-dependent files with the same name but different casing. This would break on a Windows file system. -- Best is to directly compress it on the Linux system like so:

tar -czf my_schema.tgz /tmp/my_schema/

To import the schema backup, use this command:

IMPORT "MY_SCHEMA".* FROM '/tmp/my_schema' WITH REPLACE;

You can use WITH RENAME "MY_SCHEMA" TO "MY_SCHEMA_COPY" to restore it under a different name. See IMPORT documentation for more options.

SAP note 2134959 describes the process in more detail. Also, have a look at SAP note 2121486, which lists the inconsistency risks of using EXPORT for backup, as mentioned by @Lars.

BTW, I am not sure why @Lars discourages this or even depicts it as "not possible". SAP itself recommends it and a lot of their tools rely on it, like SAP Cloud Control Center. It is a well-suited method for certain tasks, on top of a regular full backup.

Upvotes: 2

Lars Br.
Lars Br.

Reputation: 10396

Ok, this is a common question, not just for HANA but for any DBMS with schema support.
The short answer is: it's not possible to backup/restore data based on schemas. A schema is a namespace in the database, while backup/restore are functions of the data persistence.

As transactions can span multiple schemas, restoring just a single schema might lead to inconsistencies, as transactions would only be partly recovered.

Having said all that, typically the actual requirement is not to backup/restore separate schemas, but to allow for the handling of multiple database tenants on a shared system.

SAP HANA supports this with Multi-Database-Containers. Each tenant gets its own set of database processes, memory, filesystems, etc. but the HANA software installation is shared.

That way it's easy to backup/recover a single tenant without affecting the other tenants. Another benefit with this approach is that the schema itself will have the same name in all tenant DBs, making maintenance easier.

Upvotes: 2

Related Questions