Stairway to Server Management Objects (SMO) Level 5: Moving to the SQLServer Module


In the fourth level of this series I outlined the goodies of scripting options some cool operations  to do with them, and automating simple tasks Now let´s take a look into the new and awesome SQLServer module.

The SQLServer Module

Microsoft made a huge step forward when they deprecated the SQLPS module. Yes, it is deprecated and will not be updated anymore. To replace it, they created the SQLServer module.

The fact is we still have two PowerShell modules. SQLPS and SQLServer. SQLPS as I said is deprecated but is still included in the SQL Server installation (for backwards compatibility only). The SQLServer module contains updated versions of cmdlets of SQLPS and new cmdlets to support the new SQL Server features. The SQLServer module is included starting with version 16.x of SSMS and in all the newew versions of SSMS . The module is installed via PowerShell Galley. The PowerShell Gallery is the place to find PowerShell code that is shared by the community, Microsoft, and other companies.


To Install with administrator rights, run this:

Install-Module -Name SqlServer

For the non-Administrator, use this code:

Install-Module -Name SqlServer -Scope CurrentUser

When you have other versions installed , you just update the module like this:

Update-Module -Name SqlServer

Or install with –AllowClobber option, which will ensure you overwrite any conflicting methods.

Install-Module -Name SqlServer -AllowClobber

To view the versions of the module installed:

Get-Module SqlServer -ListAvailable

This returns the version and the commands available.

getting the SqlServer module version

Now we can import the module for use in a PowerShell session.

import-module sqlserver

With this code, we check what are the cmdlets of the module.

get-command -Module sqlserver

You can see there we have 105 cmdlets.

getting a count of commands in the sqlserver module

What's Changed

What’s changed is now the SQLServer module incorporates all the sqlps cmdlets and will be maintained and updated in the future. For instance, if you liked to use the SQL Server provider as file system, you still can, as shown here:

You can use the server itself and navigate as though it were a filesystem as before:

Some Cool Operations Using the SQLServer Module

In this section we will cover:

Let´s start with selecting all information from SQL Agent. This information is useful to get some properties from the Agent such as the Category of the Agent (the JobCategories property) , if the Agent has alerts or operators configured (alerts and operator property), the jobs that are part of the Agent (the Jobs array), and the service account running the agent (AgentDomainGroup Property). We can get information with this code:

get-sqlagent -ServerInstance DESKTOP-SQEVVO1

We can see the results below:

PS C:\WINDOWS\system32> get-sqlagent -ServerInstance DESKTOP-SQEVVO1

Parent                    : [DESKTOP-SQEVVO1]
Name                      : DESKTOP-SQEVVO1
JobCategories             : {[Uncategorized (Local)], [Uncategorized (Multi-Server)], Data Collector, Database Engine Tuning Advisor...}
OperatorCategories        : {[Uncategorized]}
AlertCategories           : {[Uncategorized], Replication}
AlertSystem               : [DESKTOP-SQEVVO1]
Alerts                    : {}
Operators                 : {}
TargetServers             : {}
TargetServerGroups        : {}
Jobs                      : {CommandLog Cleanup, DatabaseBackup - SYSTEM_DATABASES - FULL, DatabaseBackup - USER_DATABASES - DIFF, DatabaseBackup - USER_DATABASES - FULL...}
SharedSchedules           : {CollectorSchedule_Every_10min, CollectorSchedule_Every_15min, CollectorSchedule_Every_30min, CollectorSchedule_Every_5min...}
ProxyAccounts             : {}
SysAdminOnly              : 
AgentDomainGroup          : NT SERVICE\SQLSERVERAGENT
AgentLogLevel             : Errors, Warnings
AgentMailType             : SqlAgentMail
AgentShutdownWaitTime     : 15
DatabaseMailProfile       : 
ErrorLogFile              : C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT
HostLoginName             : 
IdleCpuDuration           : 600
IdleCpuPercentage         : 10
IsCpuPollingEnabled       : False
JobServerType             : Standalone
LocalHostAlias            : 
LoginTimeout              : 30
MaximumHistoryRows        : 1000
MaximumJobHistoryRows     : 100
MsxAccountCredentialName  : 
MsxAccountName            : 
MsxServerName             : 
NetSendRecipient          : 
ReplaceAlertTokensEnabled : False
SaveInSentFolder          : False
ServiceAccount            : NT Service\SQLSERVERAGENT
ServiceStartMode          : Manual
SqlAgentAutoStart         : False
SqlAgentMailProfile       : 
SqlAgentRestart           : True
SqlServerRestart          : True
WriteOemErrorLog          : False
ParentCollection          : 
Urn                       : Server[@Name='DESKTOP-SQEVVO1']/JobServer
Properties                : {Name=AgentLogLevel/Type=Microsoft.SqlServer.Management.Smo.Agent.AgentLogLevels/Writable=True/Value=Errors, Warnings, 
                            Name=AgentShutdownWaitTime/Type=System.Int32/Writable=True/Value=15, Name=ErrorLogFile/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft 
                            SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT, Name=HostLoginName/Type=System.String/Writable=False/Value=...}
