Shout out to Kenneth Fisher (b|t) for hosting this month's #tsql2sday. This month is about backup and recovery and here is a snippet from the invite:

Backups are one of the most common things DBAs discuss, and they are at once one of the simplest and most complicated parts of our whole job. So let’s hear it for backup and recovery!

You can see the whole post on his blog.

SQL Server Recovery Models

Yeah, lets go with that. There are 3 different recovery models in SQL Server, Full, Simple, and Bulk Logged. Understanding the intricacies of each is important in determining which is best for you. In this post I plan to share what I feel are some of the important points of each.

Full Recovery Mode

Full recovery is the most granular recovery model and gives us the ability to restore our databases to any point in time. This is usually my default recovery mode.

• Point in time recovery.
• All transactions are written to the transaction log and persist there until the next log truncation. Log truncation happens after a transaction log backup.
• Transaction log backups required! Fail to do so and your transaction log drive will eventually run out of space.
• Required for Availability Groups, Database Mirroring and Log Shipping.

Simple Recovery Mode

One of the biggest misconceptions I have seen about recovery models has been surrounding simple mode. People think that by using simple mode you're getting a performance boost because transactions are no longer logged. This is absolutely not true. Transactions are still logged, they're just not persisted or backed up for recovery purposes.

• No point in time restores. We can only restore back to our full and differential backups.
• Simple mode does not mean no transaction log activity. Transactions are still logged in simple mode. They are just not persisted in the transaction log after they complete (committed or rolled back).
• You can still run out of transaction log space while in simple mode if you have a large enough transaction.
• Log backups are not required as log truncation happens after a checkpoint.

Bulk Recovery Mode

I've only seen bulk logged mode one time and it was for an ETL process that needed the extra boost in performance. It was also a rerunnable process so if we had to go back and recover the database we would have had to rerun the ETL process over again. Again, it's the only time I've seen it used and it was for a very specific use case.

• Bulk logged is typically a temporary recovery mode to be used only during bulk operations.
• Similar to full recovery mode, except for bulk operations. Bulk operations are minimally logged (bcp, BULK INSERT, and INSERT… SELECT).
• Point in time recovery is not supported.
• Same as full recovery, transaction log backups required! Fail to do so and your transaction log drive will eventually run out of space.

Summary

Those are some of what I feel are the more important points of each recovery model. It's not an all inclusive list by any means and you can find the full details on MSDN if you are so inclined.

Generally speaking, my recommendation is to always use the full recovery model unless you can explain exactly why you don't need to. Understanding the finer details goes a long way to making the correct decisions for your environment.