SAP HANA Backup and Recovery
- Created by Paul Power, last modified on Nov 23, 2017
- Purpose
- System Privileges
- How to Perform a Backup
- Manual Backup Check
- Recovery
- Common Questions
- Issues and Reported Problems
- Related Documents
- Related Videos:
- Related SAP Notes/KBAs
Purpose
This page is to explain the steps on how to perform a basic backup and recovery. At the end of this guide, there will be frequently asked questions and common problems that are encountered. For an in-depth explanation of the backup and recovery process, please see the Admin guide http://help.sap.com/hana/SAP_HANA_Administration_Guide_en.pdf.
System Privileges
In order to do a backup, the user will need the following privileges
- BACKUP ADMIN or BACKUP OPERATOR
- CATALOG READ
How to Perform a Backup
You can perform a full data backup through HANA Studio and also through SQL Statements.
Studio
Open SAP HANA Studio and on the navigator side (left hand side) expand your system and double click the ‘Backup’ icon
Press the "Backup" button.
** For a multi-tenant database, you will have the option to backup the system database and the tenant database. **
The following popup will appear.
Click Next
The backup will then show you the backup status and then finish.
The backup will finish with the summary page.
SQL Command for Backups
The SQL Command to perform a full data backup follows the syntax:
BACKUP DATA USING FILE ('<name of backup>')
Manual Backup Check
Although automatic checks take place while complete data backups and log backups are performed, you should manually re-check the integrity of any backup if it is copied or moved to another location.
hdbbackupcheck -v <Backup file>
Recovery
To recover a SAP HANA database, the database needs to be shut down. During recovery, the database therefore cannot be accessed by end users or applications.
It may be necessary to recover the SAP HANA database in the following situations:
- A disk in the data area is unusable.
- A disk in the log area is unusable.
- As a consequence of a logical error, the database needs to be reset to its state at a particular point in time.
- You want to create a copy of the database.
There are a few different types of recovery
Most Recent State: Used for recovering the database to the time as close as possible to the current time. For this recovery the data backup and log backup have to be available since last data backup and log area are required to perform the above type recovery
Point in Time: Used for recovering the database to the specific point in time. For this recovery the data backup and log backup have to be available since last data backup and log area are required to perform the above type recovery
Specific Data Backup: Used for recovering the database to a specified data backup. Specific data backup is required for the above type of recovery option.
Specific Log Position: This recovery type is an advanced option that can be used in exceptional cases where a previous recovery failed.
Restrictions for Recovery
- A SAP HANA database cannot be recovered to a SAP HANA database with a lower software version. The SAP HANA database software version used for the recovery must always be the same version or higher than the SAP HANA database used to create the backup.
- There can be any number of hosts in the target system, provided that the number and type of services is identical in both the source and the target system.
- With SAP HANA multi-tenant database containers, recovery using snapshots is not supported yet. Tenant databases can be recoverd via the relevant system database only.
Recovery Process
The database can be recovered from HANA Studio and also from SQL Commands.
Studio
Right click on the HANA system and then select "Backup and Recovery", then "Recovery"
The HANA system will stop and then the recovery wizard will appear. From here, select the recovery option. In this example, I chose to recover from 2 hours ago.
Ensure that the log backup directory is correct. ** This is important if you are recovering onto a different HANA instance **
The location of the data backup is specified next
Additional Settings.
Confirm all of the settings are correct and then select Finish.
The recovery will take some time. After HANA has performed the recovery, the following screen is displayed. The HANA system will start automatically after a successful recovery.
Command Line
The Python script recoverSys.py supplies the restore command to the services and starts the database.
ie) python /usr/sap/<SID>/SYS/exe/hdb/python_support/recoverSys.py --password=<sidadm passwd> --wait --command="RECOVER DATABASE UNTIL TIMESTAMP '2013-03-29 23:59:59'"
1. Restore until most recent state - choose a current timestamp or one in the future
RECOVER DATABASE UNTIL TIMESTAMP 'YYYY-MM-DD HH:MM:SS'
Ex: RECOVER DATABASE UNTIL TIMESTAMP '2013-03-22 15:00:00'
2. Restore until Point in time
RECOVER DATABASE UNTIL TIMESTAMP 'YYYY-MM-DD HH:MM:SS'
Restore until point in time with timestamp using further directories containing log backups
RECOVER DATABASE UNTIL TIMESTAMP 'YYYY-MM-DD HH:MM:SS' CHANGE ALL LOG USING PATH ('/usr/sap/SH1/HDB00/backup/logs1/‘, '/usr/sap/SH1/HDB00/backup/logs2/')
Ex: RECOVER DATABASE UNTIL TIMESTAMP '2013-03-01 15:00:00' USING DATA PATH ('/usr/sap/SH1/HDB00/backup/data/COMPLETE') USING LOG PATH ('/usr/sap/SH1/HDB00/backup/logs1/‘, '/usr/sap/SH1/HDB00/backup/logs2/')
Common Questions
The following are problems have been encountered by customers in the past.
Q: Can backups be recovered to a SAP HANA database with a higher software version?
A: This is answered in SAP Note 1642148 - FAQ: SAP HANA Database Backup & Recovery
"
Yes.
You can recover data and log backups created with a SAP HANA database software version lower or equal to the target database. Data and log backups cannot be recovered to a SAP HANA database with a lower software version.
If you are updating your SAP HANA database from a maintenance revision to a support package, certain restrictions apply.
More information: SAP Note 1948334 (SAP HANA Database Update Paths for Maintenance Revisions)
"
Q: I am recovering using a backup from a multi-node to a single-node environment, what should I check before performing this recovery?
A: The most common problem with recovery from a different system is a mismatch in topology. For example, the backup was taken from a system that has 2 indexservers and you want to recover it on a system that has only 1 indexserver. If you attempt to do this, the recover will fail and you will need to recover back to the original state.
The most common service that is mismatching is the Embedded statistics server. To check this, compare the nameserver.ini in the source and the target for [statisticsserver] -> active = true.
The second most common service that is mismatching is the indexserver. Consult SAP Note 2093572 - SAP HANA Migration from Multi-Node to Single-Node for more details on how to configure the target system to match the backup landscape.
Q: If a recovery fails, my HANA server does not start.
A: This is a typical behavior of HANA. You must recover from a working backup (that has the same topology)
Q: How do you perform a system refresh?
A: Follow this note to perform a system refresh using SWPM. Please see SAP Note 1844468 - Homogenous system copy on SAP HANA
Q: I'm performing a recovery from SPS09, is there anything I need to be aware of?
A: If you have upgraded to SPS09, you should ensure that you perform a full data backup prior to attempting a recovery. See SAP Note 2119628 - Missing or wrong data after recovery of older backup with SAP HANA SPS09.. After you have successfully recovered again, perform another full data backup. See SAP Note 2097985 - Point-in-Time Recovery Fails After Data Restore.
Q: If there is a failed backup, what do I do first?
A: Consult the backup.log for more details on the error. This is one of the first things to do. Once you determine the error, you can search SAP KBA's for a solution.
Q: What is the process of archiving system-based backups?
A: Refer to SAP Note 1817703 - Archiving file system-based backups and SAP Note 2096851 - Management of Old Backups in HANA, to determine if backup files can be archived.
Q: We want to upgrade our Backint system, what needs to be done on HANA?
A: Refer to SAP Note 2009486 - Disable SAP HANA log backups during upgrade of third-party backup tool that supports the Backint for SAP HANA interface
Q. When copying or refreshing one SAP HANA system from a backup taken from another SAP HANA system, can the schema name be changed?
A. It is not possible to change the schema name on the SAP HANA database. Please see SAP Note 1642148 - FAQ: SAP HANA Database Backup & Recovery
Q: My backups are performing slow, what can I do?
A: SAP Note 1835075 - Analyze backup and recovery performance issues, provides detailed steps on how to analyze performance in relation to backups and recovery.
Q: How do you cancel a running backup?
A: To cancel a running backup, please use the following 3 SQL statements:
- Get the backup_id of your running backup:
select BACKUP_ID from "SYS"."M_BACKUP_CATALOG" where entry_type_name = 'complete data backup' and state_name = 'running' order by sys_start_time desc - Cancel the backup
backup cancel <backup_id> - Confirm the backup has cancelled
select state_name from "SYS"."M_BACKUP_CATALOG" where backup_id = <backup_id> - Check to make sure there are no running backups still
select * from "SYS"."M_BACKUP_CATALOG" where STATE_NAME = 'running'; - If the backup still exists, you can try to cancel the thread through the commands:
ALTER SYSTEM CANCEL SESSION ;
ALTER SYSTEM DISCONNECT SESSION ;
Q: How do I determine what redo log files are needed for my restore?
A: Follow the steps in this note: 1705945 - Determining required restoration files
Q: Can I use a backup taken with 3rd party tools (backint) to perform a system copy?
A: Yes, please follow the steps of the admin guide at
http://help.sap.com/saphelp_hanaplatform/helpdata/en/ea/5004bdab024061afd1caae7d309263/content.htm
Q: How do you enable further tracing if a backup is failing?
A: Run the following command in sql editor:
Enable the trace | ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('backup', 'trace') = 'debug' with reconfigure; |
Disable the trace | ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') UNSET ('backup', 'trace') with reconfigure; |
Issues and Reported Problems
Error: This error occurs when I try to recover "Could not recover system <SID> - Could not start master name server"?
Solution: When a recovery is initiated in SAP HANA Studio, the database, if still running, will be shutdown. The database is then restarted automatically during recovery. If for some reason, the database cannot be shutdown completely before the recovery, i.e. some server processes cannot be stopped, the restart and ultimately the recovery will fail. Consult SAP Note 1944052 - Recovery failed due to hdbdaemon process not able to stop
Related Documents
SCN Document SAP HANA Backup/Recovery Overview
SCN Blog: Back it on up…..then recover it
SCN: Backup and Recovery - HANA
SCN: SAP HANA Academy: Backup and Recovery - Backup Catalog
Related Videos:
SAP HANA Academy - Backup and Recovery: Storage Snapshot [SPS 08]
SAP HANA Academy - Backup and Recovery: Backup Catalog
Related SAP Notes/KBAs
SAP Note: 1642148 - FAQ: SAP HANA Database Backup & Recovery
SAP Note: 2093572 - SAP HANA Migration from Multi-Node to Single-Node
SAP Note: 1651055 - Scheduling SAP HANA Database Backups in Linux
SAP Note: 2044438 - HANA: Backup fails for Out Of Memory error
SAP Note: 2122316 - mvcc garbage collection is blocked while data backup when auditing is enabled
SAP Note 2123153 - HANA Recovery Failing with "recovery strategy could not be determined"
SAP Note 1730932 - Using backup tools with Backint for HANA
SAP Note 1730998 - Unrecommended versions of backup tools
SAP Note 2063454 - Long running log backups
SAP KBA 2101244 - FAQ: SAP HANA Multitenant Database Containers (MDC)
SAP Note 2096000 - SAP HANA multitenant database containers - Additional Information
SAP KBA 2486224 - Tenant DB recovery to another system Tenant DB