Firebird Backup & Restore Utility

When running gbak in backup or restore mode, there are a number of options which apply to either mode. These are:

When carrying out a backup of a database, the following switches, in addition to the common ones above, will be of use:

When carrying out a restore or replacement of a database, the following switches, in addition to the common ones above, will be of use:

There are a couple of tricks you can use to speed up the backup. The first is to prevent the garbage collection from being carried out while the backup is running. Garbage collection clears out old record versions that are no longer required and this is usually covered by a sweep — manual or automatic — or by a full table scan of any affected table. As gbak accesses all the rows in the tables being backed up, it too will trigger the garbage collection and, if there have been a large number of updates, can slow down the backup. To prevent garbage collection during the backup, use the -g[arbage_collect] option.

The second option is to backup the database using the -se[rvice] option. Although this is used to perform remote backups, it can be used locally as well. Using this option can help speed up your backups. It simply avoids the data being copied over the TCP network which can slow down the actions of the backup.

The example above backs up the employee database, on the tux server, "remotely" using the service manager. The tux server is, of course, where the command is running, so it isn’t really running remotely at all.

You can, of course, combine the -g[arbage_collect] and -se[rvice] options.

Should a database be restored or replaced? Restoring a database is the process by which you take the existing file and delete prior to replacing it on disc with a backup copy. Gbak does this when you specify the -r[ecreate_database] o[verwrite] switch or the -rep[lace_database] switch. What is the difference?

If a database exists on disc and you ask gbak to restore it using one of the two switches above, you might corrupt the database especially if the database is in use and has not been shut down using gfix. In addition, if you have only partially completed the restore of a database, and some users decide to see if they can login, the database may well be corrupted.

Finally, if the restore process discovers that the dump file is corrupt, the restore will fail and your previously working database will be gone forever.

It can be seen that restoring a database can be a fraught experience.

For security, always recreate the database with a new name — a clone — and update the aliases.conf to reflect the new name. This way, your users will always refer to the database by the alias regardless of the actual filename on the server.

During a restore operation, most likely when restoring a backup taken using an older gbak version, it is possible to see failure messages, in gbak's output, indicating malformed Unicode strings. The reason that these may occur is as explained by Helen Borrie:

The restoration of a database, from a backup, can be made to execute quicker if the -se[rvice] option is used. Although this is used to perform remote restores, it can be used locally as well. It simply avoids the data being copied over the TCP network which can slow down the actions of the restore.

The example above backs up the employee database, on the tux server, "remotely" using the service manager. The tux server is, of course, where the command is running, so it isn’t really running remotely at all.

You can, of course, combine the -g[arbage_collect]and -se[rvice]options.

If you replace an open and running database, there is a good chance that you will corrupt it. For best results and minimal chance of corrupting a database, you should close it before replacing it. To close a database, use gfix as follows:

The example above prevents any new transaction from being started which prevents new queries being executed or new sessions connecting to the database. It will wait for up to 60 seconds for everyone to logout and for all current transactions to complete before shutting down the database. If any long running transactions have not completed by the end of 60 seconds, the shutdown will timeout and the database will remain open.

This example takes a backup, then immediately overwrites the original database using the new backup. This is not normally a good idea as the first action of a restore is to wipe out the database.

It is possible to use gbak to recreate an empty database containing only the various domains, tables, indices and so on, of the original database but none of the data. This can be useful when you have finished testing your application in a test environment and wish to migrate the system to a production environment, for example, but starting afresh with none of your test data.

When the above dump file is restored on the production server, only the metadata will be present.

There is another way to create a database with no data and only the metadata. Simply restore from an existing dump which contains the data and supply the -m[eta_data] switch to the restore command line. The database will be restored but none of the original data will be present.

The -m[eta_data] switch can be used on either a backup or a restore to facilitate the creation of a clone database (or overwrite an existing one) with no actual data.

The gsplit filter application, documented in its own manual, doesn’t actually work anymore. This filter was supplied with old versions of InterBase and Firebird to allow large database backups to be split over a number of files so that file system limits could be met. Such limits could be the size of a CD, the 2GB limit on individual file sizes on a DVD, where some Unix file systems have a 2 GB limit and so on.

