Change default location where new DB's are created

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Change default location where new DB's are created

Author

Topic

poser
Posting Yak Master

124 Posts

Posted - 2007-07-19 : 17:57:48

Hello
The default location where db's are created is almost full.
Is there any way to change the default location where the .mdf and .ldf are created when I create a new db? I know I can do this when I create a new db and go select where I want it to be, but can I make it default to a different directory automatically so no human intervention is needed?
tia
r/P

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-19 : 23:44:17

you can change this in SSMS on the server properties dialog. right click on the server node in OE, choose, properties, then select the "database settings" tab.

or you can do it via script like this:


USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\sql\data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'E:\sql\log'
GO

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources