Supported and recommended recovery model for SQL Server System Databases

SQL Server does have restriction on how we can backup system databases. These restrictions are by design and not to be treated as a limitation of SQL Server.

Please refer below details for these restrictions and associated Microsoft recommendations:

Master: Microsoft Recommended recovery model is “Simple”.  For backwards compatibility with earlier versions, the recovery model of master can be set to FULL or BULK_LOGGED. However backing up log is not supported in master. So even if you change master database’s recovery model to full or bulk-logged, you can not backup log and associate maintenance plan/jobs will fail.

Model: Microsoft Recommended and default recovery model of this database is “Full”.  However you can configure it anyway as per your business needs. (ie. If you want all of new user databases should be in “Full” recovery by default, make sure model’s recovery model is “Full”). Also you may not want to take transaction log of this database because it rarely changes.

Msdb: Default recovery model is “Simple” and there is no direct recommendation on recovery model from Microsoft for this database. Based on your business needs and DR planning, you may want to keep this database in “Full recovery” model and take periodic transaction log backup.

Tempdb: Default recovery model is “Simple”. Also Microsoft recommends to keep it in Simple recovery model only because you can not take backup of tempdb and tempdb log space is always required to be automatically reclaimed.

Resource: Recovery model for this database is irrelevant as SQL Server backup cannot back up the Resource database. However Microsoft advice to perform a file-based or a disk-based backup on the Resource database by treating Mssqlsystemresource.mdf as if it is a binary (.exe) file. Also you cannot use SQL Server restore on these backups.