Cannot bind RelocateFile when using Restore-SqlDatabase cmdlet

Question

I have SQL Server 2012 Developer installed on my machine. I also have the SQL Server 2014 management objects installed, too, which is probably the source of the problem.

I'm writing a module to automate some common development tasks via Powershell. One of them is to simply copy an existing database to a new database.

I'm aware there are three different ways to backup and restore: executing SQL statements, SMO objects, and the SQLPS cmdlets. I'm interested in the SQLPS route. Here's my function:

push-location
import-module sqlps -disablenamechecking
pop-location

function Copy-Database {
    param (
        [string] $database,
        [string] $newDatabase
    )

    $backupFile = "$database-{0:yyyyMMddhhmmss}.bak" -f (get-date)
    backup-sqldatabase -serverinstance $defaultServerInstance -database $database -backupfile $backupFile -copyonly

    $solutionName = $newDatabase

    $mdf = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("MyDb_Data", "$defaultDatabaseRootPath\$solutionName\$newDatabase.mdf")
    $ldf = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("MyDb_Log", "$defaultDatabaseRootPath\$solutionName\$newDatabase.ldf")

    restore-sqldatabase -serverinstance $defaultServerInstance -database $newDatabase -backupfile $backupFile -RelocateFile @($mdf,$ldf)
}

It backs up the database, but when it attempts the restore, I get the following error:

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".

This problem is also described here: Problems with RelocateFile property in the Restore-SqlDatabase cmdlet

I accept that the issue may be a conflict in assemblies. The accepted answer offers two suggestions:

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

However, they only explain how to do #2. I want to know how to get this to work using the Restore-SqlDatabase cmdlet.

Answer 1

I know this is old, but I've come across the same issue.

PS C:\> [AppDomain]::CurrentDomain.GetAssemblies().GetTypes() | ? FullName -eq Microsoft.SqlServer.Management.Smo.RelocateFile | select Assembly

Assembly

• Microsoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

• Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" (*LogicalName*, *PhysicalName*)

Doing it that way, with a fully qualified type name, solves the issue.

Hopefully this will help someone else who finds this question.

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"

$mdf = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, $assemblySqlServerSmoExtendedFullName"('MyDb_Data', "$defaultDatabaseRootPath\$solutionName\$newDatabase.mdf")
$ldf = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, $assemblySqlServerSmoExtendedFullName"('MyDb_Log', "$defaultDatabaseRootPath\$solutionName\$newDatabase.ldf")

restore-sqldatabase -serverinstance $defaultServerInstance -database $newDatabase -backupfile $backupFile -RelocateFile @($mdf,$ldf)