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.
1$sqlserver = "sqlservername\instance"
2
3[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
4[void][Reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
5$server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver
6
7$computer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $server.ComputerNamePhysicalNetBIOS
8$sqlservice = ($computer.Services | Where-Object { $_.Type -eq "SqlServer" })
9
10$originalstartparams = $sqlservice.StartupParameters
11$newstartparams = "-dM:\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf;-eM:\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lM:\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf"
12
13$sqlservice.StartupParameters = $newstartparams
14$sqlservice.alter(); $sqlservice.stop(); $sqlservice.start()
15
16# You can also change it back once you're done
17$sqlservice.StartupParameters = $originalstartparams
18$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.
1[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
2$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
3try { $server.ConnectionContext.Connect() } catch { throw "Can't connect to $servername." }
4
5$sql = "select IS_SRVROLEMEMBER ('sysadmin') as issysadmin"
6$issysadmin = ($server.databases['master'].ExecuteWithResults($sql)).Tables.issysadmin
7if (!($issysadmin)) { Write-Warning "Not a sysadmin on $servername, resultset will probably be underwhelming." }
8
9$allprocs = $server.EnumProcesses() | Where-Object { $_.Host -ne $sourceserver.ComputerNamePhysicalNetBIOS -and ![string]::IsNullOrEmpty($_.Host) }
10$systemdbs = "master","msdb","model","tempdb"
11$excludedPrograms = "Microsoft SQL Server Management Studio - Query","SQL Management"
12$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.
1[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
2$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlserver"
3$server.Configuration.ShowAdvancedOptions.ConfigValue = $true
4$server.Configuration.Alter()
5foreach ($sourceprop in $server.Configuration.Properties) {
6 $displayname = $sourceprop.DisplayName
7 $configvalue = $sourceprop.ConfigValue
8 Write-Host "$displayname = $configvalue"
9}
10$server.Configuration.ShowAdvancedOptions.ConfigValue = $false
11$server.Configuration.Alter()