How do I change where databases are created in SQL Server Management Studio? [closed]
Want to improve this question? Update the q开发者_JAVA技巧uestion so it's on-topic for Stack Overflow.
Closed 10 years ago.
Improve this questionBy default, all new databases are created on C:\
. I need them to be created on E:\
. 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 E:\
by default?
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
There is a guide here for SQL Server 2000 og 2005, my guess it's probably the same for SQLServer 2008: http://support.microsoft.com/kb/224071
Go down to the "Moving the model database" section.
I hope this helps.
精彩评论