Print real backup filenames with SAP NLS events

The main SAP IQ functionality of SAP NLS is handled by database events. Such events are triggered during a data archiving run in order to execute a SAP IQ incremental backup. A successful backup is required before the data is deleted from the primary database. This document shows how to create helpful backup filenames so that they can be retrieved from the backup history for example.

The following sample shows database objects provided by SAP for the SAP NLS solution: the BACKUP_FULL event and the function that will be called in order to build the filename. This filename contains the full path as well and is stored in the variable FILENAME. The same logic applies to the event BACKUP_INCREMENTAL which is executed during each data archiving run. Here I’ve chosen the event BACKUP_FULL as it contains less code but nevertheless the important lines are included.

When the event BACKUP_FULL is triggered a full backup of the SAP IQ database is done. After each 50 GB (=52428800 kb) of data being backed up a new file will be created. So for 120 GB data, you will have two files with 50 GB each and one file with a size of 20 GB. The names of the files are built automatically by appending .1 to the first file, .2 to the second, etc. (For the restore the .1 must be omitted)

CREATE EVENT DBA.BACKUP_FULL

SCHEDULE “WEEKLY_FULL” START TIME ’00:00′ ON ( ‘Sunday’ )

HANDLER

BEGIN

    DECLARE FILENAME VARCHAR(255);

    FILENAME = CALL GET_BACKUP_FILENAME(‘full’);

    BACKUP DATABASE FULL TO FILENAME SIZE 52428800;

END;

CREATE FUNCTION DBA.GET_BACKUP_FILENAME( IN IN_FILEEXTENSION VARCHAR(255) DEFAULT ‘incr’ )

RETURNS VARCHAR(255)

NOT DETERMINISTIC

BEGIN

    DECLARE OUT_FILENAME VARCHAR(255);

    SET OUT_FILENAME = ‘/sybase/sapdata/backup/’ || DB_NAME() || ‘/’ || DATEFORMAT(NOW(),’yyyymmdd_hhnnss.ssssss’) || ‘_’ || DB_NAME() || ‘.’ || IN_FILEEXTENSION;

    RETURN OUT_FILENAME;

END;

The BACKUP_FULL event is scheduled to run every Sunday but it is also possible to trigger the event manually with the statement: trigger event BACKUP_FULL

When the backup is run, the complete backup command is stored in a system table and can be retrieved using different views or procedures like sp_iqbackupsummary. Here I refer to the view sysiqbackuphistory:

select * from sysiqbackuphistory

In case of the filename being a variable, the name of the variable is stored and not the value of the variable. Consequently a select on sysiqbackuphistory will retrieve “FILENAME” instead of the real name of the backup file. In the output of sysiqbackuphistory you can see exactly the backup command as it is stored in the BACKUP_FULL event; no replacement of the variable happens.

This might not be very handy. In a situation where you want to restore your database to a certain timestamp, you can use the stored procedure sp_iqrestoreaction to retrieve all backups that must be loaded. In our case the column backup_archive_list does not contain any useful values. (Here the output consists of one line only; in production systems where archiving processes are running, the output would contain more lines).

One line in the event BACKUP_FULL must be modified to change this behavior. The goal is to substitute the variable name FILENAME with its content. The line where the backup is executed

BACKUP DATABASE FULL TO FILENAME SIZE 52428800;

must be replaced by

EXECUTE IMMEDIATE ‘BACKUP DATABASE FULL TO ”’ || FILENAME || ”’ SIZE 52428800′;

The line contains single quotes only. The event must be dropped (drop event BACKUP_FULL) and recreated.

In the string after the execute immediate the variable FILENAME will be replaced by its content. This will be executed.

After triggering the BACKUP_FULL event again, a select on sysiqbackuphistory shows the real name of the backup file.

And also the output of the stored procedure sp_iqrestoreaction contains the correct name:

By using execute immediate the content of the variable will be shown and not only its name.