Speeding up Backup/Restore on Large Databases

SYMPTOMS

The backup and restore process is taking a long time on my large database.

SOLUTION

You can use the backup and restore functions in the underlying database rather than using the Ascent software. This option is available for users of Ascent/MAS VB versions 7.00 onwards which use the InterBase or Firebird database engines.

Why Might this Interest me?

This will reduce the time needed to perform backup/restore functions on large databases. Backing up and restoring a database can help reduce its size and increase its speed; unused space in the database file is reclaimed and the data is de-fragmented.

If you have large databases you may find that the backup and restore process takes some time. The author of this article had just been visiting a company in the UK which had collected 4.4 GB of data with vb instruments over the last 2 years. The largest database was 480 MB. A backup or restore using the Ascent backup/restore facility took somewhere in the vicinity of 1 hour to perform on their computers. This same database took 10 minutes to backup using InterBase directly. After discarding some data and doing a backup/restore it was half the size and ran about 5 times faster. The discarded data was kept in another backup file in case it needed to be referred to at some time in the future.

In particular, if you have used the File>Discard Old Data or File>Data Thinning function to reduce the size of the database, you must do a backup then delete the original database, then restore from the backup file to actually see a reduction in database file size.

You do need to let the Ascent software use its own backup/restore facility when upgrading the database format (i.e. after upgrading) so that the software can translate the database from the old structure to the new structure.

The backup process in the Ascent program is accessed using the File>Backup Now and File>Restore functions on the main menu. There are also facilities for automatically scheduling backups (File>Backup options).

Why is the Backup/Restore Process Slower when Using the Ascent Program?

To perform a backup, the software must iterate through all of the data in the database, asking the database engine for every item individually so that it can write the data to a backup file. Each item of data must be translated from the database format to the SQL format that the Ascent software uses to communicate with the database. The Ascent program then translates the data from SQL format to a binary format to write to the backup file. The InterBase/Firebird backup facilities have direct access to the database itself and can work through the database file, generating the backup file without all these extra translation stages.

So how do I Backup/Restore Using InterBase or Firebird?

First we will set up some file associations so that the database backup/restore process can be accessed easily.

Associating .gdb Files with a 'Backup' Command

First we are going to associate your database files with a 'Backup' command so that you can right-click on the files in Windows Explorer and select Backup to create an InterBase or Firebird backup file. (The names of menus and buttons may vary on different operating systems - these instructions refer to Windows 2000).

1. Open Windows Explorer (i.e. click on My Computer on the desktop).
2. Select Tools>Folder Options from the menu.
3. Select the File Types page.
4. Click New.
5. Enter GDB in the Create New Extension dialog and click OK.
   • If you are asked whether you wish to replace the existing GDB association answer No, click Cancel, click on the Extension column header to sort the list alphabetically and then scroll through the list of Extensions until you find the existing GDB entry, select it by clicking on it once with the left mouse button then continue.
6. Click the Advanced button.
7. Enter a description in the top box (it may contain something like FT000001) - we suggest InterBase (or Firebird) Database File.
8. Click the New button to add a new Action.
9. Enter Backup in the Action box.
10. Click the Browse button.
11. Find the gbak.exe file in your database's \bin folder. By default this is in:

    (for InterBase)

    C:\Program Files\Borland\InterBase\bin\gbak.exe

    OR

    (for Firebird)

    C:\Program Files\Firebird\Firebird_1_5\bin\gbak.exe

12. Click Open.

    If you are using the InterBase database engine and can't find gbak.exe in the InterBase \bin folder this means that the Command Line Tools for InterBase have not been installed - this was default behaviour for some Ascent/MAS VB installation CDs. To install the Command Line Tools insert your Ascent/MAS VB installation CD and instead of using the Menu program browse to the CD using Windows Explorer and run the IBWin32Setup.exe program in the InterBase folder. Follow the prompts through to the Select Components screen and ensure that Server, Client and Command Line Tools are checked then keep following the prompts to re-install InterBase.

13. In the Application used to perform this action box go to the end of the line (click in it and press the End key) press the SPACE bar once to add a space then add the following to the full gbak.exe filename:

    b -user SYSDBA -password masterkey "%1" Database_Backup.gbak

    If you are browsing this document electronically you can copy and paste the above line into the edit box.

    When you have done this the box should contain both the complete path to the gbak.exe file and the command line from above. For InterBase this will be:

    C:\Program Files\Borland\InterBase\bin\gbak.exe" -b -user SYSDBA -password masterkey "%1" Database_Backup.gbak

14. Click OK twice to return to the Folder Options window; leave this window open.

