HANA database recovery trick to restore to in between delta backups
SCENARIO: You backup your database as usual like below with Complete backup 19th Nov and then 3 differential (can be incremental) backups.
The DATA CORRUPTION occurred on 23rd after backup of 22nd and it is reported after you already took differential backup of 24th Nov. REALIZATION was too Late !!!
Now you didn’t backup log files as you had high data loads since last week that forced you to change log_mode to overwrite or let’s say all log backups are corrupted.
You don’t want to get into the rocket science of finding out the log position numbers or point in time for restore points, anyway these are not applicable in this situation anyway since you don’t have logs.
Moreover you don’t want to execute any intricate commands like hdbbackupdiag, or don’t want to be bothered with any manual detective work.You just don’t like the idea that systems make you work instead of other way around !
Now the scenario is shown in this graphic of latest HANA database SPS12 – 122.04. Backup Catalog is also shown and shows how to use the trick.
HANA Studio lets you to either restore to Complete backup 19th or the Last differential backup 24th. But not restore the 2 differential backups in between like 20th or 22nd . Remember you don’t have any log backups. In the dialog above if you uncheck “Use Delta” restore point will be Complete backup and if you checkmark it the restore point will be 3rd Differential backup.
THE DEFICIENCY: There is no option to select to restore to first or second differential backup, neither in studio interface or even for command line RECOVER DATBASE USING ….
THE SOLUTION: Go to Linux filesystem /usr/sap/HDB/HDB02/backup/log as below. You find 4 files with name log_backup* corresponding to each of the backups.
The trick is to simply move the file dated 24th November to another folder like home directory of ~ec2-user.
Then Start Recovery using HANA Studio
Then select Recover the database to its most recent state.
Select location of database backup.files
Choose the Complete backup which has been located
Checkmark on Use Delta Backups”
Database Restore will start.
After database is recovered you find that it is restored to the point of 2nd differential, not the 3rd. Differential backup is chosen for simplicity but same trick can be used for mix of differential and incremental backup restore.
Move the file back to the directory /usr/sap/HDB/HDB02/backup/log directory in case you need to restore to the point of Differential 3 backup dated 24th November.
Mahesh Sardesai
Product Expert – S/4HANA RIG – SAP Canada
What happen if alll my backups (full backup, logs backups, incremental and differential backups) are in 3th Parth Vendor, BACKINT, What can I do? Is possible to recover the database?
Hi Isaac,
This depends on the 3rd Party backup vendor. You should be able to ask them and they should be able to answer.
Most of them have elaborate ways to recover the database.
Appreciate your attention Mahesh.
My 3rd Party backup vendor support that feature.
I have already performed several recovery scenarios, but some of them fail after successful beginning.
Hi Mahesh,
Thanks for this blog. I’ve tried a similar approach with logs in normal mode, and didn’t have much luck.
For others going down this path, it seems even if you have delta backups, the system still forces you to use logs for any rollforward operation.
I am not sure the usefulness of having delta backups in HANA. In a bad situation, I would prefer a single delta backup image to keep track of day’s work, and not hundreds of log files. Perhaps some shops with huge systems, maybe it speeds up the recovery operation significantly.
Another user pointed me to this help page, which makes it clear that we are required to use logs for rollforward recovery and delta backups.
https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.03/en-US/4063907c2a744c158972269ca4f1b080.html
Hopefully this would change in future versions of HANA.
Hello Derek,
You have to have full backup and logs backup to perform any type of incremental/differential restore. You will not able to restore your DB to time and point if you only have full backups and not logs.
Before start reading the blog through the SCENARIO which give an idea and situation.
Your situation might be different
Thank you
Yogesh Patel
PS:This blog have all correct information.
Thanks, the author's situation is definitely different — he put his logs in overwrite mode so he didn’t have to manage excessive logs because of massive BW loads.
He also experienced corruption, where he had to recover in between differential backups, and this is a workaround to get that done, because he had the DB in log overwrite mode, so didn't have any log backups.
I thought his blog was a good starting point, as incremental restores are not very well covered with many examples, so many will find themselves led to this article when they are using search engines.
Others should know, know that logs are mandatory, even with a delta backup available, so they can plan ahead.
In HANA, to get a point in time restore, using delta backups you have to have --> Full backup + delta backup + log backups
A nice option would be this --> Full backup + delta backup = point in time restore, then logs as optional on top of that if needed, but not optional.
An example of this would be huge data loads with thousands of log files, I'd rather just be able to take 1 delta backup and track that instead of too many log files.