restore-sqldatabase dynamic relocate files

  • 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".

  • 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

  • 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.

  • 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++
     
       }

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply