get the data directory

Hi,

I am searching for how getting the data directory where default mdf files are based.

(C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)

My goal is to deploy mdf file in this folder during installation.

Thanks

hi,

I'm little in trouble giving advieces as well as registry values are not honored as the seem..

you can query, via a NON documented extended stored procedure the Windows registry for some info..

you can see a HKLM\Microsoft\Microsoft SQL Server key..

SQL Server 2005 registers instances as MSSQL.1, MSSQL.n, and this for every engine type, like Report server, Olap engine, ...

but you can get the MSSQL.x value querying the \Instance Names\SQL key as well

something like

SET NOCOUNT ON;

DECLARE @.test varchar(256);

DECLARE @.instance VARCHAR(128);

DECLARE @.regKey VARCHAR(128);

SELECT @.instance = CONVERT(varchar, SERVERPROPERTY('InstanceName'));

IF @.instance IS NULL

SET @.regKey = 'MSSQLServer';

ELSE

SET @.regKey = @.instance;

SELECT @.regKey AS [Instance name];

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',

@.key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\',

@.value_name = @.regKey,

@.value = @.test OUTPUT;

SELECT @.test AS [base instance directory];

SET @.regKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @.test + '\Setup';

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',

@.key = @.regKey ,

@.value_name = 'SQLDataRoot',

@.value = @.test OUTPUT;

SELECT @.test AS [SQL Path as per Setup key];

--<-

Instance name

MSSQLServer

base instance directory

MSSQL.1

SQL Path as per Setup key

--

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

but here problems arise... as usually data folder is in \...\MSSQL.1\MSSQL\Data , but that Data part is not shown anywhere.. .you can "perhaps" going littbe bit further, querying the \Parameters\SQLArg0 , which reports the full path of the master.mdf file formatted as the startup parameter required by SQL Server, thats to say

SQLArg0 = "-dC:\Program Files\..here full path.......\master.mdf"

and parse the result to extract the required "path".. or just hope no one changes that path and hardcode a + '\Data\' addition...

but, again, this whole post is in complete undocumented and unsupported mode..

regards

|||Thanks for your help Andrea