DATABASE

Table of Contents

CREATE DATABASE
ALTER DATABASE

CREATE DATABASE

Table of Contents

16 Kb page size supported, 1 and 2 Kb deprecated
Default collation for the database
DIFFERENCE FILE parameter

Available in: DSQL, ESQL

Syntax (partial):

CREATE {DATABASE | SCHEMA}
   ...
   [PAGE_SIZE [=] size]
   ...
   [DEFAULT CHARACTER SET charset [COLLATION collation]]
   ...
   [DIFFERENCE FILE 'filepath']

size  ::=  4096 | 8192 | 16384
If the user supplies a size smaller than 4096, it will be silently converted to 4096. Other numbers not equal to any of the supported sizes will be silently converted to the next lower supported size.

16 Kb page size supported, 1 and 2 Kb deprecated

Changed in: 1.0, 2.1

Description: Firebird 1.0 has raised the maximum database page size from 8192 to 16384 bytes. In Firebird 2.1 and up, page sizes 1024 and 2048 are deprecated as inefficient. Firebird will no longer create databases with these page sizes, but it will connect to existing small-page databases without any problem.

Default collation for the database

Added in: 2.5

Description: In Firebird 2.5 and up, you can specify a collation with the default character set, as shown in the Syntax block above. If present, this collation will become the default collation for the default character set (and hence for the entire database, except where another character set is used).

Example:

create database "colltest.fdb" default character set iso8859_1 collation du_nl

Please notice: The keyword to use here is COLLATION, not the usual COLLATE.

DIFFERENCE FILE parameter

Added in: 2.0

Description: The DIFFERENCE FILE parameter was added in Firebird 2.0, but not documented at the time. For a full description, see ALTER DATABASE :: ADD DIFFERENCE FILE.

ALTER DATABASE

Table of Contents

BEGIN BACKUP
END BACKUP
ADD DIFFERENCE FILE
DROP DIFFERENCE FILE

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 will be created if and when the database enters copy-safe mode.
If you provide a relative path or a bare filename here, 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 setting, 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 preset path and/or filename that would otherwise have been used the next time the database went into copy-safe mode, and reverts to the default behaviour.

Example:

alter database drop difference file