As of version 2.4.0, you can provide SQL Clone with T-SQL scripts to run during image creation.

Once SQL Clone has created the image - but before it makes it available for cloning - the working agent will run your scripts on the image database. This will not affect the source database.

Implementation notes

To run the scripts, the agent will temporarily attach the image database to a SQL Server instance. See our documentation on where the image will be attached.

The scripts will be run with limited privileges - as a temporary user which only has permission to modify the image as db_owner, not to make changes to the server or to other databases.

The database will be attached with a randomized temporary name when performing image modifications. Using three-part names containing the source database name when making modifications to an image will fail, as this refers to another database on the instance (possibly the source database itself) rather than the image being created. You can use DB_NAME() to get the temporary name of the database in scripts if required.

Multiple batch scripts (i.e. containing GO statements) are supported from version 2.4.2.

Injected variables

As of version 2.5.2, SQL Clone automatically sets up some SQL variables that can be used by any T-SQL script modification during image creation.

• @SQLClone_ImageName contains the name of the image being created (e.g. 'MaskedProduction').
• @SQLClone_MachineName contains the name of the machine hosting the agent that is creating the image (e.g. 'AGENTMACHINE01').
• @SQLClone_UserName contains the username of the user who created the image (e.g. 'DOMAIN\Username').
• @SQLClone_OriginDatabaseName contains the name of the database that the image was created from (e.g. 'Production') (version 2.6.2 onwards).

For example, you might change metadata on the database with:

UPDATE dbo.Metadata SET MyData=@SQLClone_ImageName

If there is any other information you would find useful as an auto-injected SQL variable, let us know.

Statements that have to be run in their own query batch, such as CREATE PROCEDURE and ALTER PROCEDURE, don't have these variables injected, as SQL Server doesn't support passing in variables for these statements. If you want to use the injected variables, build up the SQL in a variable and use EXEC sp_executesql.