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;

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.


  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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