The IBExpert Services menu item Restore Database allows you to restore the database from a backed up file.
A database restore is required in the following situations:
- Following approximately 1.3 billion transactions in order to reset the transaction space.
- Following 255 metadata changes on a single table; otherwise no further metadata changes are possible. Please refer to 253 changes of table left for details.
- When changing the Firebird/InterBase® version you need to backup the old version and restore to the new version number.
- When you need to alter the database page size.
The main advantage of restoring the database regularly is that all the counters are set back; the TIP is now set back to the minimum available number, as is the record format counter.
Before restoring a backup file into a database, it is important to first disconnect the database! - Otherwise you could end up with a corrupt database should users try to log in and perform data operations during the restore.
The Files page allows the following specifications:
Restore into: Select to restore into the existing database, or create a new database. When restoring into the existing database, select from the list of registered databases; if restoring to a new database, then set the database file name not forgetting the drive and path. When restoring into a new database you may also check the option in the bottom left-hand corner, Register database after restore, which automatically opens the IBExpert Register database window following a successful restore, allowing you to immediately register your new database for work with IBExpert.
File name: Specify the backup file name which is to be restored. The [...] button to the right of this row allows you to find an existing file name, drive, and path. The suffixes
.FBK are traditionally respectively used for InterBase® and Firebird backup files.
The following restore options may be checked/unchecked as wished:
- Deactivate indexes: If this option is checked, database indices are deactivated while restoring. This option is used to improve restore performance. If this option is not checked, Firebird/InterBase® updates indices after all tables have been populated with the restored rows. This option may also be necessary if the database contains data with a unique index, but there are values in the table that are not actually unique. It can also be used when the field length in one or more tables is to be altered retrospectively; or when an index is simply not working due to some undiscovered inconsistencies.
- Don't recreate shadow files: If this option is checked, shadow files are not recreated while restoring.
- Don't enforce validity conditions: When this option is checked, database validity conditions such as constraints on fields or tables are not restored. This option is useful if the validity constraints were changed after data had already been entered into the database. When a database is restored, Firebird/InterBase® compares each row with the metadata; an error message is received if incompatible data is found. Once the offending data has been corrected, the constraints can be added back.
- Commit after each table: If this option is checked, work is committed after restoring each table. This allows all those tables to be restored and committed where there is no corrupted data. It restores metadata and data for each table in turn as a single transaction and then commits the transaction. This option is useful if corrupt data is suspected in the backup file, or if the backup is not running to completion. Normally, Firebird/InterBase® restores all metadata and then restores the data. Should you encounter problems when restoring your database, deactivate this option and retry.
- Replace existing database: If this option is checked the restored database replaces the existing one. Leaving this option unchecked provides a measure of protection from accidentally overwriting a database file.
- 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.
- Use all space: This option should be checked when restoring the database onto a CD where little or no data manipulation will be performed on the database, as all (i.e. 100%) space is then used, as opposed to the usual 80% for databases which are subject to alterations and stored on hard drives. Using all space on a database with regular inserts, alterations and deletes will result in a lot of defragmentation. Should this options be mistakenly be checked, it can be deactivated using
GFIX. Alternatively perform the restore again, this time with the correct parameters.
- Metadata only: This option produces an empty copy of the database. Do not use this option with Replace existing database as you will end up with an empty database, and all your data lost! This option may also be used to restore the framework of a corrupt database, to allow analysis and repair work, although the IBExpert Tools menu item, Exract Metadata?, is more flexible, as it allows you to not only extract the metadata but also all the healthy data as well.
- Page size: Database page size in bytes. This is the only option allowing the page size for an existing database to be altered. We still have the options her to specify 1K, 2K, 4K etc. However since Firebird 2.1 1K and 2K are automatically converted to at least 4K. The best page size depends on a number of factors discussed in detail under Page size.
- Client Library File: This is an added possibility to specify a client library which will be used while restoring. This option allows the user to specify whether he requires the InterBase® or the Firebird client library for each IBExpert connection. The default client library is that specified in the IBExpert Options menu item, Environment Options.
- Verbose: Check Verbose to receive a detailed protocol of the current database restore process, by writing step-by-step status information to the output log. The options On Screen or Into File (not forgetting to select or specify a file name for this protocol) need to be specified before starting the backup. This option is useful if the restore is failing, and the reason needs to be analyzed. Please note that since IBExpert version 2012.11.15, IBExpert now analyzes and retrieves error messages from the restore log even if the Verbose option is off. If errors are detected they will be placed into the end of the log and the user will be warned about possible problems.
- Register database after restore: This option is only available if the Restore into a new database option has been selected at the top of the Restore database window. Checking this option automatically opens the Register database dialog following a successful restore into a new database, allowing you to register your new database for work in IBExpert.
Under normal circumstances, none of the above restore options should need to be specified. If inconsistencies between the metadata and the data itself are suspected, check the Commit After Each Table, Deactivate Indexes, and Don't Enforce Validity Conditions options.
You will be asked to login:
before the restore can start:
Please note that Firebird/InterBase® does not backup indices. It only backs up the index definition. When the database is restored Firebird/InterBase® uses this definition to regenerate the indices.
Using the Database Registration menu item, Backup/Restore default file names, 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).
Empty pages are automatically removed during a backup and restore, which reduces the total database size.
The transaction number in the TIP is reset to zero. The cache works with considerably more efficiency following a backup and restore as the pages are reordered. It is therefore recommended not only to backup but also to restore the database regularly (e.g. once a month).
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.
Allowing users to login during a restore
Repairing a corrupt database
Why is a database backup and restore important?
Firebird database housekeeping utility
How to analyse and repair a corrupted database
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