sql server 2008: setting default location for mdf/ldf

Question

When I create a new database, by default the files are saved to c:\program files... but I would like them by default to be saved into a different location WITHOUT having to adjust anything. Is there a way to have this done by default?

Perhaps there's some stored system procedure that I would have to change?

Answer 1

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