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:
> ![smoServer gm </span></pre> So having done that we found a property named <a href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.defaultfile.aspx">DefaultFile</a> 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: <pre class="csharpcode"><span class="kwrd">function</span> Get-SQLServerDefaultDataFilePath( [<span class="kwrd">string</span>]](https://i0.wp.com/mitchwheat.com/wp-content/ql-cache/quicklatex.com-35dd1d61373d8311c1fda836c8af96a6_l3.png?resize=675%2C217&ssl=1 "Rendered by QuickLaTeX.com")
sqlServer = ![(<span class="kwrd">throw</span> <span class="str">'sqlServer is required'</span>)){ [reflection.assembly]::LoadWithPartialName(<span class="str">"Microsoft.SqlServer.Smo"</span>) >](https://i0.wp.com/mitchwheat.com/wp-content/ql-cache/quicklatex.com-7a11983740e13b5419d54ac7264e1d05_l3.png?resize=825%2C56&ssl=1 "Rendered by QuickLaTeX.com")
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; }