7

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

  • 1
    There is no hard and fast rule to keep Model in full recovery you can keep it in simple recovery. Recovery model of database is always according to RPO and RTO. – Shanky Jan 10 '15 at 10:39
9

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.

| improve this answer | |
5

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? ;)

| improve this answer | |
2

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

| improve this answer | |
  • You can change recovery model of master database to full, please correct it. – Shanky Jan 10 '15 at 10:42
  • 2
    @Shanky it can be changed .. but whats the point ? Please refer to the link that I mentioned For backwards compatibility with earlier versions of Microsoft SQL Server, the recovery model of master can be set to FULL or BULK_LOGGED. However, BACKUP LOG is not supported for master. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model. – Kin Shah Jan 10 '15 at 12:53
  • Also, downvoters .. I would like to know the reason for downvote .. so that I can improve my answer. – Kin Shah Jan 10 '15 at 12:54
  • 3
    technically what you just posted in above comment can be added to your answer , as of now it seems like master database "cannot" be in simple recovery you emphasized on always u also wrote you cannot change recovery model – Shanky Jan 10 '15 at 13:22
1

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.

| improve this answer | |
  • Tempdb does not takes its recovery model from model database as such its always in simple recovery but again logging is minimal – Shanky Jan 10 '15 at 10:43
  • 3
    @shanky you are nit-picking terms here for no purpose that I can see. This answer states "new databases", in no way do I see any reference to tempdb or other system databases. – user507 Jan 10 '15 at 16:57
  • @shawn I did not down voted the answer neither I was nit picking I just wanted to add that fact from my side a normal comment I agree may be its not directly related – Shanky Jan 10 '15 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.