Archive for September 29th, 2012
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: