Determining recovery model

·        How much data can be lost? A day, one hour, a week, none...

1.           If your answer is less than a day or none you should consider the Full recovery model.

2.           If your answer is a more than a day then you could use the Simple recovery model (this is assuming you are doing full backups every day).

·        What kind of processing occurs? Transaction, batch load, reporting only, a combination...

1.           If your answer is transaction or a combination then you should consider the Full recovery model.

2.           If your answer is reporting only you could use the Simple recovery model (this is assuming this data is loaded from another source and can be easily recreated).

3.           If your answer is batch load you could use the Simple recovery model (this is assuming that you can reload the batch data without losing any data).

·        Is this data be easily recreated if there is a failure or it is not the only source of this data?

1.     If your answer is No than you should use the Full recovery model.

2.     If your answer is yes then you could use the Simple recovery model (assuming that it is not that hard to recreate the data).

·        Do you have very big batch loads that take considerable amount of time as well as load a lot of data?

1.     If your answer is No than you should use the Full recovery model.

2.     If your answer is Yes then you could use the Bulk-Logged recovery model.  Depending on how you load your data you can minimize the logging in the transaction log and therefore have smaller transaction log backups. The only drawback is that if there is a bulk-logged activity in a transaction log backup you are not able to do a point in time recovery for the transaction log backup file that has the bulk-logged operation.

Based on the recovery model you selected you should follow a plan such as the following:

·         Full or Bulk-Logged Recovery - Full backups and transaction log backups

·         Simple Recovery - Full backups

In addition to doing the above, you can also introduce Differential backups in between your Full backups.  This is helpful if you do a lot of transaction log backups during the day, so that when you need to restore you can just restore the full backup, the latest differential backup and any transaction log backups after the differential backup