sql-server - 从 PowerShell 访问 SQL Server 时出现问题

Question

我正在尝试实现我在 http://sev17.com/2011/03/restore-and-relocate-database-files-using-powershell/ 找到的备份管理脚本

文章说这取决于 SQLPSX 版本 2.3.2.1 或更高版本 - 我有最新版本。

试图执行这一行:

$server = get-sqlserver $sqlserver

结果是:

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 }

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         {}    

回复:SMO

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

结果:

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

确保首先加载包含 Microsoft.SqlServer.Management.Common.ServerConnection 类型(我认为是 Microsoft.SqlServer.ConnectionInfo)的程序集:

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