TSQL to get default location of database data and log files?

Published 08 January 08 05:37 AM | SQL Master 
By default the SQL Server database data & log files are created on the default data directory which is Program Files or based on where SQL binary files are installed.
 
In order to find what is the default location you can run following TSQL:

declare @SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT

declare @SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT

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

Comments

# SSQA.net - SqlServer-QA.net said on January 8, 2008 6:30 AM:

By default the SQL Server database data & log files are created on the default data directory which

# Other SQL Server Blogs around the Web said on January 8, 2008 6:34 AM:

By default the SQL Server database data & log files are created on the default data directory which

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.