Run a T-SQL script during image creation
Published 21 November 2017
SQL Clone version 2.4.0 can run scripts on image databases while they are being created.
Clone Agent will attach the image files directly to the SQL Server instance being used to create it (either the instance it came from for an image from SQL Server, or the temporary instance for an image from a backup file).
It will then execute your scripts via a low-privilege login and user it creates for the task, which will have the db_owner role on the image database, and since SQL Clone 2.5.2 also has VIEW ANY DEFINITION and VIEW SERVER STATE permissions on the server. The temporary user and login will be deleted after the scripts have run.
# Script to create a new SQL Clone data image with some additional post-image T-SQL run on the result
Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName WIN201601 -InstanceName SQL2014
$ImageDestination = Get-SqlCloneImageLocation -Path '\\red-gate\data-images'
$EmailRedactionScript = New-SqlCloneSqlScript -Sql "UPDATE Person.EmailAddress SET EmailAddress=N'removed@example.com'"
$PhoneRedactionScript = New-SqlCloneSqlScript -Sql "UPDATE Person.PersonPhone SET PhoneNumber=N'000-000-0000'"
$ImageOperation = New-SqlCloneImage -Name "AdventureWorks-$(Get-Date -Format yyyyMMddHHmmss)-PartiallyCleansed" `
-SqlServerInstance $SqlServerInstance `
-DatabaseName 'AdventureWorks' `
-Destination $ImageDestination `
-Modifications @($EmailRedactionScript, $PhoneRedactionScript)
Wait-SqlCloneOperation -Operation $ImageOperation
If you'd prefer to specify filenames, you can use New-SqlCloneSqlScript's -Path parameter instead of specifying raw T-SQL content with -Sql.
# Script to create a new SQL Clone data image with some additional post-image T-SQL, loaded from files, run on the result
Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName WIN201601 -InstanceName SQL2014
$ImageDestination = Get-SqlCloneImageLocation -Path '\\red-gate\data-images'
$MaskingScript = New-SqlCloneSqlScript -Path \\red-gate\data-scripts\mask-emails.sql
$PermissionsScript = New-SqlCloneSqlScript -Path \\red-gate\data-scripts\change-permissions.sql
$ImageOperation = New-SqlCloneImage -Name "AdventureWorks-$(Get-Date -Format yyyyMMddHHmmss)-PartiallyCleansedAndWithPermissionsChanges" `
-SqlServerInstance $SqlServerInstance `
-DatabaseName 'AdventureWorks' `
-Destination $ImageDestination `
-Modifications @($MaskingScript, $PermissionsScript)
Wait-SqlCloneOperation -Operation $ImageOperation