Associating .gbak Files with a 'Restore' Command

Now we are going to create another file association for the backup files that will allow us to restore them by right clicking and selecting Restore.

1. In the Folder Options window (you should still be there) click the New button again.
2. Enter 'GBAK' in the Create New Extension dialog and click OK.
   • If you are asked whether you wish to replace the existing GBAK association answer No, click Cancel then scroll through the list of extensions until you find the existing GBAK entry. Select it by clicking on it once with the left mouse button then continue.
3. Click the Advanced button.
4. Enter a description in the top box (it may contain something like FT000002) we suggest InterBase (or Firebird) Database Backup.
5. Click the New button to add a new Action.
6. Enter Restore in the Action box.
7. Click Browse.
8. Find the gbak.exe file in the database \bin folder. By default this is:

   (for InterBase)

   C:\Program Files\Borland\InterBase\bin\gbak.exe

   OR

   (for Firebird)

   C:\Program Files\Firebird\Firebird_1_5\bin\gbak.exe

9. Click Open.
10. In the Application used to perform this action box go to the end of the line (click in it and press the End key) press the SPACE bar once to add a space then add the following to the full gbak.exe filename:

    -c -user SYSDBA -password masterkey -p 2048 -use_all_space "%1" Database_Restored.gdb

    If you are browsing this document electronically you can copy and paste the above line into the edit box.

    When you have done this the box should contain both the complete path to the gbak.exe file and the command line from above. For InterBase this will be:

    C:\Program Files\Borland\InterBase\bin\gbak.exe" -c -user SYSDBA -password masterkey -p 2048 - use_all_space "%1" Database_Restored.gdb

11. Click OK twice to return to the Folder Options window.
12. Click Close.

How do I use it?

To backup and restore a database perform the following steps:

1. Shut down all other applications on the computer (especially Ascent!).
2. Find the database file that you wish to backup using Windows Explorer - the default locations are:

Windows 95, 98, NT, 2000:

C:\Program Files\Commtest Instruments\Ascent\Ascent_Database.gdb

Windows XP

C:\Documents and Settings\All Users\Documents\Ascent_Database.gdb

Windows ME:

C:\Documents and Settings\[username]\MyDocuments\Ascent_Database.gdb

To Backup the Database

• Right-click on the .gdb file and select Backup.

  This will create a file called Database_Backup.gbak in the same folder. If such a file already exists the backup will not work. You must delete or rename any existing file in the same folder called Database_Backup.gbak before using the Backup function.

To Restore the Database

• Right-click on the .gbak file and select Restore.

  This will create a new database file called Database_Restored.gdb in the same folder. Wait until the black DOS window has finished processing before using the database file. If a file called Database_Restored.gdb already exists in the same folder you must delete or rename it before performing the Restore process. Once the operation is complete you can move or rename the Database_Restored.gdb file. Once you have verified that it is working well with the Ascent program you can replace the original .gdb with this one.

Troubleshooting

The database file is actually slightly bigger now - what's going on?

The option -pagesize 2048 that is specified in the Restore function above means that larger blocks of database file space will be allocated for storing the database. The default is 1024 bytes. 2048 is recommended so that a 400 line spectrum will fit on one database page - this improves Ascent performance. Typically a spectrum will require [number of lines] x 4 bytes to store in the database. If you can afford to have larger database files and you use 1600 or 3200 line spectra often, feel free to change the -pagesize option to 4096 or even 8192 (these are the only valid options). If you want to make the database as small as possible then change the pagesize value to 1024 (or leave the option out altogether).

It doesn't work at all. The black DOS window appears very briefly then disappears without apparently doing anything

Most often this happens when there is an existing Database_Restored.gdb or Database_Backup.gbak file in the same folder. It can also happen if the database server is not available; if you select Start>Programs>InterBase>InterBase Server Manager then you should see a green message indicating that the server is 'running'. If not you can start it using the Start button. If you are still having problems please contact help for assistance.

Why do we specify '-use_all_space' on the Restore command line?

A condition monitoring database is typically going to increase size, not decrease. We do not perform many 'edits' to existing data in the database; therefore, if we do not specify - use_all_space the database will reserve 20% of the space within each block for revisions of existing data.

What other ways are there of making the software run faster?

Within the Ascent program there is a command File>Rebuild Database that will rebuild the indexes in the database, ensuring that you get maximum performance. Also make sure that you have plenty of disk space available and no other programs running in the background. Keep up to date with the latest version of Ascent software either from your distributor or the Commtest website.

FAQ ID: 11607 Last Reviewed: 20 December 2005

Back to Frequently Asked Questions