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