The IBExpert Services menu item Backup Database allows you to create a backup or copy of the database, saving it to file. This database copy may be kept simply for security reasons, or restored for the reasons detailed in Why is a database backup and restore important?.
This menu item is the management window for the Services API. Here you can specify everything that you can also do on the command line using
GBAK. To automate your backup and restore use the IBExpertBackupRestore service found in the IBExpert Services menu item, HK-Software Services Control Center.
A database backup may be performed without having to disconnect the database; users may continue their work as Firebird/InterBase® uses its multi-generational architecture to take a snapshot of the database at a moment in time the backup is requested. All information generated by committed transactions and present at this moment, is backed up.
First select the database to be backed up from the drop-down list of registered databases. Then select either an existing backup filename, or add a new backup file using the Insert File icon (or [Ins] key).
The [...] button to the right of this row allows you to find an existing file or specify the drive, path and backup filename for a new file. Please note that IBExpert will only create a filename on the server, and not locally (as with
GBAK), because IBExpert uses the Services API. A local backup can only be performed using
GBAK. The suffixes
.FBK are traditionally respectively used for InterBase® and Firebird backup files. A file size only needs to be specified when working with secondary files. All files in a multi-file database are backed up (i.e. both secondary files and shadow files). Firebird/InterBase® understands the links that exist with secondary database files and with shadows. Whereas the operating system backup works on a file-by-file basis, Firebird/InterBase® always backs up all files in a database.
- Ignore check sum: If this option is checked, check sum errors in the database header pages, where the database connection properties are stored, are ignored in the backup. As Firebird and InterBase® normally abort the backup when check sum errors are discovered, this is a way to force a backup when there are problems. Note that UNIX versions do not use check sums.
- Ignore transactions in Limbo: If this option is checked, transactions in limbo, i.e. transactions that can't be defined as executed or aborted, are ignored in the backup. Only those most recent, committed transactions are backed up. It allows a database to be backed up before recovering corrupted transactions. Generally in limbo transactions should be recovered before a backup is performed.
- Backup Metadata only: If this option is checked, only the database's definition (i.e. the metadata, which provides an empty copy of the database) is saved. (If a database copy with certain data content is required, then use the IBExpert Script Executive.)
- Garbage collection: If this option is checked, garbage collection is executed during the backup. By disabling this option, the backup can be speeded up considerably. (Refer to Garbage collection for further information.)
- Old metadata description: If this option is checked, old metadata descriptions are included into the backup database. This is included for compatibility reasons for older InterBase® versions.
- Convert to Tables: This option converts the database data to tables in the backup. This concerns external files. It is possible in Firebird/InterBase® to create a table as an external file - this option converts them to internal database tables.
- Prevent database/transaction triggers from firing: added in IBExpert version 2013.02.15. This is equal to the
isc_dpb_no_db_triggersoption in additional connection parameters.
- Format: Select the data format for the backup database file. Transportable is the recommended default option, as it allows a restore into different Firebird/InterBase® versions if wished, i.e. it saves the data and metadata to a generic format, as opposed to the option Non-Transportable. (Please note that when backing up and restoring, for example, from InterBase® 4 to Firebird 1.5, stored procedures are restored as blobs, so that they may not initially work.)
- Verbose: Checking Verbose provides a detailed protocol of the current database backup process by writing step-by-step status information to the output log.
Select the option On Screen or Into File for the log (not forgetting to select or specify a filename for this protocol). This option is useful if the backup is failing and the reason needs to be analyzed.
Using the IBExpert menu item Database / Database Registration Info, default backup filenames, paths and drives may be specified if wished, along with default backup and restore options. This information may be specified when initially registering a database in IBExpert (see Register Database) or at a later date (see Database Registration Info).
In normal circumstances, the backup should run smoothly without any of the above options having to be checked. If however, corrupt or damaged data is suspected or problems have been encountered, alter the Format to Non-Transportable and check the options Ignore Check Sum and Ignore Transactions in Limbo. Although this will not provide the usual database compression, it does provide a complete copy of the database, which is important before starting to repair it.
To automate the backup/restore process for your databases, use the HK-Software Service Center's IBExpertBackupRestore server tool. IBExpertBackupRestore is a comprehensive utility, providing automatic backup and restore facilities for Firebird and InterBase® databases with backup file compression even an option to automatically mail backup/restore log files.
ALTER DATABASE BEGIN BACKUP
ALTER DATABASE END BACKUP
Repairing a corrupt database
Restoring a backup to a running database
Automating the database backup and restore
Performing regular backups protects from hardware failures and data corruption, which cannot be fixed by the Firebird/InterBase® maintenance tools. It is important to use the Firebird/InterBase® backup and restore facilities even though most networks include a facility for data backup and restore across the network, because:
- Operating system backups require exclusive access to the database. The Firebird/InterBase® backup runs parallel with concurrent database accesses by other users. Firebird/InterBase® uses its multi-generational architecture to take a snapshot of the database at a moment in time for the backup. All information generated by committed transactions and present at this moment is backed up.
- All files in a multi-file database are backed up. Firebird/InterBase® comprehends the links between the different database files and shadows. The operating system backup processes files one after the other and saves them to the specified file or medium, so that all the various files are backed up in different versions and they cannot work together correctly anymore when restored. The Firebird/InterBase® backup backs up all database files automatically.
- The different versions of Firebird/InterBase® use different database file formats, so that it is impossible to copy a file directly from one operating system environment to the required format of another operating system environment. The Firebird/InterBase® backup utility allows a transportable backup format, so that this file can be restored on any desired Firebird/InterBase® platform. Please note: When backing up and restoring, for example, from InterBase® 4 to Firebird 1.5, stored procedures are restored as blobs, so that they may not initially work.
- The Firebird/InterBase® backup discards outdated data sets and index files, resulting in a smaller backup file (please refer to garbage collection for more information).
- Empty pages are also automatically removed during a backup and restore, which reduces the total database size. The transaction number in the TIP is reset to zero (the total number of transactions that can be recorded in a TIP is approximately 1.3 billion!). The cache works with considerably more efficiency following a backup and restore as the pages are reordered. Please note: In Firebird 1.5 the memory manager allows new data sets to automatically be stored in old pages, without first having to backup and restore.
- During a Firebird/InterBase® backup the integrity and references for all database objects, e.g. domains, tables, indices, views, triggers, stored procedures, generators, exceptions, and permissions, are checked.
- Executing a backup and restore is the only way to subsequently alter fundamental parameters in the database structure, such as the page size and distribution across secondary files.
It is therefore recommended to not only backup but also restore the database regularly (e.g. once a month).
InterBase® and Firebird command-line utilities:
Firebird Backup & Restore Utility
Firebird 2.0.4 Release Notes: Backup tools
Firebird 2.0.4 Release Notes: Reworking of garbage collection
Recovering a corrupt database
Firebird for the Database Expert: Episode 4 - OAT, OIT and sweep
Firebird Database Housekeeping Utility: Database housekeeping and garbage collection
Server and client backup strategies
How to analyse and repair a corrupted database
Firebird database housekeeping utility
Firebird for the database expert: Episode 3 - On Disk Consistency
Preventing data loss
Alternative database repair methods
Detect and avoid database errors
Tracking down crashes on Win32 systems
Tracking down crashes on Linux
Transaction options explained
Multi-generational architecture and record versioning