Why Model database in SQL Server is in FULL recovery mode

Why Model database in SQL Server is in FULL recovery mode and other system databases are in simple recovery mode?

Why is the model database in SQL Server in FULL recovery mode and other system databases are in SIMPLE recovery mode?

I think you are asking why the model database is different from the other system databases.

New user databases are created with the same recovery model as the model database. The default recovery model for the model database depends on the edition of SQL Server you have installed.

From Choosing the Recovery Model for a Database:

The simple recovery model is generally appropriate for a test or development database. However, for a production database, the best choice is typically the full recovery model, optionally, supplemented by the bulk-logged recovery model. However, the simple recovery model is sometimes appropriate for a small production database, especially if it is mostly or completely read-only, or for a data warehouse.

Following this logic, editions targeted at test and development environments (SQL Server Express and LocalDB) set the recovery model of the model database to SIMPLE. Other editions set the recovery model of the model database to FULL.

The master and msdb databases are good to have in simple mode because they generally don’t change frequently enough to warrant having them in the full recovery model.

Restoring master is a very significant exercise, and restoring a full database backup is as far as you ever want to go. msdb contains quite a bit of useful information, but generally, regular database backups are enough.

The important thing with these databases is to back them up whenever something significant has changed, and to also have scripts / deployment packages to recreate the necessary information (logins, SSIS packages, agent jobs, etc). You definitely don’t need them to be in the full recovery model.

The model database, though, is used to create new user databases, and these are generally good to have in the full recovery model on production boxes. When you create a database, I’m sure you explicitly set every option, so it’s less important what setting model has, right? 😉

Why Model database in SQL Server is in FULL recovery mode ?

This is because model acts as “Model” for any newly created database. So depending on your environment and your business requirements, if a user database needs “point-in-time” recovery, then a full recovery mode will provide that (this assumes that you are regularly taking full and transaction log backups).

If your business does not require “point-in-time” recovery then you can change the recovery mode of MODEL to simple. Make sure to still take full backups (and test them regularly to see if you are able to restore or not).

Note that the databases restored using backup/restore method or detach/attach method, will have their original recovery mode.

other system databases are in simple recovery mode?

Master and tempdb are always in simple recovery mode. You cannot change the recovery mode.

For msdb

If you want to use the backup and restore history information in msdb when you recover user databases, we recommend that you use the full recovery model for msdb. Additionally, consider placing the msdb transaction log on fault tolerant storage.

read up more on : Recovery Models for System Databases

The recovery model for new databases is taken from the Model database.
After the creation of the new database, you can change the recovery model to simple or Bulk-Logged.