0

I am working on automating a process and learning PowerShell along the way. One piece of this task is to take a SQL Server database backup from a prod server and restore to a DR server. I have already gotten this to work when there are one or two datafiles + the log file, but I want this to work no matter the number of data files so I started rewriting.

I started with this on my local machine, backup and restore local. Everything works.

$dbfiles = Invoke-Sqlcmd -ServerInstance "XXX\SQLEXPRESS" 
                 -Query "RESTORE FILELISTONLY FROM DISK='$BackupFile'"

#Loop through filelist files, replace old paths with new paths

foreach($dbfile in $dbfiles) {
  $DbFileName = $dbfile.PhysicalName | Split-Path -Leaf

  if($dbfile.Type -eq 'L') {
    $newfile = Join-Path -Path $NewLogPath -ChildPath $DbFileName
  } else {
    $newfile = Join-Path -Path $NewDataPath -ChildPath $DbFileName
  }

  $relocate += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile 
($dbfile.LogicalName,$newfile)
}

Next I needed this to work in the method described above utilizing two servers. I knew I would need the added code to make sure I wouldn't get errors regarding different versions of SMO, but I can't get it to work like in my previous version of the script which had a known number of data files.

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

My gut is telling me that specifying the version isn't getting passed along into the array.

Any thoughts on how I can fix this? TIA!

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

  $Relocate = @(New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, 
$assemblySqlServerSmoExtendedFullName"($dbfile.LogicalName, $newfile))


$DBfileList = Invoke-Sqlcmd -ServerInstance $DRServer -Query "RESTORE 
FILELISTONLY FROM DISK='$BUFile'"







 foreach($dbfile in $dbfileList){

  $DbFileName = $dbfile.PhysicalName | Split-Path -Leaf

  if($dbfile.Type -eq 'L') {
    $newfile = $LogPath  + $DbFileName
  } else {
    $newfile =  $DataFilePath + $DbFileName
  }

  $relocate += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile 
($dbfile.LogicalName,$newfile)
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bob Sacamano
  • 1
  • Check this link: https://stackoverflow.com/questions/26377356/problems-with-relocatefile-property-in-the-restore-sqldatabase-cmdlet – CR241 Oct 02 '18 at 21:25
  • A suggestion for you to consider is using the open source toolkit called dbatools.io. It is written in powershell and does all the fancy stuff for you. I know it might not give you the same learning experience, but learning to use other tools can save you from a lot of headache in the long run. – Mötz Oct 03 '18 at 07:39

0 Answers0