Freigeben über

TSQL Script – Determining Default Database File & Log Path

  • Artikel

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

  • Anonymous
    March 24, 2014
    How about method 3 - look at the model database.
  • Anonymous
    March 24, 2014
    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.
  • Anonymous
    March 24, 2014
    Method 3 indeed - why all the fuss?
  • Anonymous
    March 24, 2014
    Whoops spoke too soon
  • Anonymous
    May 21, 2014
    Pingback from In Which Folder / Directory Does SQL 2008 Store Files? | Click & Find Answer !
  • Anonymous
    May 21, 2014
    Pingback from In Which Folder / Directory Does SQL 2008 Store Files? | Click & Find Answer !
  • Anonymous
    May 22, 2014
    Method 3. Use Powershell and SMO.

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

    That should do it.
  • Anonymous
    May 22, 2014
    Make sure that you load this assembly before running the Method 3 script.

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
  • Anonymous
    October 11, 2014
    Colin, you opened a door to a lot of information, thank you !
  • Anonymous
    May 13, 2015
    what kind of article is this? and this is on MSDN? Very good!!.. these kind of articles ensure that we have a job. Urge people to follow this..