Recovery Models for System Databases
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. |
|
User configurable1 |
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 "Note") 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. |
1 To learn the current recovery model of the model, see How to: View or Change the Recovery Model of a Database (SQL Server Management Studio) or sys.databases (Transact-SQL).
For information about which system databases must be backed up, see Considerations for Backing Up and Restoring System Databases.