Planning to Back Up and Restore Databases

Backup and restore operations must be planned together. The procedure you implement will be driven mostly by your requirements for data availability. When you identify your data availability requirements, you can assess what backup procedures will give you the types of backups you must have to support the needed restore operations.

When planning backup and restore procedures:

• Think of the types of problems that might possibly affect your system.
• Plan a restore procedure for each problem.

  These procedures should take into account the importance of the data. For critical data, you need to recover to the point of failure. For other data, you may be able to rely on restoring the last database backup. If you must get the database back online in a short time interval, you may need to have more frequent backups available, or consider using a warm standby server.

• Plan backup procedures that support all of these restores.

  You may have to modify the restore requirements if testing shows that making the needed backups would take more time than is available in production.

• Run tests to verify that the procedures work within the resource and time limits available.

  The type of restore procedures chosen is influenced by the amount of time the data can be unavailable and by the number of transactions that can be tolerated to be lost. If there are strict service level agreements (SLA) or guidelines that specify how long data can be unavailable, the restore process must operate well within the time limits.

Running tests to verify that the procedures work is perhaps the most important step. It is difficult to plan a restore procedure in the tense times that accompany a server outage. Also, when a server fails, your recovery options are limited by the number and types of backups available. If you have not planned ahead and tested your procedures, the types of backups needed to do the restore options may not be available. Additionally, testing the restore procedures should verify that the data availability constraints are met (amount of time data can be unavailable), and the number of permissible lost transactions is not exceeded.

When analyzing backup procedures, remember that your backup is used to facilitate recovery, and a common recovery goal is to restore the database to a consistent state with no data loss, as quickly as possible. The time taken to recover a database and minimize the amount of lost data is largely dependent on the amount of time between backups. The optimal amount of time between backups is the time interval that results in the best balance of two opposing requirements:

• Minimizing the time needed to recover the database.
• Implementing backup procedures that can run within the time and resource constraints and prevent undue effects on production work.

It is recommended that your backup plan include:

• A tracking and management plan for storing and recycling backup sets.
• A schedule for overwriting backup media.
• A decision to use either a centralized or distributed backup approach in a multiserver environment.
• A set of conditions for using attended or unattended backups.
• A decision to store backup sets on or offsite.
• A standard and disaster recovery plan to restore your database to a production-ready state.

The SQL Server Database Maintenance Plan Wizard can help you create and automate a backup plan.

See Also

Database Maintenance Plan Wizard