Backing Up the model, msdb, and distribution Databases

The model, msdb, and distribution databases are backed up in the same way as user databases and should be backed up regularly if they are changed. These databases perform the following functions:

• The model database is the template used by Microsoft® SQL Server™ when creating other databases, such as tempdb or user databases. When a database is created, the entire contents of the model database, including database options, are copied to the new database.
  
  
• The msdb database is used by SQL Server, SQL Server Enterprise Manager, and SQL Server Agent to store data, including scheduling information and backup and restore history information.

  SQL Server automatically maintains a complete online backup and restore history in msdb. This information includes who performed the backup, at what time, and on which devices or files it is stored. This information is used by SQL Server Enterprise Manager to propose a plan for restoring a database and applying any transaction log backups. Backup events for all databases are recorded even if they were created with custom applications or third-party tools. For example, if you use a Microsoft Visual Basic® application that calls SQL-DMO objects to perform backup operations, the event is logged in the msdb system tables, the Microsoft Windows® application log, and SQL Server error log.

  If you use the backup and restore history information in msdb when recovering user databases, it is recommended that you use the Full Recovery model for msdb. Additionally, consider placing the msdb transaction log on fault tolerant storage.

• The distribution database is used by the replication components of SQL Server, such as the Distribution Agent, to store such data as transactions, snapshot jobs, synchronization status, and replication history information. Any server configured to participate either as a remote distribution server or as a combined Publisher/Distributor has a distribution database.

Backup Considerations

It is important to back up model, msdb, or distribution after any operation that updates the database:

• If model is damaged in some way due to media failure, and there is no current backup available, any user-specific template information added to model is lost and needs to be re-created manually.
  
  
• If msdb is damaged, then any scheduling information used by the SQL Server Agent is lost and needs to be re-created manually. Backup and restore history information is also lost.
  
  
• If distribution is damaged, and there is no current backup available, any replication information used by the SQL Server replication utilities is lost and needs to be re-created manually. For this reason, consider using Full Recovery model for distribution.

All recovery models are supported for model, msdb and distribution.

Modifying the model, msdb and distribution Databases

The model, msdb and distribution databases can be modified in the following ways:

• The model database is modified only by specific user changes.
  
  
• The msdb database is altered automatically by:
  • Scheduling tasks.
    
    
  • Storing Data Transformation Services (DTS) packages created with the DTS Import/Export Wizard to an instance of SQL Server.
    
    
  • Maintaining online backup and restore history.
    
    
  • Replication.
• The distribution database is altered automatically by:
  • The Replication Log Reader Agent utility.
    
    
  • The Replication Distribution Agent utility.
    
    
  • The Replication Snapshot Agent utility.
    
    
  • The Replication Merge Agent utility.

As with master, it is recommended that user objects not be created in msdb or distribution; otherwise msdb and distribution need to be backed up more frequently. Additionally, user objects compete with the system objects for space.

See Also

Backing Up and Restoring Databases

Backing Up and Restoring Replication Databases

Configuring the SQLServerAgent Service

Replication Overview

System Tables

Using Recovery Models