As mentioned before, I spend quite some time creating scripts in PowerShell for managing SQL server. Here is an example of a couple of SMO functions I have found very useful.
In the future, I'll try to post some examples that uses the functions below
We'll start by loading the correct assembly:
[void][reflection.assembly]::Load("Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")Or the more general "LoadWithPartialName" - that also works if you have another SMO version:
[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")Then create a function to establish a SMO connection to an SQL instance. The function below creates a smo connection, with a user defined timeout, and applicationname. Remember that it is always recommended to give meaningful names to any connectionto SQL - it can make troubleshooting much easier.
function New-SMOconnection { Param ($server, $applicationName= "PowerShell SMO", [int]$commandTimeout = 5) Write-Debug "Function: New-SMOconnection $server $connectionname $commandtimeout" if (test-path variable:\conn) { $conn.connectioncontext.disconnect() } else { $conn = new-object('Microsoft.SqlServer.Management.Smo.Server') $server } $conn.connectioncontext.applicationName = $applicationName $conn.connectioncontext.connectTimeout = $commandTimeout $conn.connectioncontext.connect() $conn}And a small function to remove a SMO connection once you are done with it:
function Remove-SMOconnection { Param ($connection) Write-Debug "Function: Remove-SMOconnection $connection" Remove-Variable -Name $connection -Scope 1}The above functions can be used in the following manner:
1.) Establish a SMO connection, remembering to check for errors:
$SMOconn = New-SMOconnection "MyTestServer\SQLinstance0001" "JBs Secret PowerShell App"if ($SMOconn.connectioncontext.isopen -eq $false) {# handle error}You now a an open SMO connection to your SQL server. If you would like to retrieve information about what version/build you SQL server is, you could use the following:
$SMOconn.Information.versionOr retrieve a list of database names:
Foreach ($database in $SMOconn.databases) { $database.name }Remember that "Get-Member is you friend!" - you can use Get-Member (or its alias, "gm") to explore the possibilities of SMO:
$SMOconn | gmOnce you are done, remember to close the SMO connection:
Remove-SMOconnection SMOconn