Having decided on how often to
backup and where to backup, you now must choose a location
to store your tapes. Deciding on a location is going to
depend greatly on your situation, but there are some
general rules you should keep in mind. First of all, the
location, either on or off-site, should be secure! If
someone has access to your tapes...you might as well give
them access to your server. Second, you need to find a
balance between keeping the most recent tapes nearby
(in case you need to restore a database) vs. the need to
store tapes off-site (in case of a disaster).
that we talked about earlier is storing a file backup on a
second computer from your SQL Server. By doing this, not
only can you recover from a crash faster, but it also
allows you to store tapes off site without having to worry
about going to get them to restore a database. You can also
accomplish this same effect, minus the faster restore time,
by making two sets of tape backups; one off-site for
storage and one on-site for quick access. You may even
consider making a third copy of your monthly backup for
storage in a third off-site location. Also keep in mind
that you don't have to keep all your tapes off-site.
Depending on your needs, you may find that keeping the
weekly backups, or even the monthly backups, off-site is
move on, there is one last issue with backups that I would
like to cover -- choosing someone to swap tapes. Once you
have your plan in place you should designate a single person responsible for checking that backups took place and
that tapes are swapped out as needed. It is important to have one
person do this because when multiple people share the
responsibility you end up with: "I thought you swapped the
tapes last night?!?" When a single person is responsible
for backups it becomes a routine for them. Now, if that one
person is unable to swap tapes (ex: they get sick) it is
their responsibility to find another person to swap tapes
for them. Although another person may do the job of
swapping tapes now and then, you still have the
accountability of the single person who normally does the
tape swapping, not a group of people. If the job is too big
for one person, consider giving the responsibility for half
the servers to one person and half to another person (or
however you would like to split them up). Additionally, if
you can't have the same person swapping tapes every day
(ex: you take backups on the weekend), make it clear who is
responsible for what days and keep the days the same from
week to week.
having decided how often to backup, where to backup, and
where to store our backups...what's left in a disaster
recovery plan? Well quite a lot, but most of it is highly
dependent on your environment. The first step is to
document, document, and document. Get a folder and
dedicate it to your disaster recovery plan. Here are some
things that you should include in your plan:
- Server Hardware
- Network Layout
- Server Software Configurations
- Database File Layout (i.e. log files and data files)
- Label your tapes and include a backup and rotation
step is to start thinking about, and write down, what
should happen if a failure occurs. Keep in mind when you
start to write out the plan, you should assume that you are
not on-site and are unable to come to the rescue. You
should also assume that the person restoring the server
does have technical knowledge about SQL Server, but knows
nothing about your particular setup. Think about things
- Who should be
contacted if something goes wrong?
- Where are the backups stored?
- Where are the software and driver disks stored?
- Are there any tech support numbers available?
- If new hardware is required, what should be done?
- Is there any other information that may be useful?
Once you have completed
documenting what should happen if a disaster occurs, there
is one final step that you must complete...testing your
plan. Having a plan is not enough, you have to test to see
if your plan has all the necessary information, if your
backups work correctly, and if everyone knows what to do.
In order to do this you should setup a fake disaster. Now,
don't go lighting your servers on fire (we all know how
tempting that can be sometimes!), but use some extra
hardware to test your plan. Don't worry about getting
exactly the same setup (hardware wise), you will need just
enough to run the services and any client applications.
When testing, you should follow your disaster recovery plan
and see if all the information is available in the plan. If
you left anything out, or something was wrong, now is the
time to make corrections and additions. By using the test
hardware you not only get a feel for what information needs
to be in your plan, but you are also able to test your
backups by restoring the server from tape.
Next week we will look at how to restore a database from
backup so we can continue to test our disaster recovery
plan. We should also be finished with backup/restoring
databases next week and then we can move on to some more
:) As always, if
you have any technical questions please post them on the SQL message board.
Please send any non-technical questions, comments, and feedback to
my email. I hope you are finding this
series to be a useful one, and I'm looking forward to your feedback.
See All Articles by Columnist Michael Aubert