Databases Reference
In-Depth Information
backup takes longer to run, but after restoring the base backup, you only need
to restore from the most recent differential backup.
DBMSs that use a transaction log for tracking changes to the backup support
transaction log backups.
When you back up the transaction log, you back up the
inactive portion
of the log only. That means that you back up only those transac-
tions that have been completed, either committed or rolled back. The inactive por-
tion is then deleted, freeing up the space and helping to keep the transaction log
a manageable size. Because of their small size and because they usually run quickly
and with minimal interference to user operations, transaction log backups are typ-
ically used to support backup plans that require frequent (such as hourly) back-
ups. During recovery, you first restore the base data and any change (incremental
and differential) backups. Then, you restore all transaction log backups, starting at
that point, in the order in which they were taken.
Usually, if your DBMS supports multiple backup options, it also lets you mix
and match those options to meet your needs. Let's say that General Hardware
Supply, a fictitious company, determines that they can run backups after hours
only. This means running backups between 9:00 p.m. and 6:00 a.m. Monday
through Saturday and any time on Sunday. Because there are other automated
operations occurring at night, they want the backups run during the week to
run as quickly as possible. They also want a full backup to run at least once a
week. You could meet this requirement by configuring the backups to run auto-
matically, scheduling a full backup to run on Sunday and incremental backups
to run each of the remaining evenings.
Looking back at the brokerage example again, you would run infrequent full
backups, no more than once a week and possibly once a month or less. If you
were to cut full backups back to once a month, you would probably want to
run differential or incremental backups at least once a week, and more often if
your schedule and server activity allows. For the very frequent backups, such as
those that run as often as every minute, you would probably use transaction log
backups. Where this solution becomes a problem is in recovery after a failure.
You could find yourself having to recover from the full backup, the most recent
differential backup, and potentially thousands of transaction log backups. This
is one situation where you might strongly consider the added expensive of a
duplicate server as an appropriate investment.
Answering the “Where” of Backups
Now, to our last question. “Where” refers to the backup destination, with most
backups running to one of two places: to a shared network drive or to remov-
able media. Let's look at each of these options.
Figure 11-17 illustrates one option for backing up to a shared network drive.
The backup runs on the database server and is written to a shared folder on another
computer, typically a secure file server dedicated to this purpose somewhere on the
network.
