The first problem, backing up open files, presents an obvious problem to any backup solution, because you can't very well back up files while they are being written to the hard drive. This would be like trying to sketch a picture of a fast moving train -- by the time you got your sketch started, you are waving as the caboose goes by. When you back up your computer's hard drive, what happens first is that the backup software takes a "picture" or "snapshot" of what the hard drive looks like (where all the files and folders are, and in what order they appear). This snapshot is done very quickly and the information is fed to the backup software as a list of files to be backed up. The backup software then starts feeding the backup files to your backup media (tape, drive, whatever). Any files that change once the process starts are skipped over during the backup because their information (size, modification date, etc.) is now different than the information contained in the snapshot. With a database that is being written to, the files are very likely to change during the backup process, and hence, very likely to be skipped.
Open and active database files can only be dealt with using a special plug-in for your general purpose backup software (such as Bakbone, Legato, Veritas, CommVault, Dantz, etc.), or must be handled directly by the backup application itself (such as MySQL, Borland SQL, and others which have backup routines built into them), or through complex drive mirroring technologies that while the database is active, write the database files to two drives, but for backup purposes, separate one of the drives in the mirror during the backup process so that the database is no longer actively writing to that drive while the drive is being backed up.
Most backup software packages have at least two methods for backing up database files. One method is to use a general purpose "open file backup plug-in" like that from St. Bernard. St. Bernard is licensed to about every backup software vendor's product that we can think of. When added as a plug-in to Veritas, Bakbone, Legato, etc., it uses special routines to capture the file information for open files, ensuring that most open files (including some databases) can be backed up.
Another method that most backup software companies use is to plug in their own methodology to gather the open file information in a way that can take a snapshot of the exact state the database was in when the backup process began and to ensure that it is that particular state which gets backed up, and that any changes to the database that are created during the backup process are somehow delayed until the backup routine has completed.
We delve into database backups, strategies, and tactics in great deal in our "backing up open files" chapter of The Backup Book.
For more information, attend Backup School.
Read Pierre Dorion's answer to this question.
This was first published in August 2004