Scheduling Automatic Incremental Backups

SQL Anywhere 16, which is installed with SAP Mobile Platform, provides a function for automatically backing up databases on a weekly, daily, or hourly basis.

For more information about automatic backup options using schedules and events, see the SQL Anywhere documentation.

This example schedules an event in the target database to execute an incremental backup every two hours, and writes a backup transaction log into c:\\HA_DR\\dailybackup\\clusterdb<day_name>..

CREATE EVENT DailyIncrBackup
SCHEDULE
START TIME '00:01' EVERY 2 hours
HANDLER
BEGIN
DECLARE dest LONG VARCHAR;
DECLARE day_name CHAR(20);
SET day_name = DATENAME( WEEKDAY, CURRENT DATE );
SET dest = 'c:\\HA_DR\\dailybackup\\clusterdb' || day_name; 
BACKUP DATABASE DIRECTORY dest
TRANSACTION LOG only
TRANSACTION LOG TRUNCATE
TRANSACTION LOG RENAME;
END;

To run the event automatically, save each event script as a SQL file and execute it with the dbisqlc or dbisql command.

dbisqlc -c connstring c:\DailyIncrBackup.sql

For a daily incremental backup, create a separate folder for the transaction log for each database; otherwise, the transaction logs will conflict and cannot be applied successfully.

To enable or disable the daily incremental backup event, enter:

Alter event DailyIncrBackup enable | disable;

When you restore the database, consider whether to modify or disable the event, or adjust the target backup location.