First we need to know the name and file location of the Model database files. When you change the location, make sure to keep the same name. Technically we don’t need the current physical path, but it’s just a good idea to have it documented in case things go badly.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N'model');
GOCode language: SQL (Structured Query Language) (sql)
Next we need to tell SQL where we plan to move the files. Make sure to change the ”FILENAME” path and ensure the “NAME” is the same as what you got from the above query.
USE master;
GO
ALTER DATABASE model
MODIFY FILE (NAME = modeldev, FILENAME = 'Y:MSSQL10.MSSQLSERVERMSSQLDATAmodel.mdf');
GO
ALTER DATABASE model
MODIFY FILE (NAME = modellog, FILENAME = 'Z:MSSQL10.MSSQLSERVERMSSQLDATAmodellog.ldf');
GOCode language: SQL (Structured Query Language) (sql)
Now we are ready to restart SQL server. Once SQL is back up you might want to run the first query again just to make sure everything went as planned and the location has been updated properly. Also, don’t forget to clean up after yourself and delete the old database files.
More Information – If you are moving your Model database you might want to check out the following posts:
How to move the Master database
How to move the MSDB database
How to move the TempDB database
SQLAgent Error 435
How to move cluster Quorum drive
How to move cluster MSDTC drive
Comments
6 responses
I am surprised, I have never thought of moving the Model database this way… cannot believe it even worked, well at least I used the technique prescribed on MSDN http://support.microsoft.com/kb/224071. Is there any pitfalls, or disadvantages to either method?
I’ve used the T3608 method before as well. The only drawback to that method is if you forget to restart SQL if you started it from a command with the trace flag, or you forget to go back and change it in configuration manager. Not really a drawback, but I’ve seen people do it before because they didn’t bother to check connectivity afterword. Hard to believe I know 🙂
I just find this method to be more consistent, since it works for all the system DBs except for Master. I blogged about moving master yesterday and will do the the rest of the system DBs this week.
If you are doing this on a Cluster environment wont the passive server try to take control when the SQL Server is shut down?
By shut down I meant while restarting.
If you shutdown or restart the SQL Service in cluster manager then the service will do just that. It is intentional and not a failure of the service so it will not get moved over to another cluster node. If you told it to move the service in cluster manager, then yes it would get moved to another node.
Thanks!!!
That clears my biggest question…