Trying to find the path initially can be challenging. However, by opening SSMS up, right clicking, and launching the powershell window you'll be able to easily find the correct path to get the server level object.
This allows you to leverage default methods in powershell like Get-ChildItem for iterating through objects. It treats the navigated SQL server path basically as a "file structure" allowing some interesting actions to be performed. One of these is a different approach to killing connections to a particular database.
I found this great pointer by reading Killing SPIDS in Powershell from MidnightDBA
Review that article for scripts focused on the termination of running spids.
For an adhoc purpose the scripts MidnightDba provided are excellent and would allow quickly executing a kill script on connections from ssms > powershell prompt.
import-module -name sqlserver -disablenamechecking -verbose:$false -debug:$false
CD SQLSERVER:\SQL\$ServerName -Verbose:$false -Debug:$false
dir | ?{$_.Name -eq "$DatabaseName"} | %{$_.KillAllProcesses($DatabaseName)}
I approach this with a different method in one final script using just the SMO server method KillAllProcesses. For some tasks I've found it really helpful to have a simple 1 line kill statement thanks to MidnightDba's pointer with the statements similar to the one above.
Using Microsoft's documented method shows another example of how to use to restart the service. This was one modified approach I took. I prefer not to use this type of approach as working with get-childitem with server objects to me as a little unintuitive.
<#
.LINK https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-stop-pause-resume-restart-sql-server-services#PowerShellProcedure
#>
$ErrorActionPreference = 'continue'
[bool]$Matched = (get-module -name SqlServer -listavailable | measure-object).count
if($Matched -eq $false) { install-package SqlServer -scope CurrentUser -verbose:$false -Force}
[datetime]$StepTimer = [datetime]::Now
$private:ServerName = $env:ServerName
import-module -name sqlserver -disablenamechecking -verbose:$false -debug:$false
# Get a reference to the ManagedComputer class.
CD SQLSERVER:\SQL\$private:ServerName -Verbose:$false -Debug:$false
$Wmi = (get-item -debug:$false -verbose:$false .).ManagedComputer
$DfltInstance = $Wmi.Services['MSSQLSERVER']
#Display the state of the service.
write-host "Stopping Instance: $($DfltInstance.ServiceState.value__)"
$DfltInstance.Stop()
while($DfltInstance.ServiceState.value__ -ne 1) #1 stopped
{
Start-Sleep -seconds 5
$DfltInstance.Refresh()
write-host "... state: $($DfltInstance.ServiceState)"
}
## Start the service.
$DfltInstance.Refresh()
write-host "Current Service State: $($DfltInstance.ServiceState)"
write-host "Initiating Service Start"
$DfltInstance.Start()
while($DfltInstance.ServiceState.value__ -ne 4) #4 running
{
Start-Sleep -seconds 5
$DfltInstance.Start()
$DfltInstance.Refresh()
write-host "... state: $($DfltInstance.ServiceState)"
}
write-host( "{0:hh\:mm\:ss\.fff} {1}: finished" -f [timespan]::FromMilliseconds(((Get-Date)-$StepTimer).TotalMilliseconds),'SQL Service Restart')
Database as an Object
Getting the database as an object proved to be easy though, if a little confusing to navigate initially.
$s = SqlServer\Get-SqlDatabase -ServerInstance $ServerInstance -Verbose
Once the object is obtained, you can begin scripting objects, change database properties and more very easily.
I found this method an interesting alternative to invoking using .NET accelerators as it was a quick way to easily get a database level object to work with. However, some of the limitations of not having the server level object immediately available made me end up preferring the .NET accelerator version which could look like this.
param(
$ServerName = 'localhost'
,$DatabaseName = 'tempdb'
)
$s = [Microsoft.SqlServer.Management.Smo.Server]::New($ServerName)
$d = [Microsoft.SqlServer.Management.Smo.Database]::New($s, $DatabaseName)
$s.EnumProcesses() | format-table -AutoSize
$d.EnumObjects() | Out-GridView
Interestingly, to actually access the many of the database properties you actually would call it via reference to the server object with SMO calls instead of the cmdlet. Trying $d.PrimaryFilePath doesn't work as I believe it's initiating the instance of a new database object for creation instead of referencing the initialization of a new object to an existing database. I found documentation a bit challenging to immediately sift through to get an answer, so YMMV. Someone coming from a .NET focused background might find the process a little more clear, but for me it did take some work to correctly identify the behavior.
#doesn't work. Probably trying to initialize new object for creating a db
$d = [Microsoft.SqlServer.Management.Smo.Database]::New($s, $db)
$d.PrimaryFilePath
#works to access current existing object
$s.Databases[$db].PrimaryFilePath
Exploring Properties
If you want to explore properties of an object, try using the ever faithful get-member
Depending on the type of object, you can additionally explore them with GetEnumerator, GetProperties, etc. You'll find intellisense helpful as you explore more.
For instance, here's a walkthrough on the various ways you might explore the object and find you need to dig into it to get the full detail of what you have access to.
Comparing Restoring a Database with Cmdlet vs SMO
An example of how simple using dbatools cmdlet can make restoring a database copy
rolling your own wheel
Now compare this to the complexity of running your own invocation of the SMO namespace and requires a lot more coding. Since dbatools wraps up a lot of the functionality, I've actually migrated to leveraging this toolkit for these dba related tasks instead of trying to reinvent the wheel.
