SQL Server services could not be started as 2 instances sharing same 'model' file

Question

Please advise me on this issue:

I have one default instance in SQL Server 2005 (sp 3 x64 bits) called instanceA. Then I installed another 2 instances called instanceB and C.

After that I restore master.bak from production server to this instanceB. The SQL Server services for this instance could not be started at all since then. If I turned off the default instance services, instanceB can be started. This is because both of the instances are pointing to the same model.mdf file in MSSQL.1 folder. Hence both instances could not be started simultaneously.

I believe that in production server, the model path is configured to the default folder MSSQL.1 .Is there anyway to change the path to MSSQL.8 that belongs to instanceB upon installation so that both instances A and B could be started together?

How to change the path of pointing to the correct model file?

Thank you.

Answer 1

The paths of the other system databases (model, msdb, and tempdb) can be changed using ALTER DATABASE ... MODIFY FILE. The changes take effect on instance restart.

I blogged about how to move all the system databases here, including a video demo of the whole process. (The demos shows 2008 R2, but it's the same for 2005.) In your case, the files are already in the spot you want, so all you need to do is change the paths.