使用 SMO 執行 PowerShell 腳本時缺少程序集

我編寫了一個腳本，將數據庫名稱、恢復的數據庫名稱、備份源、數據文件位置和日誌文件位置作為參數，並在 SQL Server 實例上執行數據庫恢復。當從 PowerShell ISE 執行時，腳本工作正常，但從 PowerShell 控制台執行時會拋出缺少程序集錯誤，或者當通過 SQL 代理作業作為 CmdExec 或 PowerShell 類型執行時，術語不被辨識為 cmdle 的名稱。

我試圖在腳本中使用 Add-Type 添加程序集的路徑，但沒有名為 Microsoft.SqlServer.Management.Smo.Server、Microsoft.SqlServer.Management.Smo.Restore… 等的程序集。

作為PS新手，我沒有想法。任何建議，想法如何解決這個問題以便能夠在 SQL 代理作業步驟中執行這個腳本？

我正在 ISE 中執行腳本並使用命令進行控制台

.\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log

在 SQL 代理作業中執行腳本

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Script\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log

腳本

Param(
   [parameter(Mandatory=$true)]
   [String]
   $DatabaseName,
   [parameter(Mandatory=$true)]
   [String]
   $NewDatabaseName,
   [parameter(Mandatory=$true)]
   [String]
   $SourceDir,
   [parameter(Mandatory=$true)]
   [String]
   $DataDir,
   [parameter(Mandatory=$true)]
   [String]
   $LogDir)

$file = $DatabaseName+".bak"
$fileFullPath = $SourceDir+"\"+$file
$fullDataFile = $DataDir+"\"+$DatabaseName+".mdf" 
$fullLogFile = $LogDir+"\"+$DatabaseName+"_log.ldf"
$ServerName = $env:computername
$server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $ServerName
$restore = New-Object 'Microsoft.SqlServer.Management.Smo.Restore'
$backupDeviceItem = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDeviceItem' ($fileFullPath, 'File')
$restore.Database = $NewDatabaseName
$restore.ReplaceDatabase = $true 
$restore.Devices.Add($backupDeviceItem)
$fileList = $restore.ReadFileList($server)
$dataFileNumber = 0
foreach ($file in $fileList) {
   $relocateFile = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile'
   $relocateFile.LogicalFileName = $file.LogicalName
   if ($file.Type -eq 'D') {
       if($dataFileNumber -ge 1) {
           $suffix = "_$dataFileNumber"
       }
       else {

       $suffix = $null;
       }
   $relocateFile.PhysicalFilename = "$DataDir\$DatabaseName$suffix.mdf"
   $dataFileNumber++
   }
   else {

       $relocateFile.PhysicalFileName = "$LogDir\$DatabaseName"+"_log.ldf"    
   }
   $restore.RelocateFiles.Add($relocateFile) | Out-Null
}
$restore.SqlRestore($server)
Exit

來自 PS 控制台的錯誤

New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Restore]: verify that the assembly containing this ty
pe is loaded.
At C:\Automation\RestoreDB_Script.ps1:32 char:12
+ $restore = New-Object 'Microsoft.SqlServer.Management.Smo.Restore'
+            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
   + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.BackupDeviceItem]: verify that the assembly containin
g this type is loaded.
At C:\Automation\RestoreDB_Script.ps1:33 char:21
+ ... eviceItem = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDevi ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
   + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

The property 'Database' cannot be found on this object. Verify that the property exists and can be set.
At C:\Automation\RestoreDB_Script.ps1:34 char:1
+ $restore.Database = $NewDatabaseName
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
   + FullyQualifiedErrorId : PropertyNotFound

The property 'ReplaceDatabase' cannot be found on this object. Verify that the property exists and can be set.
At C:\Automation\RestoreDB_Script.ps1:35 char:1
+ $restore.ReplaceDatabase = $true
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
   + FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At C:\Automation\RestoreDB_Script.ps1:36 char:1
+ $restore.Devices.Add($backupDeviceItem)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
   + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Automation\RestoreDB_Script.ps1:37 char:1
+ $fileList = $restore.ReadFileList($server)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
   + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Automation\RestoreDB_Script.ps1:67 char:1
+ $restore.SqlRestore($server)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
   + FullyQualifiedErrorId : InvokeMethodOnNull

來自 SQL 代理作業步驟的消息

Executed as user: domain\account. ...id not stop the script:  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'New-Object : The term 'New-Object' is not recognized as the name of a cmdlet, f  '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'unction, script file, or operable program. Check the spelling of the name, or i  f a path was included, verify that the path is correct and try again.  At C:\Scripts\RestoreDB_Script.ps1:31 char:11  + $server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $Ser ...  +           ~~~~~~~~~~'  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: '    + CategoryInfo          : ObjectNotFound: (New-Object:String) [], CommandN     '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: '   otFoundException    '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: '    + FullyQualifiedErrorId : CommandNotFoundException    '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: '     '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Restore]: ver    '  A job step r...  Process Exit Code 0.  The step succeeded.

使用 SQL Server PowerShell 中的一些資訊：如何使用 SMO 和 PowerShell 還原 SQL Server 數據庫，我能夠克服該錯誤。嘗試將以下內容添加到腳本的最頂部

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

引用自：https://dba.stackexchange.com/questions/183155