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

Some user 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:

Leave a Reply

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