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.

$sqlserver = "sqlservername\instance"

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver

$computer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $server.ComputerNamePhysicalNetBIOS
$sqlservice = ($computer.Services | Where-Object { $_.Type -eq "SqlServer" })
$originalstartparams = $sqlservice.StartupParameters
$sqlservice.StartupParameters = $newstartparams
$sqlservice.alter(); $sqlservice.stop(); $sqlservice.start()

# You can also change it back once you're done
$sqlservice.StartupParameters = $originalstartparams
$sqlservice.alter(); $sqlservice.stop(); $sqlservice.start()

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.

$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
try { $server.ConnectionContext.Connect() } catch { throw "Can't connect to $servername." }

$sql = "select IS_SRVROLEMEMBER ('sysadmin') as issysadmin"
$issysadmin = ($server.databases['master'].ExecuteWithResults($sql)).Tables.issysadmin
if (!($issysadmin)) { Write-Warning "Not a sysadmin on $servername, resultset will probably be underwhelming." }

$allprocs = $server.EnumProcesses() | Where-Object { $_.Host -ne $sourceserver.ComputerNamePhysicalNetBIOS -and ![string]::IsNullOrEmpty($_.Host) }
$systemdbs = "master","msdb","model","tempdb"
$excludedPrograms = "Microsoft SQL Server Management Studio - Query","SQL Management"
$allprocs | Where-Object {$systemdbs -notcontains $_.Database -and $excludedPrograms -notcontains $_.Program }| Select Login, Host, Database, Program

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.

$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlserver"
$server.Configuration.ShowAdvancedOptions.ConfigValue = $true
foreach ($sourceprop in $server.Configuration.Properties) {
	$displayname = $sourceprop.DisplayName
	$configvalue = $sourceprop.ConfigValue
	Write-Host "$displayname = $configvalue"
$server.Configuration.ShowAdvancedOptions.ConfigValue = $false

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

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server