Monday, October 8, 2007

SQL Server 2005 backup plan

The business hour is 09:00 to 17:00. You can have only one differential backup during the business hours. You should perform the full backup during the business hours. Business requirements allow for up to one hour of data loss. No more than six backups should be required for any recovery.

Solution:

To cover business hour, you create the following scheme:
Full backup: 8:00, 16:00
Differential backup: 13:00
Transactional Log backup: 9, 10,11,12 and 14, 15, 16, 17

For Non-business hours, you could create Full and Differential and Log backups without restrictions.