Default Data and Log Directories from remote server using TSQL

Question

How do I know the directories independently of Instance Name, cluster Name or default instance?

**UPDATE: ** Very important you can't read registry data with a nvarchar(MAX) only with a valued nvarchar like for example nvarchar(4000). I didn't know that so my reads was always failing...

**UPDATE2: ** On clusters the Default Data Directory is located on the

Software\Microsoft\MSSQLServer\Setup

registry on the SQLDataRoot Key.

**UPDATE3: ** For remote Server use linked server!

This is the final Script:

declare @datadir nvarchar(4000)
        ,@logdir nvarchar(4000);

EXEC master.dbo.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'DefaultData'
    , @datadir output;

IF @datadir IS NULL
BEGIN
EXEC master.dbo.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\Setup'
    , N'SQLDataRoot'
    , @datadir output;
END
EXEC master.dbo.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'DefaultLog'
    , @logdir output;

SELECT @datadir as Data, ISNULL(@logdir,@datadir) as Log;

Answer 1

You will find the default data and log directories in the registry which can be read from TSQL xp_instance_regread stored procedure. Please see this post for full detail.

http://tenbulls.co.uk/2010/07/08/how-to-set-and-manage-your-sql-server-default-paths/