Tip #57 – Creating a Differential Backup

A differential backup identifies the records that have changed since the last backup and updates an existing backup. This is different than an incremental backup, in that each differential backup is not stored as a separate file. Since the differences are applied to an existing backup you only have to restore once. Configuring a differential backup is a three step process.

1. Create a full backup of your database/free tables
2. Prepare the database/tables for a differential backup
3. Perform a differential backup

You must have a full backup ( see tip #56 ) of the data prior to performing a differential backup. Once the full backup has been performed the database/tables must be prepared for a differential backup. This only needs to be performed once unless the table structures change or new tables are added. You prepare the data for a differential backup by specifying the PrepareDiff option.

EXECUTE PROCEDURE sp_BackupDatabase ( 'C:\Data\Backup', 'PrepareDiff' )

EXECUTE PROCEDURE sp_BackupFreeTables ( 'C:\Data\FreeTables', '*.ADT', 'C:\Data\Backup', 'PrepareDiff', NULL )

Now that a full backup has been performed and the data has been prepared subsequent backups can be performed with the Diff option. This will only backup the records that have changed since the last backup.

EXECUTE PROCEDURE sp_BackupDatabase ( 'C:\Data\Backup', 'Diff' )

EXECUTE PROCEDURE sp_BackupFreeTables ( 'C:\Data\FreeTables', '*.ADT', 'C:\Data\Backup', 'Diff', NULL )

Note: When running the sp_BackupDatabase system procedure you must be connected to the data dictionary as adssys or a user who is a member of either the DB:Admin or DB:Backup group.