Considerations for Restoring the model and msdb Databases
Restoring the model or msdb database from a backup is necessary under the following circumstances:
- The master database has been rebuilt.
- The model or msdb 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.
Important
System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server 2005 SP1, you must use a database backup that was created after the server instance was upgraded to SQL Server 2005 SP1.
Considerations for Restoring msdb
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. Therefore, if msdb contains scheduling or other data used by the system, you must restore msdb when you rebuild master.
Restoring model or msdb is the same as for performing a complete database restore of a user database.
You cannot restore a database that is being accessed by users. If SQL Server Agent is running, it can access msdb. Therefore, before restoring msdb, stop SQL Server Agent.
Best Practice RESTORE disconnects users when necessary; however, it is a best practice to shut down applications beforehand.
If you are using the full recovery model for msdb, as recommended, you can bring the database up to the time of your most recent log backup.
Important
When SQL Server is installed or upgraded and whenever setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to SIMPLE.
To stop SQL Server Agent
- How to: Stop SQL Server Agent (SQL Server Configuration Manager)
- How to: Stop SQL Server Agent (net Commands)
To restore a database
- Performing a Complete Database Restore (Full Recovery Model)
- Performing a Complete Database Restore (Simple Recovery Model)
See Also
Concepts
Considerations for Backing Up the model and msdb Databases
Stopping Services
Other Resources
Backing Up and Restoring Replicated Databases