Restoring the model, msdb, and distribution Databases

The model, msdb, or distribution database may need to be restored from a backup when:

• The master database has been rebuilt using the Rebuild master command prompt utility.
• The model, msdb, or distribution database has been damaged, for example, due to media failure.

If model has been modified, it is necessary to restore model from a backup when you rebuild master because the Rebuild Master utility deletes and re-creates model.

If msdb contains scheduling or other data used by the system, it is necessary to restore msdb from a backup when you rebuild master because the utility deletes and re-creates msdb, which results in a loss of all scheduling information. If msdb is not restored, and is not accessible, SQL Server Agent cannot access or initiate any previously scheduled tasks. For example, if database backup operations are scheduled to run automatically using SQL Server Enterprise Manager and SQL Server Agent, a damaged msdb will prevent those backup operations from occurring.

The distribution database is not rebuilt automatically when the Rebuild Master utility is used to rebuild master; therefore it is not necessary to restore distribution after rebuilding master. If the distribution database is still intact, distribution can be re-created automatically by attaching the database to Microsoft® SQL Server™. Alternatively, a backup of distribution can be restored instead.

However, if distribution is not re-created by restoring a backup or attaching the database, the SQL Server replication utilities will not run, preventing data replication. If the distribution database is used for replication by many Publishers, this can affect many systems.

You cannot restore a database that is being accessed by users. Therefore, when restoring msdb, SQL Server Agent should be stopped. If SQL Server Agent is running, it may access msdb. Similarly, when restoring distribution, the SQL Server replication utilities should be stopped. If the SQL Server replication utilities are running, they may access distribution.

Replication utilities that must be stopped are:

• The Replication Log Reader Agent utility.
• The Replication Distribution Agent utility.
• The Replication Snapshot Agent utility.
• The Replication Merge Agent utility.

To restore a database backup

See Also