ServerVersion             : 16.0.1105
DatabaseEngineType        : Standalone
DatabaseEngineEdition     : Enterprise
ExecutionManager          : Microsoft.SqlServer.Management.Smo.ExecutionManager
UserData                  : 
State                     :

Let’s use the JobCategories property, as it is an array, I know this is an array because of the {} in the visualization. We pipe the results through Select-Object and then we need to use –expandproperty parameter to get all the items for this property. We can use this code:

get-sqlagent -ServerInstance DESKTOP-SQEVVO1 | 
Select-Object -ExpandProperty JobCategories 

Now, we see the properties listed:

PS C:\WINDOWS\system32> get-sqlagent -ServerInstance DESKTOP-SQEVVO1 | 
Select-Object -ExpandProperty JobCategories 
[Uncategorized (Local)]       
[Uncategorized (Multi-Server)]
Data Collector                
Database Engine Tuning Advisor
Database Maintenance          
Jobs from MSX                 
Log Shipping                  
REPL-Alert Response           
REPL-Distribution Cleanup     
REPL-History Cleanup          
REPL-Subscription Cleanup 

We can do the same thing with the jobs property. We can see the code and results below:

get-sqlagent -ServerInstance DESKTOP-SQEVVO1 | 
Select-Object -ExpandProperty jobs 

The results I see for this instance are:

We can also pick specific properties to examine. Just use the select-object cmdlet with the property(s) you want to retrieve separated by commas. Here we add name, agentdomain, and state:

get-sqlagent -ServerInstance DESKTOP-SQEVVO1 | 
Select-Object name, AgentDomainGroup, state

We see the results shown below:

agent information using PoSh

Other information you may want to retrieve is the jobs that are part of the SQL Agent. For that we use the Get-SqlagentJob. This cmdlet gives to you a macro vison of the Agent jobs. If you want specific details you need to select properties as we will see below. Here is the code to get a list of jobs.

get-sqlagentjob -ServerInstance DESKTOP-SQEVVO1

This returns some basic information about the jobs.

SQL Agent Job information returned by PoSh

If I want to get information of the jobs we can get the properties and methods that we may use:

get-sqlagentjob -ServerInstance DESKTOP-SQEVVO1 | Get-member

This returns the various properties and methods available from the results of get-sqlagentjob.

Let’s use some information from the cmdlet. We can get this information from the jobs: Name, Date Created, Last modified date, and if the job is enabled. We use this code to select data:

get-sqlagentjob -ServerInstance 'DESKTOP-SQEVVO1' | Select Datecreated, Datelastmodified, isenabled

The property that returns if the last run of the job failed or not is LastRunOutcome. We can just select this property to know the last status of the job:

get-sqlagentjob -ServerInstance 'DESKTOP-SQEVVO1' |select name,

If we want to return only the failed jobs, we need to add a condition in the code with Where-Object. Note the structure of the comparison inside the braces:

get-sqlagentjob -ServerInstance 'DESKTOP-SQEVVO1' |
Where-Object {$_.lastrunoutcome -eq 'Failed'} |
Select-Object name, lastrunoutcome 

This returns only the one failed job from above.

Getting the single failed job from SQL Agent

We can also get the schedules for jobs with:

Get-SqlAgentJob -ServerInstance DESKTOP-SQEVVO1 | Get-SqlAgentJobSchedule

Or all the steps for jobs with this code:

Get-SqlAgent -ServerInstance DESKTOP-SQEVVO1 | Get-SqlAgentJob | Get-SqlAgentJobStep

