PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server

Some users complained that they cannot use my PowerShell script published on TechNet https://gallery.technet.microsoft.com/Use-PowerShell-to-Backup-3bb0a397

It is a PowerShell script to Backup all user databases to Azure Storage.

They experienced the following error:

8/14/2018 11:18 AM – Setting Folders
PS>TerminatingError(New-Object): “Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.”
new-object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.

You just need to install extensions for PowerShell to be able to run this script. The SQLPS module is included with the SQL Server installation (for backwards compatibility)

There are two SQL Server PowerShell modules; SqlServer and SQLPS. The SQLPS module is included with the SQL Server installation (for backwards compatibility), but is no longer being updated. The most up-to-date PowerShell module is the SqlServermodule. The SqlServer module contains updated versions of the cmdlets in SQLPS, and also includes new cmdlets to support the latest SQL features.

Source: https://docs.microsoft.com/en-us/sql/powershell/sql-server-powershell?view=sql-server-2017

In my case, I have SQL Server 2016 as a target server, and I am running a script from other location. I can manually install the PowerShell Extensions for SQL Server 2016.

Install the following components:

  • SQLSysClrTypes.msi – Microsoft System CLR Types for Microsoft SQL Server 2016
  • SharedManagementObjects.msi – Microsoft SQL Server 2016 Shared Management Objects
  • PowerShellTools.msi – Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2016

Source: Microsoft SQL Server 2016 Feature Pack

After you finished with installing all components you can check whether the module is installed.

Similar Posts:

One Response to “ “PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server”

  1. Samuel says:

    Hi,

    I need to deploy a similar script along with the rest of the stuff on client machines. And I can’t request them to install the components on all machines. Can you possibly modify it to use SQLServer module instead of SQLPS so that one may see how this is implemented. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *