SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


restore-sqldatabase dynamic relocate files


restore-sqldatabase dynamic relocate files

Author
Message
Jeff Shurak
Jeff Shurak
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1753 Visits: 831
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
Sue_H
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49447 Visits: 12347

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
Jeff Shurak
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1753 Visits: 831
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
Jeff Shurak
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1753 Visits: 831
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++

}

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search