ALTER DATABASE

Table of Contents

BEGIN BACKUP
END BACKUP
ADD DIFFERENCE FILE
DROP DIFFERENCE FILE

Tip

Find a more recent version at Firebird 5.0 Language Reference: DATABASE

Available in: DSQL, ESQL

Description: Alters a database's file organisation or toggles its “safe-to-copy” state.

Syntax:

ALTER {DATABASE | SCHEMA}
   [<add_sec_clause> [<add_sec_clause> ...]]
   [ADD DIFFERENCE FILE 'filepath' | DROP DIFFERENCE FILE]
   [{BEGIN | END} BACKUP]

<add_sec_clause>  ::=  ADD <sec_file> [<sec_file> ...]

<sec_file>        ::=  FILE 'filepath'
                          [STARTING [AT [PAGE]] pagenum]
                          [LENGTH [=] num [PAGE[S]]

The DIFFERENCE FILE and BACKUP clauses, added in Firebird 2.0, are not available in ESQL.

BEGIN BACKUP

Available in: DSQL

Added in: 2.0

Description: Freezes the main database file so that it can be backed up safely by filesystem means, even while users are connected and perform operations on the data. Any mutations to the database will be written to a separate file, the delta file. Contrary to what the syntax suggests, this statement does not initiate the backup itself; it merely creates the conditions.

Example:

alter database begin backup

END BACKUP

Available in: DSQL

Added in: 2.0

Description: Merges the delta file back into the main database file and restores the normal state of operation, thus closing the time window during which safe backups could be made via the filesystem. (Safe backups with gbak are still possible.)

Example:

alter database end backup

Tip

Instead of BEGIN and END BACKUP, consider using Firebird's nbackup tool: it can freeze and unfreeze the main database file as well as make full and incremental backups. A manual for nbackup is available via the Firebird Documentation Index.

ADD DIFFERENCE FILE

Available in: DSQL

Added in: 2.0

Description: Presets path and name of the delta file to which mutations are written when the database goes into “copy-safe” mode after an ALTER DATABASE BEGIN BACKUP command.

Example:

alter database add difference file 'C:\Firebird\Databases\Fruitbase.delta'

Notes:

This statement doesn't really add any file. It just overrides the default path and name for the delta file that's going to be created if and when the database enters copy-safe mode.
If you provide a relative path here (or a bare filename), it will be appended to the current directory as seen from the server. On Windows, this is often the system directory.
If you want to change an existing path and name, DROP the old one first and then ADD the new one.
When not overridden, the delta file gets the same path and filename as the database itself, but with the extension .delta

DROP DIFFERENCE FILE

Available in: DSQL

Added in: 2.0

Description: Removes the delta file path and name that were previously set with ALTER DATABASE ADD DIFFERENCE FILE. This statement doesn't really drop a file. It only erases the name and path that would otherwise have been used the next time around and reverts to the default behaviour.

Example:

alter database drop difference file