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.
Product Expert – S/4HANA RIG – SAP Canada