Advantage Developer Zone


Online Backup Overview

Thursday, February 5, 2009

Advantage Online Backup functionality allows users to create a snapshot, or update a snapshot of the data without having to close all the tables first. This is especially useful in a 24X7 operation or for making regular backups during business hours on a busy system. Online backup can create full backups of the data or update an existing backup with changes through the use of differential backups.

Online backups preserve all referential integrity (RI) rules by ensuring that all RI cascade operations are complete before the backup is executed. If transactions are active during a backup the data will be backed up with the values which existed in the table prior to the start of the transaction. 

Perform a Backup

Online backups are performed via system procedures which are executed using SQL statements. The sp_BackupDatabase and sp_BackupFreeTables system procedures perform a backup of the data. The sp_RestoreDatabase and sp_RestoreFreeTables system procedures restore a prior backup.

The sp_BackupDatabase system procedure takes two string (memo) parameters. The first is the destination path and the second is the backup options (see below). The sp_BackupFreeTables system procedure takes five string parameters. The first is the path to the tables, second the source mask (i.e. *.adt), third destination path, fourth options and finally any passwords for encrypted free tables.

The destination path can be any location that the server has access to. This includes drives on the server (internal and external), network shares and Network Attached Storage (NAS) devices. You can use a Server-Side alias as the target for your backup path which makes managing the path a bit easier.

The following examples back up all of the tables for the specified database or free table directory.

// Full backup of a data dictionary no options specified
EXECUTE PROCEDURE sp_BackupDatabase( '\\cfranzxp\Backup\', '' )
// Full backup of free table directory
// This backs up all DBF and ADT tables
// No options or passwords specified
EXECUTE PROCEDURE sp_BackupFreeTables ( 'C:\Data\FreeTables', '*.adt;*dbf',">'\\cfranzxp\Backup\', '', '' )

The backup image that is saved is not structurally complete; therefore, you must restore the backup image before using it. To restore data use the sp_RestoreDatabase or sp_RestoreFreeTables shown below:

// Full restore of a database
EXECUTE PROCEDURE sp_RestoreDatabase( '\\cfranzxp\Backup\SampleDB.ADD', 'password','c:\Data\SampleDB\SampleDB.ADD', '' )
// Full restore of free tables
EXECUTE PROCEDURE sp_RestoreFreeTables( '\\cfranzxp\Backup\', 'C:\Data', '', '' )

Each of these system procedures report their results in a cursor which is returned when the procedure is run. For details about this cursor refer to the help file.

Backup and Restore Options

There are several backup and restore options such as including and excluding files, preparediff and diff. Options are specified as a string for all of the backup and restore system procedures. To pass multiple options separate each option with a semicolon (;) for example: ‘include=Customer;PrepareDiff’. For the include and exclude options table names are comma separated for example ‘include=Customer, Employee’

To perform a differential backup, only supported for ADT tables, the database or tables have to be prepared first. This is done by specifying the option PrepareDiff and performing a backup. Specify the Diff option on subsequent backups so only changes are backed up. Differential backups write to an existing backup meaning that the backup is always up to date and only the most recent backup needs to be applied. You will have to run PrepareDiff again if the database or table structures change.

If you only wish to backup specific tables use the include option. The backup operation will only backup the table names specified in the comma separated list following the include keyword. If you want to backup all tables except specific ones, (i.e. a replication queue) use the exclude option. All tables except the ones listed after the exclude keyword will be backed up.

A complete list and description of the options is available in the help file

Rights and Permissions

The Advantage Service needs network rights to the destination path in order to perform the backup. For Windows operating systems Advantage runs as the system account by default which has full rights to all local drives on the system. However, the system account does not have rights to any share on the network. If the destination path is on a network share within the same domain as the server running Advantage, give the computer account full control of the share. This gives the System account the proper rights to the share.

Another option is to create a service account for the Advantage service. You will have to give this account full control permission on the destination path. You will also have to give this account full control permission for any data shares on the server running Advantage. If you are not using a domain you will have to create local user accounts with the same name and password on each machine to ensure that Advantage has the proper rights.

On Linux systems the Advantage installer creates a user and group called advantage. This user must have full access to all directories that are accessed by the Advantage Daemon. Ideally the advantage user will be the owner of all the data directories and files. When backing up to remote destinations, these same rights must be granted to the advantage user on the remote machine(s).

A connection to the database by a user with the proper rights is required to perform a backup. In version 8.x only the administrator (adssys) had rights to backup a database. With version 9.0 any user who is a member of the DB:Backup group can perform a backup for the database.


Advantage Online Backup provides a mechanism for creating reliable backups while the database is in use. External backup software on the contrary can potentially interfere with users access to the data and often provides an incomplete or inconsistent backup. The online backup functionality is performed by the Advantage Database Server with very little impact on the active users. Backups can be performed by any user who has the appropriate rights or scheduled through the use of a command line utility (adsbackup.exe) provided with the server.