Developing a Backup and Restore Plan | Microsoft SQL Server 2000 Unleashed (2nd Edition)

The key to success in backup and recovery is a good backup plan. Take the time to carefully map out the methods you will use to back up, and your procedures for restore. You will be glad you did if disaster strikes. Factors that will affect your backup plan will include:

The size of the database(s)
The media to which you backup (Tape or disk? Local or network?)
Database availability (9 to 5 or 7/24?)
How data is loaded (Weekly data load or OLTP?)
Your recovery window (How long will a restore take?)

After you have analyzed your backup needs, you can then implement a backup and recovery plan that is tailored to your environment. For instance, in a data warehouse populated by a weekly bulk load, it would be pointless to do hourly transaction log backups . On the other hand, this might be necessary for an Online Transaction Processing (OLTP) system that processes hundreds of orders an hour . The different backup methods will be discussed in detail in a subsequent section of this chapter.

Document, document, document. I can't emphasize this enough. It does no good for you to understand the recovery drill, if you are on vacation in Borneo when the database decides to crash. If you walk out the door right now and get hit by a bus (or perhaps get offered a better job in California) could the new administrator step in and restore your databases if there was a problem? If the answer is no, you don't have sufficient documentation.

Now you have a plan and you have scheduled backups; you're all done, right? Wrong! The plan is not complete until it has been tested . Never assume you will be able to restore data. Initial and ongoing tests of your recoverability are crucial. Find the flaws in your plan before you are in an actual recovery situation. Many companies incorporate scheduled or even surprise data restore exercises. These exercises not only test the recovery plan, but also allow system personnel to get used to the recovery drill without the stress added by an actual database outage .