Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Problems with RelocateFile property in the Restore-SqlDatabase cmdlet

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
like image 212
Lars H Avatar asked Oct 15 '14 07:10

Lars H


2 Answers

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
like image 137
Rosberg Linhares Avatar answered Oct 23 '22 05:10

Rosberg Linhares


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 !

like image 19
Samuel Dufour Avatar answered Oct 23 '22 05:10

Samuel Dufour