Problems with RelocateFile property in the Restore-SqlDatabase cmdlet
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
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...
- Make sure that the versions match.
- 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);
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 !
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()