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

master

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.

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.

msdb

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.

Resource

The recovery model is irrelevant. SQL Server backup cannot back up the Resource database.

ms365937.note(en-US,SQL.90).gifNote:

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.

tempdb

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

Other Resources

ALTER DATABASE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance