InterBase 7.5 backup/restore timing problem

While in Denmark yesterday and earlier today giving an InterBase training we noticed a backup/restore issue with gbak and gfix combination where there are users connected to the database, and you want first to shut the database down (as to remove other users) and then backup and immediately restore the database.

The problem here is the word immediately. If you want to improve the internal database file structure and make the .GDB/.IB file smaller (shrink it), you need to do a backup and restore. However, if you try to do that with gbak from the command-line, you might run into the following error while restoring:

gbak: ERROR: could not drop database C:\Program Files\Borland\InterBase\examples\database\employee.gdb (database might be in use)
gbak: Exiting before completion due to errors.

We tested this with stock InterBase 7.5 server on Windows 2003 Server and Windows XP workstation. Here’s a .CMD batch file script that is able to reproduce the above error:

@echo off
cd "C:\Program Files\Borland\InterBase\bin"

echo Shutting down...
gfix -shut -force 1 -user sysdba -password masterkey "C:\Program Files\Borland\InterBase\examples\database\employee.gdb"

echo Backing up...
gbak -b -user sysdba -password masterkey "C:\Program Files\Borland\InterBase\examples\database\employee.gdb" c:\data\employee.gbak

echo Restoring up...
gbak -r -user sysdba -password masterkey c:\data\employee.gbak "C:\Program Files\Borland\InterBase\examples\database\employee.gdb"

cd\
echo Done!

To workaround this issue, you can introduce a simple wait between the backup and the restore command. Usually, a delay in the range of 5 to 10 seconds is enough. You can wait with a following VBScript code (the sleep time is in milliseconds):

WScript.Sleep 5000

You would then save this code line to a file, say, wait.vbs, and then modify the above script file to include a call to this script file:

@echo off
cd "C:\Program Files\Borland\InterBase\bin"

echo Shutting down...
gfix -shut -force 1 -user sysdba -password masterkey "C:\Program Files\Borland\InterBase\examples\database\employee.gdb"

echo Backing up...
gbak -b -user sysdba -password masterkey "C:\Program Files\Borland\InterBase\examples\database\employee.gdb" c:\data\employee.gbak

echo Waiting five seconds after backup...
cscript wait.vbs //nologo

echo Restoring up...
gbak -r -user sysdba -password masterkey c:\data\employee.gbak "C:\Program Files\Borland\InterBase\examples\database\employee.gdb"

cd\
echo Done!

With this batch code, you should be able to always take your backups and then restore immediately. I’m not sure if this problem would affect InterBase 7.5 SP1 or InterBase 2007 (8.0).