Recovery Models for System Databases
New: 14 April 2006
This section summarizes the recommendations for what recovery model to use with each of the system databases.
System database
Recovery model
Comments
Simple
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.
Full (default)
Newly created user databases use the same recovery model as the model database. If you want your new databases to use the simple recovery model, change the recovery model of model to SIMPLE.
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.
Simple (default)
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.
—
The recovery model is irrelevant. SQL Server backup cannot back up the Resource database.
.gif "ms365937.note(en-US,SQL.90).gif")
Note:
You can perform a file-based or a disk-based backup on the Resource database by treating Mssqlsystemresource.mdf as if it were a binary (.exe) file. But you cannot use SQL Server restore on these backups.
Simple
The simple recovery model is required; therefore, tempdb log space is always automatically reclaimed. You cannot backup tempdb.
For information about which system databases must be backed up, see Considerations for Backing Up and Restoring System Databases.
See Also
Concepts
Choosing the Recovery Model for a Database
Considerations for Switching from the Full or Bulk-Logged Recovery Model
Considerations for Switching from the Simple Recovery Model
Transaction Log Truncation