Gbak allows the dump files to be split into various sizes (with a minimum of 2048 bytes) and will only create files it needs.

The sizes after each filename indicate how large that particular file is allowed to be. The default size is bytes, but you can specify a suffix of k, m or g to use units of kilo, mega or gigabytes.

If the dump completes before writing to some files, then those files are not created. A dump file is only ever created when it must be.

The size of the final dump file will be quietly ignored if the database has grown too large to allow a truncated backup to complete. If, in the example above, the backup needs a total of 1500M, then the last file would be written to a final size of 900m rather than the 600m specified.

To restore such a multi-file backup requires that you specify all of the filenames in the dump and in the correct order. The following example shows the employee database above being restored from the two files dumped above:

Normally the ODS used is the one in force by the version of Firebird used to restore the database. So, the examples above will actually change the ODS when the database is restored. The backup should be taken using the gbak utility supplied by the old ODS version of InterBase or Firebird. The restore should be carried out using gbak from the newer version of Firebird.

After the above, the old 2.0 Firebird database will have been recreated — wiping out the old database — as a Firebird 2.1 database with the corresponding upgrade to the ODS from 11.0 to 11.1.

The script setenv_firebird is not supplied with Firebird and simply sets PATH etc to use the correct version of Firebird as per the supplied parameter.

The default database cache is created when the database is created, or subsequently by using gfix. Gbak can restore a database and reset the default cache size as well. The process is as follows:

The default cache size is used when the number of buffers is zero, as in the first example above. Gbak allows this to be changed if desired. Gbak, however, cannot set the cache size back to zero. You must use gfix to do this.

Similar to the example above to change the default database cache size, the database page size can also be changed using gbak.

Sometimes you do not want your reporting staff running intensive queries against your production database. To this end, you can quite easily create a clone of your production database on a daily basis, and make it read-only. This allows the reporting team to run as many intensive reports as they wish with no ill effects on the production database and it prevents them from inadvertently making changes.

The following example shows the production employee database running on Linux server tux, being cloned to the reporting team’s Linux server named tuxrep. First on the production tux server:

Then on the reporting team’s tuxrep server:

You may use gbak to create a clone of a database, on the same server, without needing to create a potentially large dump file. To do this, you pipe the output of a gbak backup directly to the input of a gbak restore, as follows.

You will notice that the output file name for the backup is stdout and the input file name for the restore is stdin. This ability to pipe standard output of one process to the standard input of another, is how you can avoid creating an intermediate dump file. The commands above assume that there are suitable alias names set up for both emptest and emptest_2. If not, you will need to supply the full path to the two databases rather than the alias.

The -replace option on the restore process will overwrite the database name specified — as an alias or as a full path — if it exists and will create it anew if it doesn’t. You may also use the -recreate overwrite option as an alternative. Both have the same result.

If you don’t want to overwrite any existing databases, use -create which will only create a database if it doesn’t already exist, and will exit with an error if it does. In POSIX compatible systems, the error code in $? is 1 in this case.

Further examples of backing up and restoring remote databases over ssh, using the stdin and stdout file names, can be seen below.

Databases can have shadow files attached in normal use. Gbak happily backs up and restores those as well and in normal use, shadow files will be recreated. Should you wish to restore the database only and ignore the shadows, gbak can do that for you as the following example shows.

Firebird’s gbak utility can make backups of a remote database. To do this, you need to connect to the service manager running on the remote server, this is normally called service_mgr. The following example shows the Firebird employee database on server tuxrep being backed up from the server tux. The backup will be written to the remote server, in other words, the backup file will be created on the tuxrep server and not on the tux one. The network protocol in use is TCP.

The backup file will have the same owner and group as the Firebird database server — on Unix systems at least.

It is also possible to restore a remote database in this manner as well, and gbak allows this.

As ever, you are advised to beware of replacing a database in case there are problems during the restore. The example above recreates the existing database in read-only mode but this need not always be the case.

A remote backup can also be run on the database server itself! On Windows, this makes no difference, but on Unix systems, this local-remote method of backups and restores reduces network traffic. The 'remote' server, in this case, is not actually remote it is just the method of running the backup — connecting to the service manager — that implies remoteness.

And corresponding restores can also be run 'remotely':

The format of the parameter used for the -service switch is different according to the nature of the network protocol in use:

