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;
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/