Illustrating Advanced Backup and Recovery Techniques

Objectives

After completing this lesson, you will be able to:

  • Identify advanced backup and recovery techniques

Data Snapshots

Data snapshots offer an additional option to backup and restore SAP HANA databases. A data snapshot captures the state of the data volume on the storage system at a particular point in time. A data snapshot includes all the data that is required to recover an SAP HANA system to a consistent state.

Data Snapshot Benefits

Data snapshots have a minimal impact on the database system, because they are created in the storage system and do not consume database resources.

Recovery from a data snapshot is fast because the data snapshot is already available in the data area of the storage system, no need to copy data from the backup server to the storage system.

For a recovery based on a data snapshot, you can also use delta backups and log backups in the same way as with a recovery based on a data backup.

Important Points to Know About Data Snapshot

If you are planning a backup strategy that makes use of data snapshots, you should be aware of several important points.

  • To create a data snapshot, first an internal database snapshot is created. This internal database snapshot provides a frozen consistent state of the database at the point in time that it was started. This is particularly important if multiple storage volume groups are involved.

  • Data snapshots can only be created through the system database.

  • During a data snapshot the SAP HANA database system is online, and all the configured services are running.

  • To create a data snapshot, you must use native SQL.

  • Recovery from a data snapshot is supported by SAP HANA cockpit.

Whereas a data backup is written to a separate storage location, a data snapshot must be manually stored in a location that is physically separate from the SAP HANA data area.

Note

The responsibility of the integrity of the storage snapshot lies with the storage vendor. SAP HANA cannot check the integrity of the storage snapshot on the storage system.

An SAP HANA database can be recovered in a single procedure, either using a data snapshot, or using a data snapshot in combination with log backups. You can replay log backups after the database has been recovered with a data snapshot.

Creating a Data Snapshot

A data snapshot is created in three steps that are performed in the SAP HANA database and at storage system level. There is a weak link between SAP HANA and the storage tool. Data snapshots are recorded in the SAP HANA backup catalog.

While the data snapshot is being prepared or created, the snapshot-relevant data is frozen. While the snapshot-relevant data remains frozen, changes can still be made in the database. Such changes will not cause the frozen snapshot-relevant data to be changed. Instead, the changes are written to positions in the data area that are separate from the data snapshot. Changes are also written to the log.

To create a data snapshot, proceed as follows:

  1. Prepare the database by creating a new internal database snapshot and finding the generated backup id using the following SQL command:

    Code snippet
    BACKUP DATA FOR FULL SYSTEM CREATE SNAPSHOT COMMENT ‘Internal database snapshot’;
    SELECT * FROM M_BACKUP_CATALOG WHERE ENTRY_TYPE_NAME = 'data snapshot';
    Expand

    From the output, find the backup id of the internal database snapshot with the state PREPARED. This backup id is later needed to confirm or abandon the data snapshot backup.

  2. Create a storage snapshot of the SAP HANA data volume area using the tool provided by your storage vendor. Note down the storage snapshot id. This storage snapshot id is later needed to confirm or abandon the data snapshot backup.

  3. Depending on the success or failure of the storage snapshot, confirm or abandon the data snapshot in SAP HANA.

    Confirm or abandon the data snapshot using the following SQL commands:

    Confirm:BACKUP DATA FOR FULL SYSTEM CLOSE SNAPSHOT BACKUP_ID 3456789 SUCCESSFUL 'snapshot_id_12345‘

    Abandon:BACKUP DATA FOR FULL SYSTEM CLOSE SNAPSHOT BACKUP_ID 3456789 UNSUCCESSFUL 'snapshot_id_12345‘

    You can specify an external ID to identify the data snapshot later in the storage system.

    An entry with the external backup ID is written to the backup catalog. If the data snapshot cannot be created, or if confirmation fails, choose Abandon.

After you have confirmed or abandoned a data snapshot, it is recorded in the backup catalog as either successful or unsuccessful. The SAP HANA database automatically deletes the internal snapshot from the SAP HANA data area after it has been either confirmed or abandoned.

Prepared data snapshots only exist until the data snapshot is executed using the storage tool. When a data snapshot is prepared but not confirmed for a longer period of time, an alert occurs.

Note

It is strongly recommended to confirm or abandon a data snapshot as soon as possible after it has been created.

For information on creating data snapshots using SQL commands, see the SAP HANA Administration Guide - Data Snapshots.

Recovery with a Data Snapshot

Using SAP HANA cockpit and storage system tools, you can use a data snapshot to recover an SAP HANA system with all its tenant databases. See the SAP HANA Administration Guide - Recover SAP HANA from a Data Snapshot

Additional Information

SAP Note 2039883 - FAQ: SAP HANA database and data snapshots (storage snapshots)

Resumption of Recovery

If a recovery is canceled or interrupted, an SAP HANA database cannot start. Before work can continue in the database, the recovery must be completed. However, having to repeat an interrupted recovery from the beginning may cause a significant loss of work time. Because of this, the option to resume an interrupted recovery can save a significant amount of time, both with a large or a smaller database.

Resume Recovery After Error

Data recovery takes up most of the time of a recovery. If a recovery fails during delta data backup recovery or during log replay, SAP HANA can resume the recovery, thus shortening the outage significantly. SAP HANA can resume database recovery if outages occur during delta data backup recovery, or after successfully replayed logs.

