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
FULLrecovery mode and other system databases are in
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.
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
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.
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.
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.
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.