Planning a Backup Strategy

You should have a solid backup plan in place before any application is ever moved from a development/test environment into a production environment. In addition, just as you would never put an untested application into production, you should never go into production without testing your backup strategy. It is always a good idea to include backup strategy planning as a key component of any project. In some cases, the backup strategy may even have a large impact on the design of the application. In planning the backup strategy for a given application, answer the following questions before launching the application into production:

• How often should the backups be done?
• What will be backed up at various times (for example, full database dumps versus transaction log dumps)?
• To what medium will the backups go (tape or disk)?
• Will the backups be done online (while users are working) or after-hours?
• Will the backups be done manually or by an automatic scheduling facility?
• If the backups are automated, how will it be verified that they actually occurred without error?
• How long will the backups be saved before reusing the medium is reused?
• Assuming failure, how long will it take to restore to the last backup? Is that an acceptable amount of downtime? If not, what is?
• Is there a mechanism in place to ensure that the backups are good, that they can be reapplied if necessary?
• Where will the backups be stored, and do the necessary people have access to them?
• Who is responsible for seeing that the backups are done and done correctly?
• If the system administrator is gone, is there someone else who knows the proper passwords and procedures to do backups and if necessary restore the backups?

This, of course, is not a complete list of all the questions you should think about in planning your backup strategy. You will need to answer these questions for every SQL Server environment, and there may be many other questions specific to your particular environment.