Tuesday, January 14, 2014

What is your database Recovery Model?

The SQL Server database recovery model is a property that dictates how transactions are logged, whether the database transaction log requires backups (or even allows them), as well as what types of RESTORE operations are possible.  We have three recovery models:  SIMPLE, BULK-LOGGED and FULL.

Typically, you will use FULL or SIMPLE.  In my book, it is almost always FULL for mission critical environments with zero down time, and SIMPLE for everything else.  But... you will find much more detail about that here:   

The purpose behind this post is two-fold.  First, I want to note that new databases inherit their Recovery Model from the model database.  Not too long ago, I found myself and my Team at our weekly meeting, asking each other 'what is the default Recovery Model?'.  Regrettably, and somewhat embarrassingly, we were not sure. 

Secondly, I just wanted to post a quick tip  --  forget about the gui, use this to check your Recovery Model:

               name [DatabaseName],
               recovery_model_desc [RecoveryModel]
               database_id > 4

Get rid of that database_id filter, if you want to see your sys dbs, too.

No comments:

Post a Comment