Advantage Developer Zone


Online Backup Enhancements

Monday, June 9, 2008

Online (hot) Backup functionality was added to Advantage Database Server in version 8.0. Online Backup allows backups of free tables or data dictionary bound tables while the tables are in use. This creates a robust solution which can assist with your high availability strategy. With the release of Advantage 9 several enhancements were added to the Online Backup feature.

Backup Overview

Online backup functionality is only available with the Advantage Database Server (ADS) and will not work with the Advantage Local Server (ALS). Backups can be initiated using a stored procedure call (sp_BackupDatabase or sp_BackupFreeTables), with Advantage Data Architect (ARC) or with the adsbackup command line utility.

Online Backups take a snapshot of the tables during the initialization process and the data is written to the specified destination. Snapshots include all the committed changes made by users at the time the snapshot is taken. If several tables are involved in a transaction during the backup the server will wait before beginning the snapshot. The snapshot will be taken after the transaction has been committed. If after a short time the tables are still involved in a transaction a “fuzzy” snapshot will be backed up which does not reflect the changes made in the uncommitted transaction.

Differential Backups

ADT tables allow for differential backups which only backup the records which have changed since the last backup. This is different than incremental backups since differential backups are applied to an existing full backup. The backup is as current as the last differential backup and only one restore operation is necessary.

You must prepare the tables or dictionary for a differential backup. Perform a full backup with the ‘PrepareDiff’ option. Run subsequent backups with the ‘Diff’ option. If the table structure or indexes are changed you will need to prepare the database again.

Backup Options

You can restrict the tables which are backed up using the INCLUDE or EXCLUDE option. Use one of these options if you do not want to backup all of the tables in the given directory or dictionary. By default existing tables are overwritten if they exist in the target directory. You can ensure that this doesn’t happen by specifying the ‘DontOverwrite’ option.

To backup only the data dictionary files (add, am) use the ‘MetaOnly’ option. The ‘PrepareDiff’ and ‘Diff’ options are used for differential backups.

Version 9 Enhancements

Several enhancements were added to the online backup functionality in version 9.0. The first is the ability of the online backup to backup external AEP libraries. This functionality does not currently work with COM or .NET Assembly libraries.

A new option ‘TableTypeMap’ can be used to allow backup of a folder containing a mixture of ADT and DBF free tables. You can also use this option if you are using a custom extension for your files. For example: TableTypeMap=dat=ADS_CDX would treat *.dat files as DBF/CDX files.

A new switch (-x) has been added to the adsbackup utility which will skip creation of the backup results table if no errors are encountered. By default a table with the naming convention BACKUP_CCYYMMDDHHMMSS is created which contains any errors encountered during the backup. More details on the backup results table can be found here.

To perform a backup of a data dictionary with version 8.x you must provide the adssys password. Version 9.0 includes new database roles one of which is DB:Backup. Members of this group have the right to perform an Online Backup operation on the data dictionary.


Advantage Online Backup functionality provides a way to perform backups on live data without affecting connected users by creating a logically correct snapshot of the data when the backup is requested. The backup can be initiated from an application using defined system procedures, by using the provided adsbackup utility or from Advantage Data Architect. Differential backups can be performed on ADT tables which can reduce the time the backup takes since only changed records are backed up. Advantage 9.0 adds the ability to backup multiple free table types, backup of AEP files and new options for adsbackup. The DB:Backup role allows more users to initiate backup and restore operations on data dictionaries.