Mitch Wheat - Treat the cause, not the symptoms!

I recently needed to find the location of SQL Server’s default data file path in order to create multiple database data files as part of an automated production install. After looking at and discarding a few options that included reading the registry directly, SQL Server Management Objects (SMO) seemed a logical choice. Talking to one of my colleagues, Piers, whose Powershell wizardary has to be experienced to fully appreciate, we (well he!) fired up a Powershell GUI and we took a look at the methods available.

As an aside, if you are not aware of this ‘trick’ it’s worth explicitly mentioning:

In Powershell, first load the relevant assembly into memory (which in this instance is Microsoft.SqlServer.Smo):

  > [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") > $null

Then, create an instance of the type you are interested in (the Server type):

  > $smoServer = new-object Microsoft.SqlServer.Management.Smo.Server “servername”

and pipe the object instance default method output through Get-Member to list all the Events, Methods and Properties exposed:

  > $smoServer  gm 

So having done that we found a property named DefaultFile which looked promising, but it just returned an empty string. After a bit of digging around, it transpires that it only returns a path if the current location is different to where the master DB is located, so here it is in Powershell:

function Get-SQLServerDefaultDataFilePath
(
  [string]$sqlServer = $(throw 'sqlServer is required')
)
{
   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") > $null
   $smoServer = new-object Microsoft.SqlServer.Management.Smo.Server $sqlServer

   $str = $smoServer.DefaultFile

   # if DefaultFile property is empty, it means default path has not been changed
   if ($str)
      {$str}
   else
      {$smoServer.MasterDBPath}
}

Update: Piers pointed out that Books Online contains a useful section on programming tasks using SQL Server Management Objects (SMO)