FRONTBASE TECHNICAL NOTES
FRONTBASE TECHNICAL NOTES

Safeguard Your Database

By: Geert Clemmensen,December 09, 2000

We have recently seen that Mac OS X Server, when swapping under heavy load, can cause unfortunate problems in the file system. These file system problems can impact the FrontBase™ database files.

This document contains the following sections:

Overview
Targeting Q1 2001

Overview

As the measures you can take to safeguard your databases are identical on all platforms and because it makes sense to protect your data in general, we have produced this write-up of the possible strategies you can pursue.

This section contains the following:

Copy the Database Files
Backup of A Live Database
Restoring a Database
Export Into Flat-Files
Replication
Log SQL Statements

Copy the Database Files

This strategy is very simple, basic, and certainly better than nothing.

FrontBase stores its database files in <install-path>/FrontBase/Databases. You can backup the contents of this directory by using your preferred backup utility. The tar utility is available on all Linux/Unix systems, it needs to be executed from the command line:

   tar cvf <destination file> <install path>/FrontBase/Databases

Example for Mac OS X Server (the backup is created in current directory):

   tar cvf fbbackup /Local/Library/FrontBase/Databases

NOTE: You need to stop all FrontBase servers before making the backup.

Backup of A Live Database

With FrontBase you can create a backup of a database while it is running. The backup is non-obtrusive and the database can continue to be updated while the backup progresses.

You need to connect to a database as the user _SYSTEM and issue the following SQL statement:

   WRITE DATA [WITH TRANSACTION DATA];

The optional WITH TRANSACTION DATA will, if specified, make sure that subsequent modifications to the database are logged as transaction logs. This will later allow you to restore a database to include all modifications that were concluded (COMMITted) after the actual backup.

On most systems, the task of starting the backup can be automated. On Linux/Unix systems the cron utility and the sql92 command line tool can, together, automate the process.

TIP: As an extra safeguard measure, prior to doing a restore from a backup, we strongly recommend you make a copy of the "old" database files in <install path>/FrontBase/Databases first.

Restoring a Database

To restore a database from a backup, you need to do so from a command line (logged in as root):

   <install path>/FrontBase/bin/FrontBase -create -restore [-rollforward] <database name>

Example for Mac OS X Server:

   /Local/Library/FrontBase/bin/FrontBase -create -restore -rollforward <database name>

The optional -rollforward option will roll forward the database to include all modifications logged as transaction logs.

Export Into Flat-Files

FrontBase can export an entire database, schema definitions and content, into flat files from which a database later can be rebuild. Connect to the database as the user _SYSTEM and issue the following SQL statement:

   WRITE ALL OUTPUT('<path to directory>' [, 'YES']);

If the optional 'YES' isn't given, only the schema definitions are exported, otherwise all content (rows in tables) are exported as well.

The above statement will, if it doesn't exist, create the specified directory and export the schema and content into that directory. The schema definition is exported into a file named schema.sql and for each table, the rows are exported into a discrete file.

On most systems, the task of starting the export can be automated. On Linux/Unix systems the cron utility and the sql92 command line tool can, together, automate the process.

To rebuild a database from an export requires only a few steps:

Replication

The replication approach will be fully described in a future version of this document, but replication is a way to have a master database, which receives all updates etc., and N mirrored databases that are read-only copies of the master.

A "suspender and belt" approach could be to have two mirrored databases, one which handles also the backups and one which logs SQL statements. This will assure a very a high degree of safety, with multiple machines involved (could easily be multi-platform) and multiple safe guard techniques.

Log SQL Statements

FrontBase can log each SQL statement it receives into a log file. The log file is augmented with information that details connection state etc., and will later on allow for a precise replay of the actions that led to a specific state of the database.

To log SQL statements requires a few steps:

This will create the file systems, the task of starting the export can be automated. On Linux/Unix systems the cron utility and the sql92 command line tool can, together, automate the process.

This will create a file, containing the SQL statements, named:

   <install path>/FrontBase/Databases/<database name>.fb.sql.

Please note that this file will contain all SQL statements from all connections.

Targeting Q1 2001

Frontbase will release a tool that will allow you to replay the contents of the SQL log file. In addition to being able to recreate a database, this tool will allow you to stress test the hardware platform as it will execute the statements found in the SQL log file as fast as possible rather than simulate the reflected time span.


Frontbase copyright ©2000 by Frontbase Inc. and its licensors. All rights reserved.

Frontbase and the Frontbase logo are registered trademarks of Frontbase Inc.

All other trademarks and registered trademarks are the property of their respective owners.