TSQL Script – Determining Default Database File & Log Path

Below are two methods that can be used to determine the default location of data and transaction log files for your installation of SQL Server. This script can come handy when automating the deployment of databases in your environment.

 

Method 1

 

-- Check if temp database exists
-- Tempdatabase is used for determining the default database path
--if the zztempDefaultPath db exists drop  

IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath')  

BEGIN 
    DROP DATABASE zzTempDBForDefaultPath  
END;

-- Create temp database. Because no options are given, the default data and --- log path locations are used

CREATE DATABASE zzTempDBForDefaultPath;

--Declare variables for creating temp database  

DECLARE @Default_Data_Path VARCHAR(512),  
        @Default_Log_Path VARCHAR(512);

--Get the default data path  

SELECT @Default_Data_Path =   
(   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
    FROM sys.master_files mf  
    INNER JOIN sys.[databases] d  
    ON mf.[database_id] = d.[database_id]  
    WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 0);

--Get the default Log path  

SELECT @Default_Log_Path =   
(   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)  
    FROM sys.master_files mf  
    INNER JOIN sys.[databases] d  
    ON mf.[database_id] = d.[database_id]  
    WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 1);

--Clean up. Drop de temp database

IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath')  
BEGIN 
    DROP DATABASE zzTempDBForDefaultPath  
END;

PRINT    @Default_Data_Path;  
PRINT    @Default_Log_Path;

Method 2

This method uses the registry entries to work out the default location path. Note: be vary of using this method as it is possible that registry entries may change and also the extended stored procedures to read the registry may not be supported in future versions.

For SQLPath:

declare @rc int,
@dir nvarchar(4000)

exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLPath', @dir output, 'no_output'
SELECT @dir

For the default data location:

declare @rc int,
@dir nvarchar(4000)
exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dir output, 'no_output'

if (@dir is null)
begin
exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
select @dir = @dir + N'\Data'
end

SELECT @dir