Illustrating Advanced Backup and Recovery Techniques

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.

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.

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.

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

   Copy codeSwitch to dark mode

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

   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.

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

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

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

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.

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.

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.

• SAP HANA Administration with SAP HANA Cockpit - Resume a Canceled Recovery
• SAP Note 1642148 - FAQ: SAP HANA Database Backup & Recovery

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.

12345678910111213141516171819
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

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

• SAP HANA Administration Guide for SAP HANA Platform - recoverSys.py
• SAP Note 1703278 - Restoring an SAP HANA database using a command line
• SAP Note 2784202 - How to use native SQL to recover MDC system from command line

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

   Copy codeSwitch to dark mode

   12345
   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

3. Enter the password of the SYSTEM user.
4. Start the SAP HANA system.

For additional information, see the following SAP Notes:

• SAP Note 2588284 – Re-initialize a not recoverable system database
• SAP Note 1925267 – How to reset SYSTEM password
• SAP Note 2274157 – How to reset SYSTEM user password in a Tenant Database