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:

7 Responses 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

  2. Ken Sturgeon says:

    Are reboots required when installing the above mentioned components (SQLSysClrTypes, SharedManagementObjects, and PowerShellTools)?

    I tried to install SharedManagementObjects and it indicated that I first needed to install SQLSysClrTypes. I have done so but I still get the same message (missing SQLSysClrTypes) when I try to install SharedManagementObjects. I fear I may have to restart the server before it will recognize that the SQLSysClrTypes are already installed.

  3. DjE says:

    Hi Barac,

    It looks like my SQL modules were loaded to a different directory, C:\Program Files\WindowsPowerShell\Modules. PowerShell does not seem to look in this direction. What seems to be the default directory is C:\windows\system32\WindowsPowerShell\v1.0\modules. Is there a way to update the search paths for PowerShell? I am running Version 5.1.14393.3383 Thank you for any help you may provide.

  4. Ken Sturgeon says:

    Barac, thank you for your response. I failed to mention that I’m trying to copy a database from one SQL Server instance to a second SQL Server instance on a different physical machine. I’m performing the task from a server that does not have SQL Server installed.

    I discovered that my problem was that I had inadvertently downloaded the x86 versions of the msi’s rather than the x64 versions. I’ve since successfully installed them and executed my scripts without rebooting the server.

    Thanks again for taking the time to respond.

Leave a Reply

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