Backing Up Your Database: The Good, the Bad,
and the Ugly
I've the pleasure of working with a variety of
people during the course of my varied careers. Some
folks seem to be a constant state of anxiety. They
know that stuff is going to go wrong and they are sure
that the hassle factor will be immense.
Then there are those folks who don't worry about
problems at all. They too know that things will go
wrong, but they don't worry about it - not because they
don't care or they refuse to let it get to them. Rather,
they don't worry because they have a contingency
plan. And, if the contingency plan doesn't work, they
just go to the next step in their chain of contingency
plans. They can relax because they know they have a
method for dealing with things when they go wrong.
Ask yourself this question: If your database became
corrupt right now what would you do? Are you one of
those folks who would say "This is horrible!" or are
you one of the folks who would say, "Okay time to go to
the back up"?
No doubt, you want to be in the second group, not the
first. This month we'll talk about the good, the bad,
and the ugly of database backups.
|No Back Up at All: The Ugly
There are some folks who don't back up their
database files -- no backup whatsoever. The reason
they don't is because they've never lost a file due to
corruption, or turned on their computer and have the
hard drive not spin. In other words, they haven't yet
been burned. If this sounds like you, stop what you're
doing, and back up your database now!
You do not want to be in the position of losing all the
work that you have stored in your database. Every
single disc in every single computer will fail. It's not a
question of if; it's a question of when.
|Backing Up a Database: The Bad
There are three main problems that most people run
into when backing up their databases.
- They don't have a regular backup schedule. The
problem with this is obvious. If you haven't backed up
for two weeks, and your database becomes corrupt,
you've lost two weeks worth of work. You want to
backup your database on a daily basis at
The primary reason people don't back up on a regular
basis is because it is a manual process. Any process
that takes human intervention is subject to our
imperfections. As a result, although we may mean to
do a daily backup, the hubble-bubble of life gets in the
way and it just doesn't happen.
- The second problem is that, although they
might be backing up their databases on a regular
basis, they never test them. In this situation, there
may be a flaw with the backup procedure. Or maybe
you think you're backing up your database, but you're
You don't want to discover this flaw on the day you
need your backup. The only way that you can know
that your procedures are working properly is to actually
test the results. It's a fairly simple process. You take
your existing database, rename it something
innocuous, install your backup database, and see if it
works properly. If it does, you're all set. If not, you take
a very close look at your backup procedures to find out
where it's breaking down.
- The third problem is much more subtle.
There is an effective procedure, but the backup file
has a name and location that might cause it to be
accidentally used in place of the live file. If you work on
a machine that is not part of a network, and you're the
only person who uses the database, this is probably
not a very big issue. However, if you work on the
network with a database that is shared among
people, then it's important to have a backup that can't
be accidentally mistaken for the live file.
My recommendation is that when you're doing a
backup, have a clear naming convention that makes it
difficult to mistake the production database with the
|Backing Up a Database: The Good
The ideal way to back up a database is to take it out of
your hands. Some systems create a backup every
single day with no human intervention required. Your
database gets backed up automatically. There are
many software packages out there that can do this for
you. I use a program called Second Copy. Every day,
it backs up all my files with no thought or action from
I periodically test to make sure that things are working
the way they should. Because of the nature of my
work, I actually have redundant copies, so that if
something does go wrong with the media in my
backup machine, I just go to a copy of the backup,
which I keep on a different machine.
I don't typically work on a network shared with others,
so I don't worry about a strict naming convention. But
when I work with my customers, I help them set up a
system that makes it difficult to mistake a backup
database with the live file. In some cases, I help them
by making it impossible to pick up a backup file
|Conclusion: How to Sleep Soundly
Yes, I know that life is more than just databases.
However, if you back up your databases on a regular
basis, and test the backups periodically, you won't
have to worry about what happens when good data
goes bad. You just go to your backup plan.
Tip of the Month: Offsite Backups - Wear Both a Belt and Suspenders
If your database is mission critical, to either your life or
your business, then you want to take an extra step to
be comfortable that you can recover from corruption.
Consider the case where, God forbid, a fire breaks out
in your house/office and your computer and everything
else is damaged beyond recovery. How would you be
able to cope with this as far as a database is
The solution is fairly straightforward: Off-site data
backup. There are two approaches to this. The first is
a physical backup that is stored someplace other than
your home or business.
If you're a business, have someone take home a
backup on a regular basis. If it's personal information,
stash a backup copy with a friend, neighbor, or family
member. Both of those work just fine.
The other potential way to handle the problem is to
backup to the cloud. If you Google data backup,
you'll find a host of services that will do this.
A couple of things to note: Make sure that the company
that you are backing up to is reliable. It will do you no
good to have a backup in the cloud with a company
that no longer exists. If a company has any potential
of going out of business, steer clear. The same is true
of the relatively new companies. Who knows how
good a job they do?
Whichever company you choose, it would be wise to
make an assessment of its financial health. If you
don't have the tools to do that, ask a friend to take a
look. Since you're backing up your data, you want to
make sure that you can get it back when you need it.
And if you're backing up to the cloud, its vitally
important that you periodically test the backup to make
sure that things are working as planned.