The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. Issues in MSDB restore that may result loss of all scheduling information, as well as the backup and restore history. 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.
The model database is used as the template for all databases created on an instance of SQL Server. SQL Server created tempdb every time SQL Server is started that required existence of model database. Whenever new database created on SQL Server, DB Engine took entire contents of the model database & its database options to copy & create the new database.
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 2008 SP2, you must use a database backup that was created after the server instance was upgraded to SQL Server 2008 SP2.
MSDB & Model database restore is almost same as other user database restore.
Steps to restore SQL Server MSDB & MODEL Database :-
1) For MSDB Database Stop SQL Server Agent Services to disconnect SQL Server Agent session from MSDB
2) Close all connection from MODEL & MSDB database
3) Restore MSDB from desired backup
RESTORE DATABASE MSDB
FROM DISK = ‘<Backup File Location>’
WITH REPLACE
RESTORE DATABASE MODEL
FROM DISK = ‘<Backup File Location>’
WITH REPLACE
4) You are done.
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
