TSQL to get default location of database data and log files?
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]
**__________________________________**
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.