Subject Backups of large database & super transactions
Author Jason Wharton
> The backup problem, howerver, is real. The disaster recovery
> strategy of InterBase is, well, a disaster. On the bright side,
> most database disaster recovery strategies are based on the
> concept of magnetic tape, a quaint medium more suited for display
> next to punched cards than modern computer systems.

Some time I suggested implementing the ability to create named freeze points
in the database. This essentially would be a super transaction. There are
various benefits of having super transactions and backups could take full
advantage of them as well.

Thus, a DBA would have the option to declare a freeze point in the database
and perform a backup for that freeze point. Then, for the next week on a
daily basis a backup could be performed only sending to the backup file that
which has changed since a certain freeze point. This would allow a much more
refined backup strategy.

A DBA could have a database up to a certain freeze point restored and
waiting in the case of a failure on the main machine. Then, in order to get
up to the latest production copy of the database (in case of failure) simply
apply the most recent delta backup to the restored "base" database and set
it online. I think that this is a very feasible approach to my problem and
would alleviate our need to store those image files in the file system.

Then, I could setup a backup strategy as follows. Once a week I would
rollback the previous week's named transaction and establish a new one in
conjunction with performing a new base total backup of the database. I could
optionally setup on an alternate volume a restored version of this to be
"on-deck" in case of a failure on the main database. On a daily basis I
would perform backups that would only pick up the new stuff since the last
"named" freeze point. Those would get set aside to be applied to the
restored base file that has been placed "on-deck".

I don't know if you would want to go to the extent of doing both daily
differential or daily incremental style backups but something along these
lines I think would be highly useful and make it so that InterBase would be
much more feasible to use for very large implementations. What I mean is, do
we want the ability to put together a delta from a named transaction on both
ends or just make it either a full backup or a backup to a certain named

If we allowed a backup to run from one freeze point to another freeze point
then it would be VERY trivial to maintain a distributed base of replicated
databases on a non-real-time manner. Each night the source database would
declare a new freeze point and run a backup for that days work only. Then,
this backup delta could be compressed and FTP'd to all of the remote
replicated locations and applied to all the databases. Thus, a huge
corporation could easily maintain a distributed database and keep it up to
date on a daily basis. U-Haul would have greatly benefited from a strategy
like this.

We had the 2nd largest point of sale app in the US at the time and I was
given the task of evaluating client/server databases for our 3.0 version to
be built on client/server technology. That's when I became acquainted with
InterBase. In spite of my recommendation to use InterBase I was overridden
because MS SQL Server had replication... <sigh>

> Like most hard problems, the place to start is to collect and
> prioritize requirements. When we have a handle on the problem,
> we can work on a solution.

I know what the problem is and I am working on a solution. Just seems that
people are more interested in shooting holes in my ideas that seeing how
they solve real problems... I'm out in the real world solving business
problems with InterBase on a daily basis... (not trying to imply anything
other than that)

Jason Wharton
CPS - Mesa AZ