Skip to Content

How to delete old backups using hdbsql?

Hi experts,

I want to take data backup and delete old backups everyday using hdbsql. However, I have no idea how to know backup id of backup older than yesterday.

I know HANA studio shows all information including backup id, but I want to execute hdbsql in background on application server using 3rd party job management software.

Can anyone help me? Thak you.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Jun 06, 2019 at 06:59 AM

    Hello Junya,

    There are two tables from where you can get all backup information - M_BACKUP_CATALOG and M_BACKUP_CATALOG_FILES

    Query to get information of all complete data backup

    SELECT * FROM M_BACKUP_CATALOG WHERE ENTRY_TYPE_NAME='complete data backup'

    So you can get older backup ID of complete data backup and use the same in below query to delete older backups.

    BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID <backupid> 

    Below query also delete physical data backup as well along with clearing entry from backup catalog

    BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID <backupid> COMPLETE

    NOTE: In HANA, it is not possible to delete just the log or incremental backup from backup catalog. The reason is obvious as deletion of any random log and incremental backup will make data backup useless for point in time recovery. Even if you try to delete an entry of log backup from backup catalog, it will result you with error. So you have to provide BACKUP_ID of complete data backup and from that it will delete all data, incremental and log backup.

    There are various other ways for housekeeping of backup catalog but I think you want everything to be schedule from third party tool.

    1. Scheduling HANA Cleaner python script for housekeeping (If you tool can call python script you can use this method as well)

    https://blogs.sap.com/2018/10/29/hana-housekeeping-using-hanacleaner/

    2399996 - How-To: Configuring automatic SAP HANA Cleanup with SAP HANACleaner

    2. Use of latest Cockpit to schedule retention.

    https://blogs.sap.com/2018/08/08/sap-hana-cockpit-2.0-sp-07-whats-new-by-the-sap-hana-academy/

    Regards,

    Dennis

    Add a comment
    10|10000 characters needed characters exceeded

    • Hello Junya,

      If you are just trying to find the last complete data backup - BACKUP_ID, then you can use below command. It will show the backup id of the latest backup as its value is going to be highest.

      SELECT MAX(BACKUP_ID) FROM M_BACKUP_CATALOG 
      WHERE ENTRY_TYPE_NAME='complete data backup'

      Regards,

      Dennis.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.