Databases Reference
In-Depth Information
Answering the “When” of Backups
Answering the “when” is a little more difficult. The easy answer is that you need
to back up the data after it changes. Configurations that constantly back up the
production databases throughout the day are possible, but are expensive and
often difficult to implement.
The important question here is, “How much data can you afford to reenter
after a failure?” The answer tells you the maximum interval between backups.
For some companies, once a day is often enough. For others, you might need
to run backups at a much shorter interval, such as once an hour. The potential
impact on other operations can influence your decision, possibly forcing you to
run backups during off-peak times. Scheduling can be a definite problem if your
DBMS makes the database unavailable during backups. However, most current
management systems, including SQL Server, allow user access and support most
database operations while a backup is running.
Consider the earlier example of a stock brokerage. Data changes rapidly in
this type of environment. If data is lost, it can be difficult (if not impossible) to
accurately repost or re-create the data. In this environment you need to back up
the data as often as possible, but with minimal impact to database operations.
One way of doing this is to run backups that back up large amounts of data
infrequently, probably no more than once a week. You would then supplement
this by backing up changes only. These could run as often as every minute, back-
ing up just the changes that were committed during that period.
Backup frequency, as well as the amount of overhead you can allow for back-
ups, will also help determine the “How” part of the question.
Answering the “How” of Backups
With that lead-in, let's go ahead and talk about “how” before we talk about “where.”
Most database systems support multiple backup options. All systems include some
method for a
full backup
of all database data. This can be through a special backup
utility, or in some cases, can be done by backing up the database and object files
through the operating system. Some systems require you to back up the entire data-
base when you run a full backup, but others let you back up the full content of sep-
arate database files individually. It is strongly recommended that you run a periodic
full backup as a base for recovery with most companies running one at least once
a week. Most manufacturers recommend an interval of no longer than once a month.
Most DBMSs provide an option for backing up changed data only between
full backups so that the backups run faster. These can be run as incremental or
differential backups. An
incremental backup
backs up only those changes made
since the last backup. This means that the backups take very little time to run,
but when recovering data, you must restore each of the incremental backups in
the order in which they were made.
Differential backups
back up all changes
since the last base or full backup. That means that each successive differential
