SQL Server 2000 Administration in 15 Minutes a Week: Creating a Disaster Recovery Plan (Part 2) - Page 2

June 28, 2002

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).

One approach 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 adequate.

Before I 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.

So, 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 Specifications
- Network Layout
- Server Software Configurations
- Database File Layout (i.e. log files and data files)
- Label your tapes and include a backup and rotation description

The next 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 like:

- 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 interesting stuff! :) 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.

Mike
maubert@databasejournal.com
www.2000trainers.com

» See All Articles by Columnist Michael Aubert