Backup Strategy

Backup strategy needs to be designed in such a manner that it causes minimum amount of downtime in case during a disaster recovery scenario if backups need to be restored. The backup strategy depends on the following:

1. Size of the database – This would determine how often a FULL BACKUP can be taken
2. Recovery Model – This would determine if transaction log/differential backups are possible
3. The importance of the database – This would determined how often transaction log backups need to be takenIf the database is in “simple” recovery model, then the only option that we would have is to take full backups.

If the database is in FULL/BULK-LOGGED recovery models, then we have the option of taking transaction log/differential backups.

Now the next question is how often to take a transaction log backup. Depending of the importance of the database, transaction log backups can be taken every 10-15 minutes or even every hour. This needs to be determined at your end. Furthermore, if the transaction log backups are being taken very frequently and a full backup is taken once a week, then it is advisable to take differential backups during the middle of the week. This would ensure that the process of restore becomes less cumbersome as after the full backup, then differential backup can be applied followed by the transactional log backups.

In case of full/bulk-logged recovery models, the following backup strategy would be ideal:

1. Full backup

a. Differential backup(s)

i. Transaction log backup(s)

b. Differential backup(s)

i. Transaction log backup(s)

2. Full backup (and repeat the above cycle)

Furthermore, for creating sound fail-over strategies for disaster recovery scenarios, the following can be considered:
1. Log shipping
2. Database Mirroring (For SQL 2005 SP1 and above)
3. Replication

Also, it is considered a good practice to restore a backup on a test server and a CHECKDB run on the restored database to make sure that the backup is in good shape. This becomes of utmost importance when it is not possible to run a CHECKDB before taking a backup. In SQL Server 2005, you have the option to perform a piece meal restore which would allow you to restore up to the page level in case you have database corruption.

The following articles could be helpful:
Overview of Restore and Recovery in SQL Server
http://msdn2.microsoft.com/en-us/library/ms191253.aspx
SQL Server 2005 provides the option of performing an online restore which is available for SQL Server 2005.

Please refer the following for more information:
Performing Online Restores
http://msdn2.microsoft.com/en-us/library/ms188671.aspx
Storage Top 10 Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
Using Recovery Models
http://msdn2.microsoft.com/en-us/library/aa173678(SQL.80).aspx