Biblioteca

12 Common Mistakes while Backing Up Databases

This article was initially intended for Firebird DBMS developers and administrators, but contacts with administrators of other databases made it clear that most mistakes are common among them as well and literally everyone stumbles over nearly the same stones. If you can add something to this list (even something specific to a particular DBMS), contact us via our e-mail [email protected].

1. Deleting the previous backup copy before a new backup copy is created

This mistake is most common among newbies who do not realize that the main purpose of a database backup copy is not just to create a database copy, but to make the downtime of an information system (an important part of which is the database) as short as possible.

As a result, the system remains unprotected from the moment the latest backup copy is deleted and to the moment the new one is created because the database does not have a single backup copy during this period. Since creating a backup copy may take quite a while, it is the perfect time for Murphy's law to take effect. This approach works especially well when it is combined with Issue 7 (see below).

Recommendation: do not delete the previous backup copy before the new one is created! (and do not make a new backup copy into an existing file).

Recommendation for Firebird: The FBDataGuard tool included in HQbird (an advanced distribution package of Firebird) deletes the earliest backup copy in the history only after a new one is created.

2. Overwriting an existing database while restoring it from a backup copy

This mistake is less common though the results may be much worse. If the backup copy has not been verified and turns out to be corrupted (see Issue 6), you will have neither the previous copy of the database nor a valid backup copy.

A mess like this usually happens on a Friday evening when things get hectic and when directions from the management get kind of contradictory. A bit of bad luck and a languorous weekend in the server room is there for you.

Firebird has kind of protection against this mistake – it will not be possible to restore a database from a backup copy with the help of the gbak utility if its default –create switch is on and if the specified file name points to an existing database. Unfortunately, there is a way to get around this protection: the –rep switch still allows you to overwrite the existing file.

Recommendation: never overwrite the file of a working database without a written directive from your management.

Recommendation for Firebird: Use FBDataGuard because it never overwrites the database file.