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

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.

Answer 2

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 3

Use this code in the custom action,

strName = "(local)\SQLEXPRESS"
arrNames = Split(strName, "\")
intIndex = Ubound(arrNames)
strFile =  arrNames(intIndex)
strFile = trim(strFile)

if strFile <> "" then
    reg = readFromRegistry("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\" & strFile & "\Setup\SQLPath", "")
    reg = trim(reg)
    if reg <> "" then
        reg = reg & "\DATA"
    end if
end if

msgbox reg

function readFromRegistry (strRegistryKey, strDefault )
    Dim WSHShell, value

    On Error Resume Next
    Set WSHShell = CreateObject("WScript.Shell")
    value = WSHShell.RegRead( strRegistryKey )

    if err.number <> 0 then
        readFromRegistry= strDefault
    else
        readFromRegistry=value
    end if

    set WSHShell = nothing
end function

The variable reg will retrieve the path. You can give the instance name on the variable strName at the start.

Registry function source: How to check for registry value using VbScript