The other day, we noticed that when our CREATE DATABASE scripts were running... the databases were not created where we expected. Due to our setup, we want the databases and log files to use the SQL Server default locations.

We ran the following T-SQL to verify the default locations:

USE master
GO

DECLARE @SmoDefaultFile nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT

DECLARE @SmoDefaultLog nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT

SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]

Results...

DefaultFile	DefaultLog
--------------------------------	--------------------------------
D:\SQLDbs\Data	D:\SQLDbs\Logs
(1 row(s) affected)

Looks correct... why is it putting the database and log files in:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Just to be sure... we verified this setting on the Server Properties screen in SQL Management Studio...

Looks correct...

What we found through testing... In order for the default locations to work as expected... SQL Server must be restarted. I feel (not proven) that SQL Server loads these values from the registry at startup, and does not update when the change happens on Server Properties.