Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'DefaultData'
    , @datadir output;

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

SELECT @datadir as Data, ISNULL(@logdir,@datadir) as Log;
share|improve this question
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.

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


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.