Move Model Database (How to change default database location)

Question

I'm not sure if this would belong on Stack Overflow or Server Fault, so if it belongs on Server Fault please let me know.

Anyway, here's the problem. I've got an install of SQL Server 2008 on a server on the local c drive. The c drive is not a large drive, and as such i want all new databases created on the server to be created on the e drive. My first instinct was to move the database files for the model database, but SSMS is not giving me the option to detach it.

So, my question is, is there a way to set up the server so that all of the new databases are created on the e drive by default?

Answer 1

I've used this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82885 -- it seems to work for DBs created through SSMS, but I had issues with a vendor product which created its own DB.

Answer 2

In SSMS, right click on the server and choose "Properties". On the "Database Settings" page of the Server Properties window, specify your new locations for data and log files.

You could also do this with T-SQL by writing directly to the registry:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'E:\YourData'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'E:\YourLogs'
GO