All things Data Related....

On this site I will be posting entries related to Data platform and analytics that I learn and believe could be valuable learning to others who want insight from their data

TSQL Script – Determining Default Database File & Log Path

TSQL Script – Determining Default Database File & Log Path

  • Comments 8
  • Likes

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

Comments
  • How about method 3 - look at the model database.

  • The model database isn't necessarily in the default data and log directories. In our system we put all system databases in a different location than the default. Also, it's possible to either move model or to change the default directories after the install.

  • Method 3 indeed - why all the fuss?

  • Whoops spoke too soon

  • Method 3. Use Powershell and SMO.

    $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') ServerName
    $s | select DefaultLog, DefaultFile

    That should do it.

  • Make sure that you load this assembly before running the Method 3 script.

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment