powershell - Restore-SqlDatabase cmdlet中RelocateFile属性的问题

我试图通过使用Restore-SqlDatabase cmdlet还原数据库。我需要重定位文件，但出现以下错误

Restore-SqlDatabase : Cannot bind parameter 'RelocateFile'. Cannot convert the
"Microsoft.SqlServer.Management.Smo.RelocateFile" value of type
"Microsoft.SqlServer.Management.Smo.RelocateFile" to type
"Microsoft.SqlServer.Management.Smo.RelocateFile".
At line:25 char:108
+ ... e -RelocateFil $RelocateData
+                    ~~~~~~~~~~~~~
+ CategoryInfo          : InvalidArgument: (:) [Restore-SqlDatabase], ParameterBindingException
+ FullyQualifiedErrorId CannotConvertArgumentNoMessage,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

我的PowerShell代码看起来像这样

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("MyDB_Data", "c:\data\MySQLServerMyDB.mdf")
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("MyDB_Log", "c:\data\MySQLServerMyDB.ldf")
$file = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($RelocateData,$RelocateLog)
$myarr=@($RelocateData,$RelocateLog)
Restore-SqlDatabase -ServerInstance DEV\DEMO -Database "test" -BackupFile $backupfile -RelocateFile $myarr

最佳答案

这看起来像您已加载的SMO版本与Restore-SqlDatabase所期望的版本有所不同。这里可能有两种方法...

确保版本匹配。

使用Microsoft.SqlServer.Management.Smo.Restore.SqlRestore方法代替Restore-SqlDatabase cmdlet。

我从下面的一个较大的脚本中提取了相关片段。它未经这种形式的测试，有一些变量，例如$ ServerName，它们假定是可用的，但足以使您顺利进行。

    if($useSqlServerAuthentication)
    {
        $passwordSecureString = ConvertTo-SecureString -String $password -AsPlainText -Force;

        $serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection $ServerName, $UserName, $passwordSecureString;

        $server = new-object Microsoft.SqlServer.Management.Smo.Server $serverConnection;
    }
    else
    {
        $server = new-object Microsoft.SqlServer.Management.Smo.Server $ServerName;
    }

    $dataFolder = $server.Settings.DefaultFile;
    $logFolder = $server.Settings.DefaultLog;

    if ($dataFolder.Length -eq 0)
    {
        $dataFolder = $server.Information.MasterDBPath;
    }

    if ($logFolder.Length -eq 0)
    {
        $logFolder = $server.Information.MasterDBLogPath;
    }

    $backupDeviceItem = new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $Path, 'File';

    $restore = new-object 'Microsoft.SqlServer.Management.Smo.Restore';
    $restore.Database = $DatabaseName;
    $restore.Devices.Add($backupDeviceItem);

    $dataFileNumber = 0;

    foreach ($file in $restore.ReadFileList($server))
    {
        $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 = "$dataFolder\$DatabaseName$suffix.mdf";

            $dataFileNumber ++;
        }
        else
        {
            $relocateFile.PhysicalFileName = "$logFolder\$DatabaseName.ldf";
        }

        $restore.RelocateFiles.Add($relocateFile) | out-null;
    }

    $restore.SqlRestore($server);