How to Restore model and msdb database

Unlike master database, restoring model and msdb is simple and follows the same procedure as restoring any other user database. However we have to be very cautious while restoring system databases as it will have sensitive data which is important for SQL Server to function without any issues.

Create Database fails with the error "Could not obtain exclusive lock on database 'model'. Retry the operation later."

Sometimes when you try to create a database, the operation fails by presenting the below error message.

TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Database 'Test'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=1807&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

This happens because, the exclusive lock on the model database is a mandatory step that database engine takes to create a new database. We all know that when SQL Server creates a new database, it uses a copy of the model database to initialize the new database and its metadata. Also, users could create, modify, drop objects in the Model database. So, it is important to exclusively lock the model database to prevent copying the data in change from the Model database. Otherwise, there is no guarantee that the content copied from the Model database are consistent and valid.

To fix this issue, find out the connection that is using model database and close it and then re-issue the create database statement.

SELECT * FROM sys.sysprocesses WHERE DB_NAME(dbid)='model'

Moving model and msdb databases

Moving of model and msdb databases also follow the similar procedure as moving the tempdb database but with some additional steps.
Since these are also system databases, unfortunately we cannot move them just by detach and attach process, as we cannot attach or detach a system database.

Moving model database:

1. First get the list of model database files by using this query
   
   select name,physical_name from sys.master_files where DB_NAME(database_id)='model'  
2. Then for each model database file that you need to move, execute statements like below
   Alter Database model modify
   
   file (NAME = 'modeldev' ,
   FILENAME = 'Drive:\Path\model.mdf') -- Mention the new location
   
   
   Alter Database model modify
   file (NAME = 'modellog' ,
   FILENAME = 'Drive:\Path\modellog.ldf') -- Mention the new location
   
3. Stop SQL Services
   
4. Move the files manually to the new location
   
5. Start SQL Services
   
6. Verify the new Location
   select name,physical_name from sys.master_files where DB_NAME(database_id)='model'
   

Moving msdb database:

1. First get the list of msdb files by using this query
   
   select name,physical_name from sys.master_files where DB_NAME(database_id)='msdb'  
2. Then for each msdb database file that you need to move, execute statements like below
   Alter Database msdb modify
   
   file (NAME = 'MSDBData' ,
   FILENAME = 'Drive:\Path\MSDBData.mdf') -- Mention the new location
   
   
   Alter Database msdb modify
   file (NAME = 'MSDBLog' ,
   FILENAME = 'Drive:\Path\MSDBLog.ldf') -- Mention the new location
   
3. Stop SQL Services
4. Move the files manually to the new location
5. Start SQL Services
6. Verify the new Location
   
   select name,physical_name from sys.master_files where DB_NAME(database_id)='msdb'

If the SQL Server Instance is configured with Database Mail option, then after the msdb movement you will have to verify that the database mail is working fine by sending a test email.