Backing Up the model, msdb, and distribution Databases

The model database is a 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 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. The distribution database is used by the replication components of SQL Server, such as the Distribution Agent, to store data including transactions, snapshot jobs, synchronization status, and replication history information. A server configured to participate either as a remote distribution server or as a combined Publisher/Distributor has a distribution database.

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.

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 by using SQL Server Enterprise Manager. Similarly, 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.

The model database is modified only by specific user changes.

The msdb database is altered automatically by:

• Scheduling tasks.
• Storing DTS packages created with the DTS Import and DTS Export wizards to 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 are not 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.

SQL Server maintains a complete online backup and restore history automatically 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 automatically 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 NT® application log, and SQL Server error log.

All types of backup operations are supported for model, msdb and distribution. Because model and msdb are unlikely to become large, it may be easier to back up the entire database on a regular basis, rather than manage transaction log backups. Similarly, if distribution is unlikely to become large, it may be easier to back up the entire database on a regular basis, rather than manage transaction log backups.

To create a database backup

See Also