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") > <span class="kwrd">null</span></span></pre>    Then, create an instance of the type you are interested in (the <span style="font-family:cons;">Server</span><span style="font-family:consMS;"> </span>type):   <pre class="csharpcode"><span style="font-size:85%;">  >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>]sqlServer = (<span class="kwrd">throw</span> <span class="str">'sqlServer is required'</span>)){   [reflection.assembly]::LoadWithPartialName(<span class="str">"Microsoft.SqlServer.Smo"</span>) >null
smoServer = <span class="kwrd">new</span>-<span class="kwrd">object</span> Microsoft.SqlServer.Management.Smo.ServersqlServer

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)


.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; }

Visual Studio Database Guide

CodePlex is hosting an excellent whitepaper created by the Visual Studio ALM Rangers, containing guidance on working with Visual Studio 2010 Database projects:

Practical guidance for Visual Studio 2010 Database projects, which is focused on 5 areas:

  • Solution and Project Management
  • Source Code Control and Configuration Management
  • Integrating External Changes with the Project System
  • Build and Deployment Automation with Visual Studio Database Projects
  • Database Testing and Deployment Verification

This release includes common guidance, usage scenarios, hands on labs, and lessons learned from real world engagements and the community discussions.

You can download the Visual Studio Database Guide here.

SQL Sentry Plan Explorer

It may not be the greatest named application out there but SQL Sentry Plan Explorer is a free, lightweight, standalone tool that improves dealing with SQL Server query plans so much, you’ll wonder why it hasn’t been incorporated into SSMS (and let’s face it, viewing plans in SSMS really sucks!).

There are several ways to open a plan: In SSMS, right-click a graphical plan and select “Show Execution Plan XML”, then copy and paste the plan XML into Plan Explorer. Or, save an execution plan from SSMS to a .sqlplan file, then open the file from Plan Explorer.

Download SQL Sentry Plan Explorer here.

TSQL: Round to Nearest 15 Minute Interval

A colleague asked me if I had any TSQL to hand that would round down to a 15 minute interval and also round to nearest 15 minute interval. A quick search found several formulas but several had rounding errors. Here are both without any rounding errors.

declare @adate datetime = ‘2010/02/15 23:59:00’

 

— The epoch, or start of SQL Server time: ‘1900-01-01 00:00:00.000’

— select cast(0 as DateTime) as Epoch

 

— Both these formulas will only work until ‘5983-01-24 02:07:00.000’ !!

— select dateadd(n, 2147483647, cast(0 as DateTime))

 

 

— Round down to nearest 15 minute interval (avoiding any rounding issues)

select dateadd(n,(DATEDIFF(n, cast(0 as DateTime), @adate)/ 15) * 15, cast(0 as DateTime))

 

— Round to nearest 15 minute interval (avoiding any rounding issues)

select dateadd(n,((DATEDIFF(n, cast(0 as DateTime), @adate) + 7)/ 15) * 15, cast(0 as DateTime))

As noted, they have the limitation of working only until 5983 AD, but I figure I won’t be around!

Mark Russinovich: “Zero Day”

Microsoft Technical Fellow and all-round Windows Internals expert, Mark Russinovich, has an upcoming book release. And this time it’s not a new edition of ‘Windows Internals’!

His fiction debut, Zero Day, is set in a world completely reliant on technology (sounds familiar), and surrounds the events of cyber infrastructure attacks released on a largely unprepared world.

How to give great presentations

Assuming your user group is actually holding meetings :), User Group Support Services (UGSS) have released a series of videos on “How to give great presentations” aimed at first time speakers and anyone wanting to improve their skills (I know I need to):

This video series guides you through what you need to know to give your first presentation or to improve your existing presentation skills. You’ll learn how to choose the right subject for you, how to break subjects up so that your explanations and demonstrations are clear and understandable, how to construct your slide deck so that it covers essential subjects without sending your audience to sleep, why being nervous is completely normal and what you can do to make it manageable, tips and tricks for giving great demonstrations, how to prepare your laptop so that it does not fight you while you are presenting and finally how to deliver the presentation that you have worked so hard creating.