Analyzing Backup Requirements

When developing your backup plan, answer the following questions. Your answers should provide guidance to the best Microsoft® SQL Server™ backup methods, backup device, and backup media to use in your environment.

• How large is each database?
• How often does the data change in the database?
• Are some tables modified more often than others?
• If data is lost, how vital is your database to the daily operation of the enterprise?
• How important is it to never lose a change?
• How easy would it be to re-create lost data?
• What are your critical database production periods?
• When does the database experience heavy use, resulting in frequent inserts and updates?
• Is your database used heavily during payroll processing, one time quarterly, on specific days, or during certain hours of the day?
• Will users access the database during backup operations?
• How long can you go between transaction log truncations?
• Do you need to keep a rotating series of backups (for example, backup, backup+1, backup+2)?

  This allows you to restore some data if a backup is lost, or lets you restore further back in time if a problem is not detected for some time after it occurs.

• Is your SQL Server in a cluster?
• Is your SQL Server system in a multiserver environment with centralized administration?

The time it takes to run backup and restore operations depends partly on the speed of the physical devices (tape or disk) used to store the backups. Backup device vendors can supply performance information to help calculate the approximate time required to back up an entire database. However, the best method for determining the time it takes to run backup and restore operations is to perform realistic tests on the production, or a duplicate, system. For example:

• Create a database backup of the real system (or a copy of the real system) and time the operation.
• Create a transaction log backup, after typical transactions that occur in production have been performed, and time the operation.
• Restore a database backup and time the operation.
• Apply the transaction log backup and time the operation.

See Also

Disk, Tape, and Named Pipe Devices