A data recovery can be resumed from the last successfully recovered full backup, or from the successfully recovered delta backups that rely on additional fallback points set between full backups and delta backups during recovery. Failures during log replay can benefit from preserved changes at frequent savepoint intervals, so that only the most recent set of logs after the savepoint must be replayed.

Note

A recovery from only a full data backup cannot be resumed. If a recovery from only a full data backup is interrupted, repeat the recovery from the beginning.

During a recovery, SAP HANA automatically defines fallback points, which mark the point after which you can resume a recovery.

If you resume database recovery after a failure during delta data backup recovery, the following occurs:

  • Accelerate subsequent recovery using successful recovered full data backups, differential and incremental data backups, and log backups.

  • Successfully recovered data backups are kept implicitly as an internal database snapshot (fallback point) in the data area.

  • If a recovery failure occurs during delta data backup recovery, the full backup recovery is preserved. Only the delta data backups and the log backups are reprocessed.

If you resume database recovery after a failure during log recovery, the following occurs:

  • Data changes caused by replaying log backups during a database recovery are periodically stored in the log area with fallback points.

  • The frequency of fallback points can be set using the log_recovery_resume_point_interval configuration parameter (default: 1,800 sec; max: 18,000 sec, 0 = disabled).

  • Once the interval expires, the next fallback point log entry is replayed.

  • If a recovery failure occurs after a fallback point, only the log backups after the most recent fallback point must be reprocessed.

After a recovery has been successfully completed, the fallback points are invalidated. It is then no longer possible to perform a new recovery based on those fallback points.

Note
The fallback points are recorded in the backup.log file. The fallback points indicate whether you can resume a recovery.

Perform Recovery After Error

A recovery that can be resumed is also recorded in the backup catalog. In SAP HANA cockpit, a partially completed recovery that can be resumed is given the backup prefix RESUME. To resume the recovery from that backup, proceed as follows:

  1. Start the recovery from SAP HANA cockpit.
  2. In the recovery dialog box, select the backup with the prefix RESUME.
  3. Follow the steps described on-screen to complete the recovery.

Additional Information

Recovery with the Command-Line Tool

It is strongly recommended that you use SAP HANA cockpit to recover the SYSTEMDB and/or tenants databases. However, when the SAP HANA cockpit isn't available the SYSTEMDB can be recovered using the Python script recoverSys.py.

SQL statements for recovery cannot be executed using the normal SQL clients such as hdbsql and they cannot be executed when the database is online. For this reason, the Python script recoverSys.py is used to pass SQL statements to SAP HANA.

Caution

Tenant databases cannot be recovered using the command-line tool. To recover a tenant database, use SAP HANA cockpit or the recoverSys.py tool to execute SQL statements on the online system database to recover an offline tenant database.

Recovery Procedure Using Command-Line Tool

The following is the procedure for performing recovery using the command-line tool:

  1. The administrator calls the script with the required parameters, specifying recovery target time, recovery type, and further options.

    HDBSettings.sh recoverSys.py [<parameters>]

  2. The script stops the SAP HANA database, prepares, and executes the recovery.

  3. After the coordinator name server of the SAP HANA database starts successfully, the script terminates.

    Note that, at this point, the recovery is not complete yet. Call the script using the --wait option, because this ensures that the script waits until the recovery finishes.

Code snippet
HDBSettings.sh recoverSys.py --wait
[139669536070528, 0.007] >> starting recoverSys (at Thu Aug 17 19:07:04 2023)
[139669536070528, 0.007] keys: {'wait': True}
using logfile /usr/sap/H46/HDB00/wdflbmt7346/trace/backup.log
recoverSys started: ============2023-08-17 19:07:04 ============
testing master: wdflbmt7346
wdflbmt7346 is master
shutdown database, timeout is 120
stop system on: wdflbmt7346
stopping system: 2023-08-17 19:07:04
stopped system: 2023-08-17 19:07:18
creating file recoverInstance.sql
restart database
restart master nameserver: 2023-08-17 19:07:23
start system: wdflbmt7346
sapcontrol parameter: ['-function', 'Start']
sapcontrol returned successfully:
recoverSys finished successfully: 2023-08-17 19:07:55
Expand

To check that the recovery is successful, see the backup.log file.

Additional Information

Re-Initialize a Non-Recoverable System Database

The system DB is corrupted and cannot be started anymore, but all tenants are still working. You do not have a SYSTEMDB backup, so the only possible solution is to re-initialize the SYSTEMDB instead of recovering the whole SAP HANA database system including all tenants.

The command-line tool hdbmdcutil allows you to re-initialize the system database with information on all tenant databases of the SAP HANA instance.

Re-Initialize a System Database Using the Command-Line Tool hdbmdcutil

  1. Stop the SAP HANA system. Ensure that all hosts and services that are part of this system have been stopped.
  2. Start the hdbmdcutil tool with the -restoreTopology option:
    Code snippet
    hdbmdcutil -restoreTopology [--set_user_system_pw] [--force]
    initializes the topology with the content from the topology backup file
    Options:
    set_user_system_pw - open prompt to specify password for SYSTEM user
    force - force the operation even preconditions are not fulfilled
    Expand
  3. Enter the password of the SYSTEM user.
  4. Start the SAP HANA system.
Caution

This feature will not restore any other data previously stored in the system database. This data, for example license information, users, delivery units, security templates, and others, has to be restored manually using the SQL interface or other user interfaces and/or tools.

Because of this limitation, the system database should always be recovered from a backup instead of using the hdbmdcutil tool.

Additional Information

For additional information, see the following SAP Notes:

Log in to track your progress & complete quizzes