Resolving SAP HANA System Database Refresh Issue – Missing Backup Root Key During Recovery

[Hari_Balaji]

Introduction

During a recent SAP HANA system database refresh activity, we encountered a critical issue while restoring the Production (PRD) database backup into the Quality (QAS) system. The refresh activity is typically straightforward; however, when database encryption is enabled, additional key management steps are required to ensure a successful recovery.

This blog explains the issue faced, its root cause, and the step-by-step resolution that helped us complete the recovery successfully.

Issue Faced

While recovering the PRD database backup in QAS, the recovery process failed with the following error

Root Cause

Our PRD backup was encrypted, as we are running SAP HANA 2.0 SPS07, where backup encryption is enabled by default. However, the required encryption root key was not available in the QAS system. Without this root key, SAP HANA is unable to decrypt or restore the encrypted backup.

Solution

To resolve this issue, we exported the encryption root keys from PRD and imported them into QAS.

Step 1: Set Backup Password for Encryption Root Keys (PRD)

Execute the following command in HANA Studio (PRD system):

ALTER DATABASE <PRD_SID> SET ENCRYPTION ROOT KEYS BACKUP PASSWORD 'MyStrongPassword123!';

This password protects the exported root key backup.

Step 2: Back Up Encryption Root Keys (PRD)

BACKUP ENCRYPTION ROOT KEYS FOR <PRD SID> USING FILE ('backup001');

The encrypted root key file is generated in the following location:

/usr/sap/<PRD_SID>/HDB<Instance_Number>/backup/sec/DB_<PRD_SID>/backup001_rootkeys

Step 3: Copy Root Key to QAS

Securely copy the generated _rootkeys file from the PRD server to the corresponding directory on the QAS system.

Step 4: Recover Encryption Root Keys in QAS

Run the following command from the QAS OS level :

hdbnsutil -recoverRootKeys /usr/sap/<QAS SID>/HDB<Instance Number>/backup/sec/DB_<PRD_SID>/backup001_rootkeys --dbid=4 --password=MyStrongPassword123! --type=ALL

Permissions : 

For the System Database, execution of this statement requires any one of the following system privileges:

BACKUP ADMIN
BACKUP OPERATOR

For a Tenant Database, execution of this statement requires any one of the following system privileges:

DATABASE BACKUP ADMIN
DATABASE BACKUP OPERATOR

Hint : 

How to Find the DBID ?

Run the following query in SAP HANA Studio:

SELECT DATABASE_NAME,

       CASE WHEN (DBID = '' AND DATABASE_NAME = 'SYSTEMDB')

            THEN 1

            WHEN (DBID = '' AND DATABASE_NAME <> 'SYSTEMDB')

            THEN 3

            ELSE TO_INT(DBID)

       END DATABASE_ID

FROM (SELECT DISTINCT DATABASE_NAME,

      SUBSTR_AFTER(SUBPATH,'.') AS DBID

      FROM SYS_DATABASES.M_VOLUMES);

Final Step: Retry Database Recovery

After successfully importing the encryption root keys into QAS, the database recovery was retried and completed successfully without any errors.

 Key Takeaway : 

When performing SAP HANA system refreshes involving encrypted backups, always ensure:

1.Encryption root keys from the source system are backed up

2.The keys are securely transferred and restored in the target system before recovery

3.Please note that from SAP HANA 2.0 SPS 01 version we can create encrypted backup

4.In a newly installed HANA 2.0 SPS07, encryption is enabled by default.

Conclusion : 

System refresh activities in SAP HANA environments require careful handling when encryption is enabled. The missing root key error may seem complex initially, but the resolution is straightforward once the root key export and import process is properly executed.