HANA DB refresh/copy using Point in Time Recovery option
Hello !! Have you ever thought of an alternative for System Replication method for HANA DB host migration, or have you ever had a requirement to perform Copy of HANA DB using Backup/restore method with very low RPO and RTO? Then this blog is for you.
Background :- Basically, when we want to Copy a Production DB to QA DB, we are mostly left with the option to recover QA DB with a Complete Data Backup of the Prod DB which might be a few days older. But if we want to recover to recent point in time, we need to trigger a Complete Backup, copy the backup file to QA host and then Recover that, and this is more time consuming. Lets take an analogy of the same scenario with MS SQL Server, where we can restore the complete backup of the source DB in the target with No Recovery option a day prior to migration or refresh, and at the time of migration/refresh, we can take a diff backup and recover the target DB, which brings the data to most recent point in time without spending too much time. This option does not work in HANA as we need Backup Catalog to be available if we want to recover HANA DB with Complete Backup and Diff Backup. In this blog, I am suggesting a solution for this case.
- Copy Backups (Data and at-least 1 log backup) from source to target server.
- Create backup catalog in target DB with backup files from Source DB
- Recover Target DB
- Source and Target Database are in same version.
- Tenant Database is only considered for this copy/recovery.
- In this blog, source database is BP1 tenant on BHP database and Target is BTX tenant on BHQ database.
Step 1) Stop Target Tenant
Step 2) Generate Backup Catalog in target using source backup files
hdbbackupdiag --generate --dataDir <location of Data backup files> --logDirs <location of log backup files> -d <location of backup catalog>
Step 3) Check the newly generated Backup Catalog
hdbbackupdiag -d <location of backup catalog>
Step 4) Recover target tenant
Below is the recovery command format, but I have performed then recovery using HANA Studio, which is convenient.
RECOVER DATABASE FOR <Tenant name> UNTIL TIMESTAMP 'time stamp' CLEAR LOG USING DATA PATH ('<location of data backups>') USING LOG PATH ('<location of log backups>') USING BACKUP_ID <backup id as per backup catalog> CHECK ACCESS USING FILE
Check the progress of the recovery from trace files.
Voila! Recovery is complete.
Thank you for taking time to read this blog. Hope this blog is useful !
Vijay, Nice blog.
We have a SAP Note 1812057 – Reconstruction of the backup catalog using hdbbackupdiag
If the restore from the command line, then How to get the BACKUP_ID?.
Should we consider the BACKUP_ID from your example?.
Or should we get it from M_BACKUP_CATALOG?