Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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;
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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/

share|improve this answer
    
I've noticed that on clusters I can only read the Default Data dir on Software\Microsoft\MSSQLServer\Setup with Key SQLDataRoot, should I read both and Test? –  Gabriel Guimarães Dec 14 '10 at 15:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.