Powershell: Get SQL Server Default File Paths using SMO

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") > 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:

  > sqlServer = null
   sqlServer

   smoServer.DefaultFile

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

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

.csharpcode, .csharpcode pre
{
 font-size: small;
 color: black;
 font-family: consolas, "Courier New", courier, monospace;
 background-color: #ffffff;
 /*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
 background-color: #f4f4f4;
 width: 100%;
 margin: 0em;
}
.csharpcode .lnum { color: #606060; }