That isn't all that useful, but what if we want to get the steps for one job? We can get those for the job job that starts with BackupDatabase in the name with this code:

Get-SqlAgent -ServerInstance DESKTOP-SQEVVO1 | 
    Get-SqlAgentJob |
        where-object {$ -like "DatabaseBAckup*"}  |

It´s very important get job history to make sure all your jobs are running successfully. For that we use the Get-SQLAgentJobHistory and it will show the history of the jobs with the server and message properties.

Get-SqlAgentJobHistory -ServerInstance DESKTOP-SQEVVO1

The next cmdlet will get the general information of SQL Server instance.

Get-SqlInstance -ServerInstance DESKTOP-SQEVVO1

You can see this returns the SQL Server version and host version.

We can also get all the databases by chaining another cmdlet onto the previous one.

Adding a Login

A common task is to add a new login to an instance. When you add a new SQL login, you need to provide a password.  We can use this code to avoid that:

Add-SqlLogin -ServerInstance DESKTOP-SQEVVO1 -LoginName "MyNewLogin" -LoginType "SqlLogin" -Enable

Since we have not included the loginpscredntial parameter, a prompt appears for you to provide the password , as showing in the image below:

You can enter a password, which will be used for the login.

You can create a pscredential to suppress the prompt :

$secpasswd = ConvertTo-SecureString "test" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("mylogin", $secpasswd)
Add-SqlLogin -ServerInstance DESKTOP-SQEVVO1-LoginName "mylogin" -LoginType "SqlLogin" -LoginPSCredential $mycreds -Enable

We can check the login with Get-SqlLogin to verify it was added.

Get-SqlLogin -ServerInstance DESKTOP-SQEVVO1

We can get more information about logins, such as checking if a login is disabled with this code:

Get-SqlLogin -ServerInstance DESKTOP-SQEVVO1 -Disabled

We might use this to verify that "sa" is disabled on our instances.

Reading the SQL Server Error Log

When comes troubleshooting SQL Server, one of the more important action is to read the SQL Server error log. To get the SQL Server Error log, run this :

Get-SqlErrorLog -ServerInstance DESKTOP-SQEVVO1

This returns the entries as a series of objects.

We can also filter the log in different ways. We can see yesterday's entries with:

Get-SqlErrorLog -ServerInstance DESKTOP-SQEVVO1 -Since Yesterday

We can get look at the log before or after a date with this type of code:

Get-SqlErrorLog -ServerInstance DESKTOP-SQEVVO1 -After (Get-Date).AddDays(-2) 
Get-SqlErrorLog -ServerInstance DESKTOP-SQEVVO1 -before (Get-Date).AddDays(-3) 

Try these and see what is returned on your instance.

Querying a Table

We have a cmdlet to read data from a table in Read-SqlTableData. Here I use the code to read from a specific table:

Read-SqlTableData -ServerInstance DESKTOP-SQEVVO1  -DatabaseName adventureworks2022 -TableName AWBuildVersion -SchemaName dbo

The output is

To be honest IMHO it´s a waste of time since we already have invoke-sqlcmd. They could have used the time and effort to create new and functional cmdlets. I can get the same result if I use Invoke-SQLcmd.

Invoke-Sqlcmd -ServerInstance DESKTOP-SQEVVO1 -Database adventureworks2022 -Query "Select * from humanresources.department"

Automating a Simple Task

To automate a backup database we can use both SMO and  the SQL Server cmdlets. You can see that using SQL Server cmdlets is easier because you don’t need to have a previously knowledge of SMO . Both solutions work fine and you can choose what is the best option for your environment.

