Model database question 1

[takaoki]

Hi,

Why is the Model database in Full recovery mode, and the other system databases are not?

Thanks

 

[SQLWilts]

Hi,
Model system database, as it's name suggests, is the model (or template) that SQL server uses to create new databases when you right click the databases node and choose "New Database". It takes its defaults from there, and so you then modify the new database to suit your needs.
Model is set as full recovery mode by default as you would want most of your new production databases to be in that recovery mode so that you can perform point in time recovery. Should you want to change your new DB to simple, mearly use the Alter Database command.
If, on a particular instance (for example a development instance) you want all new databases to be in simple recovery mode, just change the model database.
Long story short, the model database holds the defaults for all new databases created on an instance.
HTH

 

[takaoki]

Thanks. So it's not in FULL because one would take log backups on Model, correct? It's just there as a template?

 

[SQLWilts]

Absolutely correct