How do I run 2 masking sets in powershell sql clone

glennsharrock

$ServerUrl = 'http://mycloneserver:14145' 

$MachineName = 'mymachine' 

$ImageLocation = '\\mymachine\Images' 

$DatabaseName = 'dbname' 

$ScriptPath = 'C:\Database\CloneSQL\db.sql' 

$MaskingSetPath1 = 'C:\Database\DataMaskerSet\Clone-NoXML.DMSMaskSet'

$MaskingSetPath2 = 'C:\Database\DataMaskerSet\Clone-XML.DMSMaskSet' 

$BackupFileName0 = '\\mybackupserver\backup.bak'

Connect-SqlClone -ServerUrl $ServerUrl

$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName $MachineName

$ImageDestination = Get-SqlCloneImageLocation -Path $ImageLocation

$MaskingSet1 = New-SqlCloneMask -Path $MaskingSetPath1

$MaskingSet2 = New-SqlCloneMask -Path $MaskingSetPath2

$SQLScript = New-SqlCloneSqlScript -Path $ScriptPath

$ImageOperation = New-SqlCloneImage -Name "$DatabaseName-$(Get-Date -Format yyyyMMdd)" `

    -SqlServerInstance $SqlServerInstance `

    -BackupFileName @($BackupFileName0) `

    -Destination $ImageDestination `

    -Modifications @($MaskingSet1, $SQLScript) 

Wait-SqlCloneOperation -Operation $ImageOperation

THIS WORKS Above, but when i add the 2nd set it fails (below)

$ServerUrl = 'http://mycloneserver:14145' 

$MachineName = 'mymachine' 

$ImageLocation = '\\mymachine\Images' 

$DatabaseName = 'dbname' 

$ScriptPath = 'C:\Database\CloneSQL\db.sql' 

$MaskingSetPath1 = 'C:\Database\DataMaskerSet\Clone-NoXML.DMSMaskSet'

$MaskingSetPath2 = 'C:\Database\DataMaskerSet\Clone-XML.DMSMaskSet' 

$BackupFileName0 = '\\mybackupserver\backup.bak'

Connect-SqlClone -ServerUrl $ServerUrl

$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName $MachineName

$ImageDestination = Get-SqlCloneImageLocation -Path $ImageLocation

$MaskingSet1 = New-SqlCloneMask -Path $MaskingSetPath1

$MaskingSet2 = New-SqlCloneMask -Path $MaskingSetPath2

$SQLScript = New-SqlCloneSqlScript -Path $ScriptPath

$ImageOperation = New-SqlCloneImage -Name "$DatabaseName-$(Get-Date -Format yyyyMMdd)" `

    -SqlServerInstance $SqlServerInstance `

    -BackupFileName @($BackupFileName0) `

    -Destination $ImageDestination `

    -Modifications @($MaskingSet1,$MaskingSet2, $SQLScript) 

Wait-SqlCloneOperation -Operation $ImageOperation

Jon_Kirkwood

Hi @glennsharrock

Thanks for reaching out on the Redgate forums regarding your Clone + Datamasking set question.

From immediate looking at your code it looks good, you can apply multiple masking sets via the -Modifications tag
(https://documentation.red-gate.com/clone3/automation/powershell-worked-examples/run-a-data-masker-masking-set-during-image-creation)

Can you confirm if your code runs when just calling the $MaskingSet2 variable - want to verify if both masking sets function individually 

Do you get any error/exit message when running the 2nd script? 
Also does the script run with both $MaskingSets but minus the $SQLScript variable?

Just wanting to pinpoint what might be occurring

You may also be able to implement some error handling in your script to get a better indication on where this is failing: 
https://documentation.red-gate.com/clone3/automation/powershell-cmdlet-reference/get-sqlclone/error-handling