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.

Normally, you can just use the injected T-SQL variable directly, as shown in this Redgate product learning article. However, 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 in those scenarios, you need to build up the SQL dynamically in a variable and use EXEC or sp_executesql.

For example, to set the clone as the active database, you could do:

EXECUTE('USE ' + @SQLClone_CloneName)
GO
-- Do any operation as needed

The downside is that it opens the door to SQL injection attacks and can pose a security risk.

To reduce this risk you can employ the QUOTENAME() function which wraps the given parameter in square brackets automatically:

DECLARE @CLONEDB NVARCHAR(MAX);
SET @CLONEDB = N'USE ' + QUOTENAME(@SQLClone_CloneName);
EXECUTE(@CLONEDB);
GO
-- Do any operation as needed