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.
The model has been modified. In this case, it is necessary to restore model from a backup when you rebuild master because the Rebuild Master utility deletes and re-creates model.

The model and msdb databases can only be restored from backups created on a Microsoft® SQL Server™ 2000 server. Restore of backups of these databases made on SQL Server version 7.0 or earlier is not supported.

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. This results in a loss of all scheduling information, as well as the backup and restore history. If msdb is not restored, and is not accessible, SQL Server Agent cannot access or initiate any previously scheduled tasks.

Meta Data Services uses msdb as the default repository database. An open connection between Meta Data Services and msdb will disrupt an msdb restore. To release the connection, restart Enterprise Manager and then restore msdb. Do not click the Meta Data Services node in Enterprise Manager until msdb is fully restored.

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 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.

Restoring the model, msdb, and distribution Databases

See Also