Databases in SIMPLE recovery mode may not really be set to SIMPLE

In SQL 2012 if your model database is set to SIMPLE recovery and you create a new user database you expect the recovery model of the user database to be automatically set to SIMPLE. In fact, sp_helpdb and the sys.databases reports that the recovery model is SIMPLE. However, if you backup the database and then backup the transaction log you’ll be surprised that the last action succeeds. If you do this in earlier SQL versions the backup log reports an error stating that such an operation is not permitted for databases with SIMPLE recovery model.

Typically for development servers you’d set model to SIMPLE. Databases which have inherited this recovery model will show ever increasing transaction log files. If you manually CHECKPOINT such databases you should observe that the log space used never decreases.

One workaround is to manually toggle the recovery model. That is, use the ALTER DATABASE to set it to FULL and then run it again but this time set it to SIMPLE.

The following Connect item has been raised for this issue:

https://connect.microsoft.com/SQLServer/feedback/details/765315/when-model-db-is-set-to-simple-recovery-newly-created-databases-are-not-truely-in-simple-recovery

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: