Section 15.11. Documentation and Testing

15.11. Documentation and Testing

Restoring an RDBMS is the most complex procedure that you will ever do, and there is usually very little time in which to do it. The users want the database up now and don't really care that you've never done this before. You need to document and test your database backup procedures often to make sure that they work. The following guidelines may help:

• Set up a standalone machine that does not have any other databases on it. This machine doesn't have to be large or even dedicated to this purpose, but it would be good if you could reinstall the OS for a new test. This often can be done when you buy a new machine. Before you put it to work for real, do some test database restores on it.

• When you test your restores, test the worst-case scenario. Make sure that you know how to install the product onto a new system and that you know all of the files that you need to edit to make it work. This is why you should be doing this on a virgin operating system. That way, you will always have to edit /etc/system on a Unix, for example, instead of forgetting it without penalty because it's already been edited.

• You can set up a test instance and then back up and restore that. However, the best thing to do is to take a normal database backup from one of your production systems and try to restore it to the test system.

• Make the procedure detailed enough that anyone with good SA or DBA skills should be able to follow it. If possible, do not have the person who wrote the procedure perform the test. This is the perfect task to hire a consultant for. See if someone who knows what they are doing but doesn't know your environment can follow the procedure.

• Have all your DBAs participate, whether they've got time or not! The worst thing that can happen is that only one or two people know how to restore the databases. Then when a database crashes, one of them is sick and the other one just quit or is on vacation in the Bahamas. (I once saw a restore go on for hours because the DBA didn't know he had to press Enter! He called me saying, "Man, this thing is taking forever!") Make sure every DBA knows how to restore your databases!