ALTER DATABASE

ALTER DATABASE

BEGIN BACKUP
END BACKUP
ADD DIFFERENCE FILE
DROP DIFFERENCE FILE

`ALTER 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 file system 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 file system. (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