recovery model of model database

Question

Hi All, Is it good to leave "model" database recovery model as "FULL:" or change it to simple? is it just to ensure the newly created user databases to have FULL recovery model or is there any other good reason ? I see log backup pending for model database that's why asking this question.

Regards, Sam

Answer 1

For a dev server where no one cares about log backups, it is a very good idea to set model to simple recovery. For the same reason, it is a very bad idea to do on production server. As for the log backup, I guess someone has made changes to it. If there is a table, or a set of tables, you want in every database, you can add that to model. But not very many people do. The one update I would recommend to do in model is this:

DROP SCHEMA db_owner
DROP SCHEMA db_accessadmin
DROP SCHEMA db_securityadmin
DROP SCHEMA db_ddladmin
DROP SCHEMA db_backupoperator
DROP SCHEMA db_datareader
DROP SCHEMA db_datawriter
DROP SCHEMA db_denydatareader
DROP SCHEMA db_denydatawriter

This removes a bunch of schemas that exist for backwards compatibility with SQL 2000, but which, hopefully, few people use in practice.

Answer 2

The "model" system database is used as template, when you create a new user database. So if you change th recovery mode, all future databases get the same and you may have to remind it for the user database. https://learn.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-ver16

Answer 3

Hi @Samantha r,

Best practice:  We recommend that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.

As said here.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.