Here is the code for backing up all user databases using SMO.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null #A
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $serverName #E
$server.databases |
where {$_.IsSystemObject -eq $false -and $_.IsAccessible -eq $true}|
foreach-object {
    Backup-SqlDatabase -ServerInstance $servername -Database $ -BackupFile  "c:\backup\$($"

The code is much simpler and easier to read with PoSh SQLServer module cmdlets.

Get-SqlDatabase -ServerInstance 'DESKTOP-SQEVVO1'  |
ForEach-Object {
    if ($ -ne 'tempdb') {
        Backup-SqlDatabase -ServerInstance 'DESKTOP-SQEVVO1' -Database $ -BackupFile "c:\temp\databases\DESKTOP-SQEVVO1_$($" 

To make this easier, I can create a text file called servers.txt with the name of your SQL Server servers. I my case I have a SQL Server default instance called DESKTOP-SQEVV01, but you can add more as in the below image.

Just remember that the cmdlet will use Windows Authentication with this code:

get-content c:\temp\servers.txt |
ForEach-Object {
    $serverinstance = $_
    Get-SqlDatabase -ServerInstance $_  |
    ForEach-Object {
        if ($ -ne 'tempdb') {
            Backup-SqlDatabase -ServerInstance $serverinstance -Database $ -BackupFile "c:\temp\databases\$($serverinstance)_$($" 

We can automate this with PowerShell Jobs. When we use PowerShell jobs, PoSh concurrently runs commands and scripts through jobs in another scope. It means you don’t see the return of these jobs unless you use the cmdlets: Get-Job, Receive-Job, or Wait-Job. Here is some code to use a PoSh job.

needget-content c:\temp\servers.txt |
ForEach-Object {
    $serverinstance = $_
    Get-SqlDatabase -ServerInstance $_  |
    ForEach-Object {
        if ($ -ne 'tempdb') {
            $dbname = $
            $job = start-job  -scriptblock { Backup-SqlDatabase -ServerInstance $using:serverinstance -Database $using:dbname -BackupFile "c:\temp\databases\$($using:serverinstance)_$($using:dbname).bak" }
            $pso = new-object psobject -property @{Server=$serverinstance;DbName=$dbname;JobId=$job.Id} 
            Register-ObjectEvent -InputObject $job  -EventName Statechanged  -MessageData $pso
Get-job | receive-job

The problem with PowerShell Jobs are they run I another scope, as I said before, so it's hard to control and get the output. To solve this problem we will use PowerShell Events. By registering the event in the job we can be notified when status of the job change  to completed. The cmdlet responsible for this is Register-ObjectEvent.

$counter = 0
get-content c:\temp\servers.txt |
ForEach-Object {
    $serverinstance = $_
    Get-SqlDatabase -ServerInstance $_  |
    ForEach-Object {
        if ($ -ne 'tempdb') {
            $dbname = $
            $job = start-job  -scriptblock { Backup-SqlDatabase -ServerInstance $using:serverinstance -Database $using:dbname -BackupFile "c:\temp\databases\$($using:serverinstance)_$($using:dbname).bak" }
            $pso = new-object psobject -property @{Server=$serverinstance;DbName=$dbname;JobId=$job.Id} 
            Register-ObjectEvent -InputObject $job  -EventName Statechanged  -MessageData $pso
1..$counter |
foreach-object {
    $eventsource = Wait-Event
    switch ($eventsource.SourceEventArgs.JobStateInfo.State){
        "Failed" {
            Write-Output "Backup $($eventsource.MessageData.server) - $($eventsource.MessageData.dbname) failed"
            receive-job $eventsource.MessageData.jobid
        "Completed" {
            Write-Output "Backup $($eventsource.MessageData.server) - $($eventsource.MessageData.dbname) Completed"
        "Running" {
            Write-Output "Backup $($eventsource.MessageData.server) - $($eventsource.MessageData.dbname) is running"
    remove-event $eventsource.SourceIdentifier
Get-event | remove-event
Get-EventSubscriber | Unregister-Event

We get the results from our console:

backup results

Now let’s cause an error by

if ($ -ne 'tempdb')


if ($ –eq 'tempdb')

and let’s see what happens. We see this errors out below:

We can make this run faster by using Foreach parallel, available in PowerShell (Core) v7+ only.

get-content c:\temp\servers.txt |
ForEach-Object {
    $serverinstance = $_
    Get-SqlDatabase -ServerInstance $_  |
    ForEach-Object -Parallel  {
        if ($ -ne 'tempdb') {
            $dbname = $
            Backup-SqlDatabase -ServerInstance $serverinstance -Database $ -BackupFile "c:\temp\databases\$($serverinstance)_$($"
    } -ThrottleLimit 5

Summing Up

In this article we covered the new sqlserver module and it's use through examples of the sqlserver module cmdlets. We saw how to use PowerShell Jobs and the Register-ObjectEvent cmdlet in real world as well.




This article is part of the parent stairway Stairway to Server Management Objects (SMO)


5 (2)

You rated this post out of 5. Change rating




5 (2)

You rated this post out of 5. Change rating