The examples below are written on a computer running Windows 8 and SQL Server 2012.
SQLPS is imported much simpler than we used to import SQL Server Management Objects SMO assemblies.
Import-Module 'SQLPS'
The parameter „DisableNameChecking“ can be added to avoid a message about unapproved verbs.
A list of exported Cmdlets in the module can be shown
(Get-Module 'SQLPS' -ListAvailable).ExportedCmdlets.Values.Name
Backup-SqlDatabase
Add-SqlAvailabilityDatabase
Disable-SqlAlwaysOn
Enable-SqlAlwaysOn
Switch-SqlAvailabilityGroup
Join-SqlAvailabilityGroup
New-SqlAvailabilityGroup
New-SqlAvailabilityReplica
New-SqlHADREndpoint
Remove-SqlAvailabilityGroup
Remove-SqlAvailabilityDatabase
Remove-SqlAvailabilityReplica
Resume-SqlAvailabilityDatabase
Set-SqlAvailabilityGroup
Set-SqlAvailabilityReplica
Set-SqlHADREndpoint
Suspend-SqlAvailabilityDatabase
Test-SqlAvailabilityGroup
Test-SqlAvailabilityReplica
Test-SqlDatabaseReplicaState
New-SqlAvailabilityGroupListener
Set-SqlAvailabilityGroupListener
Add-SqlAvailabilityGroupListenerStaticIp
Invoke-PolicyEvaluation
Restore-SqlDatabase
Invoke-Sqlcmd
Encode-SqlName
Decode-SqlName
Convert-UrnToPath
Server Object
To create a SMO Server object on a local default SQL Server database instance can be like$SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server '(local)'
SMO only connects to the when you ask for something specific. For a start I would like to know the basics about the installation.
$SmoServer.Information
Parent : [TITANIUM]
Version : 11.0.3128
EngineEdition : EnterpriseOrDeveloper
ResourceVersion : 11.0.3000
BuildClrVersion : 4.0.30319
BuildClrVersionString : v4.0.30319
BuildNumber : 3128
Collation : Latin1_General_100_CI_AS_KS_WS_SC
...
The full set of Server property values can be accessed$SmoServer | Format-List *
AuditLevel : Failure
BackupDirectory : D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup
BrowserServiceAccount : NT AUTHORITY\LOCALSERVICE
BrowserStartMode : Disabled
BuildClrVersionString : v4.0.30319
BuildNumber : 3128
...
Database Object
A SMO Database object can be created on a database with a known name$SmoDatabase = $SmoServer.Databases['msdb']
This object can give access to some information
$SmoDatabase | Format-Table DataSpaceUsage,IndexSpaceUsage -AutoSize
DataSpaceUsage IndexSpaceUsage
-------------- ---------------
12080 3136
Table Object
Also the Database object can be used to create a SMO Table object. This can be direct on the table name alone$SmoDatabase.Tables['sysjobhistory']
Or the Table object can be create more specific on the schema name with the table name
$SmoTable= ($SmoDatabase.Tables |
Where-Object -FilterScript { $PSItem.Schema -eq 'dbo' -and $PSItem.Name -eq 'sysjobhistory' })
Again the Table object gives access to some basic information
$SmoTable | Format-Table Schema,Name,DataSpaceUsed,IndexSpaceUsed,RowCount -AutoSize
Schema Name DataSpaceUsed IndexSpaceUsed RowCount
------ ---- ------------- -------------- --------
dbo sysjobhistory 32 32 84
Stored Procedure Object
The Database object can also be used to create a SMO Stored Procedure object. Like the Table object it can be created in two ways, but I will be (professional) lazy and only show the creation on the name alone$SmoStoredProcedure = $SmoDatabase.StoredProcedures['sp_get_job_alerts']
A stored procedure usually has one or more parameters that can be described with
$SmoStoredProcedure.Parameters | Format-Table Name,DataType,DefaultValue,IsOutputParameter -AutoSize
Name DataType DefaultValue IsOutputParameter
---- -------- ------------ -----------------
@job_id uniqueidentifier False
@job_name sysname False
Server Job Object
With the installation of the SQL Server database service is also installed SQL Server Agent that is the SQL Server scheduler used for maintenance, monitoring and other automated tasks.A SMO Job Server object can be created from a Server object property and gives a quick access to some basic SQL Server Agent information
$SmoServer.JobServer
AgentDomainGroup : NT SERVICE\SQLSERVERAGENT
AgentLogLevel : Errors, Warnings
AgentMailType : SqlAgentMail
AgentShutdownWaitTime : 15
DatabaseMailProfile :
ErrorLogFile : D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT
...
More interesting is the information om a job, where a SMO Server Job object can be created on the Server object$SmoJob = $SmoServer.JobServer.Jobs['syspolicy_purge_history']
This gives access to some basic information on the job and its execution
$SmoJob | Format-Table CurrentRunStatus,LastRunDate,LastRunOutCome,NextRunDate -AutoSize
CurrentRunStatus LastRunDate LastRunOutcome NextRunDate
---------------- ----------- -------------- -----------
Idle 05-04-2013 02:00:00 Succeeded 21-05-2013 02:00:00
Reference
There are much more on SMO in MSDN Library, you can start with„Microsoft.SqlServer.Management.Smo Namespace“
SMO is much larger which is indicated at
„SQL Server Management Objects Reference“, take a look at the index to the left.