Cannot bind RelocateFile when using Restore-SqlDatabase cmdlet

Question

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

I am writing a module to automate some common development tasks through Powershell. One of them is to simply copy the existing database into a new database.

I know that there are three different ways to back up and restore: executing SQL statements, SMO objects, and SQLPS cmdlets. I'm interested in the SQLPS route. Here is 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 tries to restore, I get the following error:

Restore-SqlDatabase: Unable to bind 'RelocateFile' parameter. I can not convert the value "Microsoft.SqlServer.Management.Smo.RelocateFile" enter "Microsoft.SqlServer.Management.Smo.RelocateFile" to enter the type "Microsoft.SqlServer.Management.Smo.RelocateFile".

This issue is also described here: Issues with the RelocateFile property in the Restore-SqlDatabase cmdlet

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

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

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

Answer 1

I could not find a good way to solve this as it is. As a result, I uninstalled the SQL Server installation and just installed SQL Server 2014.

Answer 2

I know this is old, but I ran into the same problem.

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" (logical name, physical name)

Running this method, with the full type name, solves the problem.

Hope this helps someone else who finds this question.

Answer 3

You can do this 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)