Friday, May 31, 2013

How to backup a working Firebird database using a third party backup tool.

One of the basic problems of trying to use a third party backup tool, or simple file copy of a Firebird database whilst a database is active and online, is that the tool or utility has no concept of transactions, so all you get is a copy of what is in the O/S buffer or disk of the database at the time you make the backup.  However this "copy" of the database might be changing as new or active transactions are committed to the database while the copy is taking place. This is likely to produce at best, an inconsistent database, or at worst something that is corrupt and can't be used. Prior to Firebird 2.0 (other than using gbak) the only way you could do a backup or copy like this was to shut the database down, and make sure that no users were accessing the database before you invoked the third party backup tool or copy.

However it is possible to use Nbackup to achieve the functional equivalent of a gbak and use a third party backup tool.

The first thing you need to do is start a "freeze" on your database using the following syntax.

nbackup -U username -P password -L database.fdb

This will effectively lock the database, a flag is placed on the database header page, and it is set to "Locked" to let the engine know that all amended database pages that are written to the database are now being redirected to a delta file.

Changes are flushed from the internal (Firebird) database cache to the O/S cache when a transaction is committed, if forced writes are on then these changes are flushed directly to disk, the final task on commit is to mark the transaction as committed in the Transaction Inventory Page. Once the database is locked, all commits are written to the delta file rather than the database, thus ensuring that the database is kept in a consistent state.

Once the lock is applied, a simple gstat -h on the database wil show the "LOCKED" status as an optional database attribute. Once the -L command has done its work, a dela file will now be capable of receiving any committed changed database pages.

You can now use your an alternative backup tool whilst database users continue to work. When your backup tool has finished doing what it needs to do to take a copy of the database, you can "unfreeze" the database using the nbackup -N (unlock) command.

nbackup -U username -P password -N database.fdb

The unfreeze causes nabckup to merge the changed pages from the delta file back into the main database, when completed the delta file is removed and the database header is changed back to its normal state.
The backup you made of the database in its frozen state will still be in a "LOCKED" state, so if you need to restore it users will be unable to attach to it until you perform a "fixup". The fixup will reset the locked flag on the database header page back to normal, even though there isn't a delta file associated with the database.

Note: If you are going to use this capability, please make sure that you are using the latest version of Firebird, as a number of bugs in nbackup have been fixed since its original release.

No comments: