PowerShell:无法将类型SMO.server转换为类型SMO.server

Question

我从PowerShell收到错误：

Cannot convert argument "srv", with value: "[DBADEV\SQL2008r2]", for "SqlBackup" to type "Microsoft.SqlServer.Management.Smo.Server": "Cannot convert the "[DBADEV\SQL2008r2]" value of type 
"Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server"."
At line:23 char:1
+ $backup.SqlBackup($srv)
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

我正在尝试编写一个PowerShell脚本，将数据库从我们的生产箱恢复到DBADEV箱中。下面是我正在使用的代码，它会产生错误。

#Clear Screen
cls

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$ErrorActionPreference = "Inquire"

# Restore [SQLSRV2k8-0102\SQL2008] instance

$BackupFile = Get-ChildItem "\\NetworkShare\r`$\MSSQL10.SQL2008\MSSQL\Backup\AdventureWorks2008r2" | select -Last 1

$BackupFile

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server("DBADEV\SQL2008r2")
$res = New-Object Microsoft.SqlServer.Management.Smo.Restore
$backup = New-Object Microsoft.SqlServer.Management.Smo.Backup

$Backup.Devices.AddDevice($BackupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$Backup.Database = "AdventureWorks2008r2"
$Backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$backup.Initialize = $true
$backup.SqlBackup($srv)
$srv.Databases["AdventureWorks2008r2"].Drop()

$res.Devices.AddDevice($BackupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$res.Database = "AdventureWorks2008r2"
$res.NoRecovery = $true
$res.SqlRestore($srv)

这个错误对我来说似乎有点神秘(就像大多数PowerShell错误一样)。对于为什么会发生这种情况，有什么想法吗？当我在这里使用Mike Fal的powershell脚本时，我也得到了这个错误：http://www.mikefal.net/2014/07/22/restoreautomation-powershell-module/

通过将"DBADEV\SQL2008r2“直接传递给SQLRestore (即$res.SqlRestore("DBADEV\SQL2008r2") instead of $res.SqlRestore($srv) )，似乎可以克服这个错误

现在会生成一个错误，指出"Restore failed for Server 'DBADEV\SQL2008r2‘“

Answer 1

根据我的经验，这种错误是由于变量中错误的名称/数据类型造成的。

我会尝试在"DBADEV\SQL2008r2“中转义\

或者尝试我在MSDN上找到的

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
Write-Host $srv.Information.Version

所以它可能看起来像

New-Object Microsoft.SqlServer.Management.Smo.Server("(DBADEV\SQL2008r2)")