I love the fact that you can get information about your SQL Server installations from SMO, and then you can use that information in scripts. This allows you to create one script for some function, like to create a database, for example, and run it on any server and you know it'll just run.
There are two places where SMO stores database file default location. One is for the system database and log files, and it's in the Information collection of the Server object. The properties are MasterDBPath and MasterDBLogPath, respectively. The other is in the Settings collection of the Server object, and the properties are DefaultFile and DefaultLog, respectively.
Here's the problem: when the default location for the user database files is the same as the system database files, the DefaultFile and DefaultLog properties are never initialized. If you change them (using Management Studio or via the registry values) then the properties will be populated, but if you don't the values will be empty.
Using a PowerShell script to get this information, we can accommodate this issue by script like this:
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'SQLTBWS\INST01'
$fileloc = $s.Settings.DefaultFile
$logloc = $s.Settings.DefaultLog
if ($fileloc.Length -eq 0) {
$fileloc = $s.Information.MasterDBPath
}
if ($logloc.Length -eq 0) {
$logloc = $s.Information.MasterDBLogPath
}
At this point the $fileloc variable contains a string object with the name of the default file location, and the $logloc variable contains a string object with the name of the default log file location.
The SQL Server dev team is going to make a change in SMO so that DefaultFile and DefaultLog are properly populated, but until that's available this code will successfully return the locations you need in your scripts.
Allen