Problems with RelocateFile property in the Restore-SqlDatabase cmdlet

Question

I trying to restore a database by using the Restore-SqlDatabase cmdlet. I need to relocate the files but I'm getting the following errror

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

My powershell code look like this

$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

Aileenailene

I think you should mention that this error happens when you upgrade from SQL 2012 to SQL 2014. I'll raise this bug in Connect also and let you guys know the link. – Dosser

Answer 1

This looks like a difference in the version of SMO that you have loaded and the one that Restore-SqlDatabase expects. There are probably two approaches here...

1. Make sure that the versions match.
2. Use the Microsoft.SqlServer.Management.Smo.Restore.SqlRestore method instead of the Restore-SqlDatabase cmdlet.

I have extracted the relevant pieces from a larger script below. It is untested in this form and there are a few variables such as $ServerName which are assumed to be available but it should be enough to get you going.

    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);

Octennial

Hey there - I have this issue, and I'm a bit of a powershell noob. How do I "make sure that the versions match"? – Olives

Answer 2

You can do this in a version-independent way:

$sqlServerSnapinVersion = (Get-Command Restore-SqlDatabase).ImplementingType.Assembly.GetName().Version.ToString()
$assemblySqlServerSmoExtendedFullName = "Microsoft.SqlServer.SmoExtended, Version=$sqlServerSnapinVersion, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

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

Expediential

Worked like a charm. Thanks – Demandant

Not sure why I just used 5 lines to pass a path, but thanks. I have looked for this for hours. – Idelia

Answer 3

For solution #1, you need to specify assembly qualified name when you instanciate relocate file to use correct assembly.

$RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "MyDB_Data", "c:\data\MySQLServerMyDB.mdf"
$RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList "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

Hope it helps !

Elope

Really helpful answer. Saved us a lot of time and chaos. This one should be "Marked as Answer". However, can you please tell where you found this syntax ? – Dosser

According to the Powershell documentation on New-Object, you pass the fully-qualified name of the class. The string used here is the original form of it, containing the version of the assembly to use, which is the key of the issue here. For example, by using the Powershell command used on the next answer, you can find the fully-qualified name of the relocatedfile class versions. <pre> [appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like "*smo*"} | foreach{$_.GetTypes()} | where{$_.Name -match 'relocatefile'} | select -Property AssemblyQualifiedName | Format-List</pre> – Elope

For Sql Server 2014, you have the change the Version number to "12.0.0.0". – Binky

Thank you! I spent 2 hours trying to figure this out. I think you can also load the assembly into the process space and simplify your new-object syntax... [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), then New-Object Microsoft.SqlServer.Management.Smo.RelocateFile – Islas

Answer 4

Used @Linhares solution except the Snapin assembly's version of 15.0.0.0 did not match the referenced Microsoft.SqlServer.SmoExtended version 15.100.0.0.

So tweaked this line to get the version directly from the referenced assembly.

$sqlServerSnapinVersion = ((Get-Command Restore-SqlDatabase).ImplementingType.Assembly.GetReferencedAssemblies() | ? { $_.Name -eq "Microsoft.SqlServer.SmoExtended" }).Version.ToString()

Aquamanile

This one did the trick! Thanks I was also banging my head against the wall ;) – Decern

Answer 5

I blogged about solving this issue by changing environment path variables. Please check http://powershelldiaries.blogspot.in/2015/08/backup-sqldatabase-restore-sqldatabase.html. As I mentioned above also, the answer by "Samuel Dufour" helped me. I just thought of an another way.

Dosser

Very interesting explanation, it helps to understand the issue in details. – Elope

This worked for me, though I don't think any of these options are ideal. – Sulfamerazine

Answer 6

I had the same problem on a build agent where no SQL Server and no Mangement Studio is installed. Only PS module "SqlServer" is available.

Just adding the following line at the beginning of the script solved the issue for me.

(Get-Command Restore-SqlDatabase).ImplementingType.Assembly

After that the assembly is loaded and all types can be used.

Standfast