SQL Server 2008/R2 recovery model

Pretty much all of our databases on certain servers do not require the Full Recovery model (we don't do transaction log backups) and the default should always be to create databases and specify the Simple Recovery model.

Quite often and for certain practical reasons many databases are created using SSMS. However mistakes can be made and the operator can forget to specify the Simple Recovery model. This leads to a "suprise" a few days later when the box is struggling with disk space due to three or four 60GB log files that have never been truncated.

I can make the Simple Recovery model the default setting for new databases by configuring the recovery model on the model database. However, is this recommended, if I do this could it come back and bite me in any way in the future?

I see one of three options here:

1) you can have a templated script to create databases that explicitly includes the recovery model.

2) you can set the model database to simple and not have to worry about this.

3) you can hope everybody remembers, which seems like what you are doing. (not recommended)

I would personally go with number two. That's what the model database is there for.

StackExchange Database Administrators Q#6252, answer score: 17