As shown above, you can use the special file names stdin and stdout to backup and restore a database to a separate database on the same server. However, you can also use the same tools, over an SSH connection to a remote server, and pass the backup of one database directly to a restoration of a separate one.

The first example copies a local database to a remote server where Firebird is running and the firebird user has its environment set up so that the gbak tool is on $PATH by default, on login.

When the above is executed, you will be prompted for a password for the remote firebird user on server tuxrep, assuming that you don’t have a proper SSH key-pair already set up and active. The command will replace the local database according to the alias name emptest but you can, if required, supply full path names for the databases. The following shows an example of the above being executed.

As you can see, there’s not much in the way of output, but you can connect remotely and check:

The next example, shows a remote database being backed up to a local one, in a similar manner.

You can see that a new tuxrep_emptest.fdb database has been created. Does it work? Checking with isql shows that it does.

The final example shows how to backup a remote database on one server, to a remote database on another.

Gbak and nbackup are the best tools to use when backing up and/or restoring Firebird databases. They have been extensively tested and know the internals of the database and how it works, so the chances of these tools corrupting your valuable data are very slim. However, some DBAs still like to use external tools (those not supplied with Firebird) to make backups for whatever reason.

Because it is not possible for external tools to know where a database is to be found, given the alias name, the script writer and/or DBA must explicitly find out the correct location of the database file(s) and supply these to the external tool. To make this simpler for script writers, my own installation uses a standard in my aliases.conf file as follows:

The last rule applies to my installation only and means that the following simple backup script will work. If multiple file databases were used, more coding would be required to take a backup using external tools.

The following shows the use of the gzip utility on a Linux server to take and compress a backup of a running database. The following is run as the root user due to the requirement to run gfix to shut down the database.

The restore process for this database would be the reverse of the above. Again, the following runs as root.

If you do not specify a mode switch such as -b[ackup] or -c[reate] etc, then gbak will perform a backup as if the -b[ackup] switch had been specified — provided that the other switches specified are correct for a backup.

Only a SYSDBA or the owner of a database can take a backup of the database, however, any authenticated user can restore a database backup using the -c[reate] switch. This means that you must make sure you prevent your backup files from falling into the wrong hands because there is nothing then to stop unauthorised people from seeing your data by the simple process of restoring your backups onto their server.

The database restore will fail, of course, if the user carrying it out is not the database owner and a database with the same filename already exists.

The -y suppress_output switch is supposed to cause all output to be suppressed. Similar in fact to running with -v[erify] not specified. However, all it seems to do is cause the output (according to the -v[erify] switch setting) to be written to a file called suppress_output, however this only works once because the next run of gbak with -y suppress_output will fail because the file, suppress_output, already exists.

It is possible that this problem was introduced at version 2 for Firebird, because both 2.0 and 2.1 versions actually use the -y suppress switch rather then -y suppress_output. Using this (shorter) option does work as intended and the output is indeed suppressed.

If you specify a log file name with the -y <log file> switch, and the file already exists, then even though the firebird user owns the file, and has write permissions to it, gbak cannot overwrite it. You must always specify the name of a log file that doesn’t exist. On Linux systems, the following might help:

The above is quite useful in as much as it prevents you from overwriting previous backups that may be required. The downside is that you now need to introduce a housekeeping system to tidy away old, unwanted backups to prevent your backup area filling up.

Gbak recognizes the literal strings 'stdin' and 'stdout' as source or destination filenames. In POSIX systems, when the standard input and/or standard output channels are used, it is not permitted to execute seek operations on these channels. Using 'stdin' or 'stdout' as file names with gbak will force gbak to use processing that will not seek on the input or output channels, making them suitable for use in pipes — as per the examples in the recipes section above.

These file names, while they appear to be POSIX names, are definitely not synonyms for /dev/stdin or /dev/stdout, they are simply literals that gbak checks for while processing its parameters. Do not attempt to use names /dev/stdin or /dev/stdout in a piped process as it will most likely fail.

If you wish to create a dump file actually named either stdin or stdout, then you should specify the filename as a full, or relative, path name such as ./stdin or ./stdout, which causes gbak to treat them as a literal file name rather than a special file name that causes different to normal processing.during the dump or restore process.