With one of the projects I am currently working I needed to gather the location of the Default File Paths for the Data, Log and Backup Files for SQL Server. I wanted to be able to do this remotely and via a PowerShell Script.

While working on the script I found the following page that had the information I needed to gather this information. After changing my script to match what was posted I found that the script had errors and wouldn’t work. To get it working I had to change the Equality check that the script was using. Again thanks to Allen White for the original script and information, but I have updated it and have attached the working script.

For others that are looking for this information, the key piece that you need to remember is as follows.

“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.”

And here is my Script, plus the link to the Original Article.

######################################
# Script: ReturnDefaultSQLFilePaths.ps1
#
######################################
# Return the Default File Paths for the
# Data Files, Log Files and Backup Files
#
######################################

$Server = '.\Developer'
$SMOServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Server

# Get the Default File Locations

$DefaultFileLocation = $SMOServer.Settings.DefaultFile
$DefaultLogLocation = $SMOServer.Settings.DefaultLog

if ($DefaultFileLocation.Length -eq 0)
    {
        $DefaultFileLocation = $SMOServer.Information.MasterDBPath
    }
if ($DefaultLogLocation.Length -eq 0)
    {
        $DefaultLogLocation = $SMOServer.Information.MasterDBLogPath
    }

write-host 'Default File Locations'
write-host '======================'
write-host 'File Location :' $DefaultFileLocation
write-host 'Log Location :' $DefaultLogLocation
write-host 'Backup Location :' $SMOServer.BackupDirectory