Automatic delete expired backup catalog information

[former_member182967]

Hello expert,

I wonder to know if there is any method to delete automatically expired backup catalog.

For example, our data backup retention is 21 days and some backup is expired (backup data is deleted by the third party backup tools). However, the information is still exist in the HANA backup catalog.

Thanks & Best Regards,

Tong Ning

[Prabhith · Accepted solution]

Hi

It is a very interesting question and I was doing some research in this topic.

When I see the Backup Catalog screen in DBACOCKPIT, I couldn't see any automation process in that screen. I am afraid we don't have any such automation process, but frankly speaking, its a wild guess from my side.

If there is no such automation process as of now, I would strongly believe that SAP is working on to fix this in the near future and am sure lot of customers would be expecting the same.

In case if you don't find a right solution here, I would strongly suggest you to raise a ticket with SAP so that we get the latest updates on this area from them.

BR

Prabhith

[former_member182967 · Accepted solution]

Hello Probhith,

Thanks for your quick reply.

I have opened a message to SAP support, I will forward to you as soon as I get the SAP reply.

Thanks & Best Regards,

Tong Ning

[former_member182967 · Accepted solution]

Hello Prabhith,

I get the reply from SAP support, he said that the automatic delete backup catalog is not supported now.

Thanks & Best Regards,

Tong Ning

[Prabhith · Accepted solution]

Hi ,

Is it? Probably, it would come soon!

And happy to see that my guess was right here.

Since you have your answer now, could you be kind enough to close this discussion thread so that our other colleagues who have the same query can use it as a reference...

BR

Prabhith

[Jonathan_Haun · Accepted solution]

SAP HANA Administration Guide - SAP Library

This page shows the SQL script to clear out the backup catalog. You should be able to find the max BACKUP_ID based on a date and then clear out everything before that backup ID.

[former_member182967 · Accepted solution]

Hello Jonathan,

Yeah, a good answer.

We can get the BACKUP_ID (additionally SYS_START_TIME for date reference) from M_BACKUP_CATALOG and use command: BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID <backup_id> (maybe we can also write a script to do it periodly.)

Thanks & Best Regards,

Tong Ning

[Jonathan_Haun · Accepted solution]

Below is an example shell script that will do it. I tried using a HANA stored procedure but I received a parsing error trying to pass a variable to the "BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID :v_bid COMPLETE" command.

With that said, I really like this method over just deleting the older log snapshot backups. Using the "COMPLETE" option on the command not only prunes the backup catalog but it also deletes the un-needed log backup files from the file system.

#### Start Shell Script:

### go to the hdbclient install directory.

cd /usr/sap/SID/hdbclient/

### Full Daily Backup

./hdbsql -i 0 -n localhost -u SYSTEM -p XXXXXX "BACKUP DATA USING FILE ('COMPLETE_DATA_BACKUP')"

### Find backup and log backups in the backup catalog over 5 days old and remove them and their files

### Get min full backup id in the last 5 days

./hdbsql -i 0 -n localhost -u SYSTEM -p XXXXXX -o /usr/sap/backupid.txt -x -a "SELECT TOP 1 min(to_bigint(BACKUP_ID)) FROM SYS.M_BACKUP_CATALOG where SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP, -5) and ENTRY_TYPE_NAME = 'complete data backup' and STATE_NAME = 'successful'"

### read the backup id from a file and assign to variable $BACKUPID

read BACKUPID < /usr/sap/backupid.txt

### Create a file containing a query to prune the backup catalog and files before the identified backup id

cat >  /usr/sap/catalogdel.txt << EOF

BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $BACKUPID COMPLETE

EOF

### Read the query from the file and

./hdbsql -i 0 -n localhost -u SYSTEM -p XXXXXX -I /usr/sap/catalogdel.txt

### Reclaim any un-needed log space -- Nice option for pre SPS7 HANA environments.

./hdbsql -i 0 -n localhost -u SYSTEM -p XXXXXX -a "ALTER SYSTEM RECLAIM LOG"

[former_member182967 · Accepted solution]

Hi Jonathan,

A good example, isn't it? Thanks!

Best Regards,

Tong Ning