Get SQL Server database folder of selected instance by using vbscript

Question

Assume that you have a running SQL Server Express instance named (local)\SQLEXPRESS. Its database folder is c:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

How can VBScript be used to retrieve that folder?

Maybe by using SMO? And if so, how? <- Forget that. SMO uses .NET. Only possible in PowerShell.

---

Update: The reason for this is, that I am developing an MSI setup that uses a custom action (call to a .NET based assembly) for attaching mdf Files to an existing SQL server instance. for this, the .mdf files get transferred from the installation medium to the hard drive. So the setup needs to know where to place the files.

Since I would like to support the standards, I need to place the files into the common data folder.

---

Update: When selecting a SQL server instance in InstallShield, the Property IS _ SQLSERVER _ SERVER is set with the instance name.

I found out, that I can query the registry under HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL to get the SQL name for the instance name (e.g. SQLEXPRESS -> MSSQL.1).

With this information, I can query HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Setup to retrieve the SQLDataRoot key. When I append a \Data to the value found here, I have the folder, I was looking for.

However, is there a better way?

Answer 1

If you said why you were doing this it might be easier.

SQL Server will let you place your files on any local drive letter (even master, model, msdb and temp can be moved), although during installation there is a default folder used for each instance.

Since I would like to support the standards, I need to place the files into the common data folder.

If I was a DBA installing your software, I would prefer that you not put them in that location (which is usually on the C: drive). I would prefer that you ask me where to install them, because if these databases have even moderately significant use, they are going to go on SAN drives where I can easily expand the capacity and I would want the database and logs on separate drives (i.e. not the Way Microsoft installs by default), and the log especially on a drive (i.e. not C:) where if it fills up, I'm not going to render my server incapacitated in any way.

Answer 2

The PrimaryFilePath Property of SQL-DMO looks interesting.

The MSDN states that SQL-DMO is deprecated as of SQL Server 2008, but for now it should still be working.

If you don't want to use SQL-DMO anymore, I guess there might be something usable in the root\Microsoft\SqlServer namespace of WMI. But Microsoft has either hidden it very well or my search skills have left me, at the moment I cant find anything in this regard.

(EDIT: Removed something that did not answer the question.)

The default data path is also stored in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\DefaultData

I'm not sure where instances write their data, but it must be somewhere in the logical vicinity.