Professional PowerShell-based SQL Server SMO Recipes

Last year, I finally started working with SMO (SQL Management Objects) within PowerShell and I absolutely love it. Below are a few recipes that I created and use extensively throughout my SQL Management scripts, which you can find on ScriptCenter. Some recipes show more than one way to perform a task.

These SMO recipes are particularly useful because of the extensive automation being performed. For instance, in the restore recipe, file structures are automatically determined using information contained within the given .bak file. These scripts were created and tested on Windows 8.1 with PowerShell 4 and SQL Server 2014. PowerShell v3 and above is required for many of the recipes.

SQL Administration

Programmatically change SQL Server startup parameters

Usually when we deal with SMO, we’re using the Microsoft.SqlServer.SMO. This snippet, however, uses Microsoft.SqlServer.SqlWmiManagement. This is what controls everything you usually see in SQL Server Configuration Manager.


It’s important to note that you must use the server’s network name when working with SqlWmiManagement. This script automatically determines your SQL instance’s network name by using $server.ComputerNamePhysicalNetBIOS.

Get info similar to SQL Server Management Studio Activity Monitor Processes tab

I used this technique to collect information for my DB/Login Inventory App, Watch-DBLogins.ps1.

In my script, I added the results to a SQL table and collected the information over time.

Get Server Configuration (sp_cofigure)

$server.Configuration is useful for not only viewing, but modifying server configurations as well. Don’t forget the .Alter() if you make any changes.

To see more recipes, head to the PowerShell SMO Recipe index.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *


Migrating SQL Server?

dbatools is an awesome PowerShell module that helps you migrate entire instances with a single command.

Available from and github

SqlServer Needs You

SqlServer now has a dedicated engineer and Microsoft is asking for our input!

Upvote priorities and cmdlets now


Chrissy LeMaire

Brandon Abshire
View Brandon Abshire, MCDBA's profile on LinkedIn


Chrissy has been awarded the Microsoft MVP for her work in the PowerShell community.

Join us!

Belgian PowerShell
User Group

  SQL PASS PowerShell
Virtual User Group


Upvotes Needed

Help persuade Microsoft to open source SQL Server's PowerShell module, SQLPS.

Upvote now on Microsoft Connect