meta data for this page
  •  

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.

back to top of page

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.

back to top of page

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.

back to top of page

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