Problem accessing SQL Server from PowerShell

Question

I'm trying to implement a backup management script i found at http://sev17.com/2011/03/restore-and-relocate-database-files-using-powershell/

The article says it depends on SQLPSX version 2.3.2.1 or higher - I have the most current version.

attempting to execute this line:

$server = get-sqlserver $sqlserver

results in:

New-Object : Cannot find type [Microsoft.SqlServer.Management.Common.ServerConnection]: make sure the assembly containing this type is loaded.
At C:\Users\...\Documents\WindowsPowerShell\Modules\sqlserver\SQLServer.psm1:68 char:24
+     { $con = new-object <<<<  ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver }

result of get-module -listAvailable

    ModuleType Name                      ExportedCommands                                                           
    ---------- ----                      ----------------                                                           
    Script     adoLib                    {}                                                                         
    Manifest   Agent                     {Get-AgentTargetServerGroup, Get-AgentProxyAccount, Get-AgentJobSchedule...
    Script     ISECreamBasic             {}                                                                         
    Script     mySQLLib                  {}                                                                         
    Script     OracleClient              {}                                                                         
    Script     OracleIse                 {}                                                                         
    Script     PBM                       {}                                                                         
    Script     PerfCounters              {}                                                                         
    Manifest   Pscx                      {}                                                                         
    Manifest   Repl                      {Get-ReplEnumSubscriptions2, Get-ReplPublisherMonitor, Get-ReplEnumPubli...
    Manifest   ShowMbrs                  {Get-ShowMbrs, Set-ShowMbrs, New-ShowMbrs, Get-GroupUser}                  
    Script     SQLIse                    {}                                                                         
    Manifest   SQLMaint                  {Get-SqlIndexFragmentation, New-UserMember, Invoke-SqlIndexRebuild, Get-...
    Manifest   SQLParser                 {Test-SqlScript, Out-SqlScript}                                            
    Script     SQLProfiler               {}                                                                         
    Script     SQLPSX                    {}                                                                         
    Manifest   sqlserver                 {Get-SqlScripter, Get-SqlIndexFragmentation, Remove-SqlServerRoleMember,...
    Manifest   SSIS                      {New-ISItem, Get-ISPackage, Get-ISItem, Copy-ISItemFileToSQL...}           
    Manifest   WPK                       {}                                                                         
    Manifest   AppLocker                 {}                                                                         
    Manifest   BitsTransfer              {}                                                                         
    Manifest   PSDiagnostics             {}                                                                         
    Manifest   TroubleshootingPack       {}                                                                         
    Manifest   WebAdministration         {}    

RE: SMO

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") 

results:

True   v2.0.50727     C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll                             
True   v2.0.50727     C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll                                                   
True   v2.0.50727     C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoExtended\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SmoExtended.dll                                   
New-Object : Cannot find type [Microsoft.SqlServer.Management.Common.ServerConnection]: make sure the assembly containing this type is loaded.

Answer 1

Make sure the Assembly containing the Microsoft.SqlServer.Management.Common.ServerConnection type (which I think is Microsoft.SqlServer.ConnectionInfo) is loaded first:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

Answer 2

For me works with

Import-Module -Name SqlServer

It could stay in script NOTE:

• Windows Server 2016

• Before I have installed

Install-Module -Name SqlServer

• With [Reflection.Assembly]::LoadWithPartialName works only on windows 10 for me.