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.
Hi Jürgen,
that's good stuff! Very useful. Thanks for sharing.
Best
Axel
Hi Jurgen,
Is it required/mandatory to include automatic backup in SAP NLS configuration? in some doc it stated that it is an optional connection parameter. we are verifying if there is any impact in the deletion stage if we
Regards,
Joan
Hi,
I set up NLS in a way that an IQ incremental backup is done and afterwards the data will be deleted from the primary database.
This is covers the (unlikely) scenario: data has been deleted from the primary database. And an IQ backup has not been done, IQ goes down, and does not come up again. If you are not able to start IQ anymore, you probably have to restore from backup. In this case the data from your achiving run is probably lost as it was not part of a backup yet. Maybe data is not completely lost. If you have system copy, maybe data is still available in your QA system. Or you have to restore your primary database and get the data from there.
All in all the descision is up to you. If you rate the described scenario as possible than you might want to include IQ incremental backups. If you rate it as unlikely and you're probably able to recover lost data, then an IQ backup as part of an archiving run is not necessary.
Best regards,
Juergen
Hi Juergen,
Thank you for your clarification. That means the step is optional.
And if we do not set up automated backup, then IQ backup is no longer impacting the NLS process? deletion phase will no longer fail if no current backup is found since the only backup are Differential and Full?
Does this hold true for SAP 7.4 too? We will be migrating to 7.4 SP13. The docs I've found with steps in setting up NLS are related to 7.3 mostly.
Hi Joan,
I always configured IQ backups so I'm not 100% sure how to skip this. One solution could be to remove the backup command from the BACKUP_INCREMENTAL event. So only update the BACKUP_REQUEST table.
But I think this should work as well: the backup event is defined in the connection parameters of transaction RSDANLCON. Maybe ommitting the event there will help. But you have to test this thoroughly.
I haven't heard of any changes with 7.4. So it should work with that version as well.
Best regards,
Juergen
thank you Juergen we are no assessing if we will take the risk of not implementing it