restore-sqldatabase dynamic relocate files

• Jeff Shurak

  SSCrazy

  Points: 2361

  More actions

  November 30, 2017 at 9:48 am

  #341772

  I'd like to use restore-sqldatabase to automate a nightly refresh and am trying to dynamically pass values for the RelocateFile parameter by building an array:

  #Find the appropriate file locations for the move statements.
$NightlyDBFiles = Invoke-Sqlcmd -Query "select name,physical_name
from sys.master_files
where database_id = db_id('Database')" -ServerInstance 'Server'

  $RelocateFiles = @()
foreach($file in $NightlyDBFiles)
{
  $RelocateFiles += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($file.Name,$file.Physical_name)
}

  Restore-SqlDatabase -ServerInstance "Server" -Database "Database" -BackupFile $BackupFiles -RelocateFile @("$RelocateFiles")

  I receive the following error:

  Restore-SqlDatabase : Cannot bind parameter 'RelocateFile'. Cannot convert the "Microsoft.SqlServer.Management.Smo.RelocateFile
Microsoft.SqlServer.Management.Smo.RelocateFile Microsoft.SqlServer.Management.Smo.RelocateFile" value of type "System.String" to type
"Microsoft.SqlServer.Management.Smo.RelocateFile"

  It seems my array is coming through as an array and I'm not exactly sure how to insert the object vs strings.  Any thoughts? Is there a simpler way to accomplish this?

  Edit:

  If I call restore-sqldatabase like this:

  Restore-SqlDatabase -ServerInstance "Server" -Database "Database" -BackupFile $BackupFiles -RelocateFile $RelocateFiles

  I receive this 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".
  

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  November 30, 2017 at 4:10 pm

  #1970190

  That can be due to having different versions of SMO installed - did you see any of the posts on that? Changing the environment path variables is what I did before. Refer to the following about forcing the version or using the approach of changing the environment variables:
  Problems with RelocateFile property in the Restore-SqlDatabase cmdlet

  Sue

• Jeff Shurak

  SSCrazy

  Points: 2361

  More actions

  November 30, 2017 at 5:43 pm

  #1970203

  I did see that post.  The second answer worked for me only if created and passed them manually. I still could not dynamically create those objects and pass them.

• Jeff Shurak

  SSCrazy

  Points: 2361

  More actions

  December 1, 2017 at 9:58 am

  #1970317

  I resolved this with the following.  The restore-sqldatabase requires variables for the relocate files.  I was previously adding the variable contents directly to the array. By creating a variable, then adding that variable value directly to the array it worked.  It's kind of like a dog chasing its tail, but alas, it works.

     $i=1
    $RelocateFiles = @()
   foreach($file in $NightlyDBFiles)
   {
    New-Variable -Name "Relocate$i" -Value (New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList $file.Name,$file.Physical_name)
    $RelocateFiles += get-variable -Name "Relocate$i" -ValueOnly
    Remove-Variable -Name "Relocate$i"
    $i++
 
   }