SQL Clone 4

SQL Clone Documentation
Automation
PowerShell worked examples
Run a T-SQL script during image creation

These pages cover SQL Clone 4, which is not the latest version. Help for other versions is also available.

Run a T-SQL script during image creation

Page last updated 05 January 2021

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.

Copy

##########################################################################################
# Create a new SQL Clone data image with a post-image T-SQL.ps1 - version 0.1
# Purpose: Script to create a new SQL Clone data image with some additional post-image 
#          T-SQL run on the result.
# Warning: 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).
##########################################################################################

$ServerUrl = 'http://sql-clone.example.com:14145' # Set to your Clone server URL
$MachineName = 'WIN201601' # The machine name of the SQL Server instance to create the clones on
$InstanceName = 'SQL2014' # The instance name of the SQL Server instance to create the clones on
$ImageLocation = '\\red-gate\data-images' # Point to the file share we want to use to store the image
$DatabaseName = 'AdventureWorks' # The name of the database

##########################################################################################

Connect-SqlClone -ServerUrl $ServerUrl
$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName $MachineName -InstanceName $InstanceName
$ImageDestination = Get-SqlCloneImageLocation -Path $ImageLocation

$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 "$DatabaseName-$(Get-Date -Format yyyyMMddHHmmss)-PartiallyCleansed" `
    -SqlServerInstance $SqlServerInstance `
    -DatabaseName $DatabaseName `
    -Destination $ImageDestination `
    -Modifications @($EmailRedactionScript, $PhoneRedactionScript)

Wait-SqlCloneOperation -Operation $ImageOperation

Create%20a%20new%20SQL%20Clone%20data%20image%20with%20a%20post-image%20T-SQL.ps11:31

View "Create a new SQL Clone data image with a post-image T-SQL.ps1" on GitHub

If you'd prefer to specify filenames, you can use New-SqlCloneSqlScript's -Path parameter instead of specifying raw T-SQL content with -Sql.

Copy

##########################################################################################
# Create a new SQL Clone data image with a post-image T-SQL, loaded from files.ps1 
# - version 0.1
# Purpose: Script to create a new SQL Clone data image with some additional post-image 
#          T-SQL, loaded from files, run on the result
# Warning: 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).
##########################################################################################

$ServerUrl = 'http://sql-clone.example.com:14145' # Set to your Clone server URL
$MachineName = 'WIN201601' # The machine name of the SQL Server instance to create the clones on
$InstanceName = 'SQL2014' # The instance name of the SQL Server instance to create the clones on
$ImageLocation = '\\red-gate\data-images' # Point to the file share we want to use to store the image
$DatabaseName = 'AdventureWorks' # The name of the database
$DropLargeTableScriptPath = '\\red-gate\data-scripts\drop-large-table.sql' # The path to a SQL script
$PermissionsScriptPath = '\\red-gate\data-scripts\change-permissions.sql' # The path to another SQL script

##########################################################################################

Connect-SqlClone -ServerUrl $ServerUrl
$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName $MachineName -InstanceName $InstanceName
$ImageDestination = Get-SqlCloneImageLocation -Path $ImageLocation

$DropBigTable = New-SqlCloneSqlScript -Path $DropLargeTableScriptPath
$PermissionsScript = New-SqlCloneSqlScript -Path $PermissionsScriptPath

$ImageOperation = New-SqlCloneImage -Name "$DatabaseName-$(Get-Date -Format yyyyMMddHHmmss)-PartiallyCleansedAndWithPermissionsChanges" `
    -SqlServerInstance $SqlServerInstance `
    -DatabaseName $DatabaseName `
    -Destination $ImageDestination `
    -Modifications @($DropBigTable, $PermissionsScript)

Wait-SqlCloneOperation -Operation $ImageOperation