Get default database directory using DMO

  • Is it possible to get the default database directory settings from an SQL Server using SQLDMO. I am creating databases on remote servers and need to know this value.

  • I think you can use the parameters collection of the Configuration object to find out what you need:

    For x = 1 To vServer.Configuration.Parameters.Count

    Debug.Print vServer.Configuration.Parameters.Item(x)

    Next x

    This is what I got for a typical installation showing the data, error, and log file info for the master:

    -dC:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf

    -eC:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG

    -lC:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf

    You can also find the data path of any known database by using the "PrimaryFilePath" property of the "SQLDMO.Database" object.

  • Hey, I didnt even KNOW there was a configuration collection. Found a different way to do it:

    Dim oServer As SQLDMO.SQLServer

    Dim o As SQLDMO.Registry

    Set oServer = New SQLDMO.SQLServer

    oServer.LoginSecure = True

    oServer.Connect "."

    Debug.Print "Err Logs :"; oServer.Registry.ErrorLogPath

    Debug.Print "Data Root:"; oServer.Registry.SQLDataRoot

    Debug.Print "SQL Root :"; oServer.Registry.SQLRootPath


    Set oServer = Nothing

    You could also get these by just accessing the registry directly. Paul, Im curious, if you're creating db's on remote servers, wouldnt you just want to go with the defaults? Dont have to know them to use them, SQL applies them automatically. Just curious!


  • That's not too bad either. Are they read-only properties, or can you write to them as well?

  • I have not tried them, but they arent flagged as read only in the object browser.


  • Thanks guys, the registry object is just what I was looking for...

    Andy, I wanted to allow the user to specify a directory other than the default but also display the default.

    The properties are Read/write Modifiable.


Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply