Microsoft SQL server provides three recovery models; full, simple, and bulk-logged. Each have their benefits. The details of the three recovery models is outlined below.
You will need to choose the best option for your scenario. For a production database that you need to be able to recover from, the best option will generally be the full recover model.
Full recovery model:
The full recovery model maintains the entire transaction log history up until the point where the database log file is backed up. The major benefit of the full recovery model, is that you have the ability to restore the database to a specific point in time. For example, you can restore back to 4:23pm, even if a full backup was not taken at that time.
If using the full recovery model, you will need to make sure the transaction logs are backed up reguarly as to prevent the log growing too big and filling up all free space on the server.
Simple recovery model:
The simple recovery model still has a transaction log file, however it will only store transactions for a short period of time, and cant be used to restore to a specific point in time. With the simple recovery model, you can only restore to a database backup file, at the time the backup was performed.
Bulk-Logged recovery model:
The bulk-logged recovery model is similar to the full recovery model, but it handles bulk data (for example, a data import) in a different way. When dealing with bulk data, instead of keeping track of every transaction in the log file, it will simply log the end result after the bulk transactions have occured. This is known as ‘minimal logging’. This helps reduce the impact that logging may have on the performance of the server.
With this model, you wont be able to restore to a specific point in time. You will only be able to restore back to the end of the transaction log backup.
Ideally, you would use this model on an as needed basis. It provides better protection in a disaster over the simple recovery model, however it lacks behind the full recovery model by not being able to recover to a specific point in time.
