Notable points to remember about pre-deployment and post-deployment scripts

• Pre and post deployment scripts must be re-runnable as they will be executed as part of every deployment. 
• Pre and post deployment scripts are deployed to every environment
• You need to provide your own error handling as desired in pre and post deployment scripts - the example below uses a TRY/CATCH pattern
• See the main page regarding ordering, transaction management, and more

When to use this pre-deployment script

When using External Data Sources objects containing the CREDENTIAL clause you may run into a problem with the verification and placement of objects in the target location. This is because SQL Change Automation will not script sensitive data, including credentials, by default.
We recommend that this script be used only for verification and build purposes.

In these scenarios either the shadow database (for verification) or the build database (for CI purposes) needs to be able to be created from an empty state.
We recommend that the credential used for these purposes which is not shared with any other database in another environment.
For this reason, the script contains a guard close using the built-in $IsShadowDeployment variable.
We recommend that credentials for non-verification databases be managed in an external process.

In the example below we use the MASTERKEY object which is necessary for the demonstration. It should be created with a strong password, individually for each solution.

IF (SELECT is_master_key_encrypted_by_server FROM sys.databases WHERE name = DB_NAME()) = 0
BEGIN
	CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
END
GO

Pre-deployment script to configure External Data Source Dependencies for Build and Verify.

In this example we use $(IsShadowDeployment) variable to allow us to perform migration script verification on shadow database only.
The following script is applicable to both on-premise SQL Server and Azure SQL Database.

IF '$(IsShadowDeployment)' = 1
	BEGIN

		PRINT N'Creating Master Key encryption.';
		IF (SELECT is_master_key_encrypted_by_server FROM sys.databases WHERE name = DB_NAME()) = 0
		BEGIN
			CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
		END

		PRINT N'Creating Database Scoped Credential.';
		IF NOT EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = 'csvCredentials')
		BEGIN
			CREATE DATABASE SCOPED CREDENTIAL csvCredentials
			WITH IDENTITY = 'secret', SECRET = 'secret';
		END

	END
GO