|
This question
will help gauge a candidate's understanding of a fundamental
concept of database backup strategies. This question is
appropriate for an entry-level database administrator.
You
have a 24x7 web-based database application, in production.
It is approximately 2 gigs in size. How often will you back
it up, and what type of backup will you use?
The candidate
must first show a basic understanding of a full, differential,
transaction log and file/filegroups backups.
A full backup
is a copy of all of the used database pages in the database.
From the full backup, you can restore a database, which
has been corrupted, from which data has been inadvertently
removed or incorrectly updated, or simply make a move to
another machine. A differential backup is a list of changes
to the database since the last full backup. A transaction
log backup is a backup of the changes made since the last
transaction log backup, differential database backup or
the last full database backup. A file/filegroup backup will
make a full backup of the selected file or filegroup allowing
large databases to be backed up in manageable pieces.
In general, if
a candidate says, "It depends", and then tells me what it
depends on; I accept the answer. Mitigating factors follow:
-
You must at least start with a full database backup, because
if you have only differential or transaction log backups,
you have nothing to apply changes against.
-
Differential and transaction log backups are far quicker;
in general, and as a result require less I/O against what
may be an already busy server.
-
The more frequently you take transaction log backups between
database dumps, the more transaction log backups you must
apply after you restore from the backup when disaster
strikes.
-
If you are in Full Recovery Mode you should take at least
some transaction log backups to keep the log cleared out.
because that is how the log gets cleaned out.
Depending on the
size of the database most shops take a database backup at
least once a day, and transaction log backups about once an
hour. If the databases are really small, the shop may simply
back up the databases and not take transaction log backups
(other than to clean them out). If the databases are really
large, database dumps may be weekly, rather than daily with
maybe a differential database backup done every day with transaction
log backup hourly.
File/filegroups
backups are rarely used do the complex nature of making sure
that the file/filegroup containing the indexes are also backed
up on the same schedule as the table containing the index
which might negate the reason for the additional file/filegroup.
It depends how a candidate defines really little or really
big, and this is based upon hardware (for example, does it
take one minute or one hour to backup a gig of data on this
hardware?).
Anybody who hits
all of these high points is at least a mid-level, likely a
senior.
About
the author
Jeff Garbus' background includes a B.S. Degree from Rensselaer
Polytechnic Institute, and work experience from PC's to Mainframes
and back again. Jeff has many years of client/server, Sybase,
and Microsoft SQL Server experience, with a special emphasis
on assisting clients in migrating from existing systems to
pilot and large-scale projects. He is very well known in the
industry, having spoken at user conferences and user groups
for many years, written articles and columns for many magazines
nationally and internationally, as well as having written
over a dozen books. Recently his focus has been on high-volume
database-based web applications. Jeff has been in consulting
for seventeen years, training for ten, and in the software
business for twenty. He has a demonstrated talent for staying
at the leading edge of technology, as well as transferring
his knowledge to others. Jeff@soaringeagle.biz
|
