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

Pre-deployment script to enable Change Data Capture

In this example, we use a pre-deployment script to enable Change Data Capture on shadow, build, and deployment target databases.

• We USE [$(DatabaseName)] to ensure we are working with the correct database (not the default database for the login)
• We check if CDC is enabled, and only act if it is not enabled
• We alter the database owner to sa
  • Although you are not required to have sa as the owner of a database to enable change data capture, SQL Server will attempt to validate the name of the database owner with the domain and in some situations the default database owner (the user who created the database) may cause an error when you execute sys.sp_cdc_enable_db.
  • You may choose to manage the database owner setting in its own pre-deployment script which runs before the CDC pre-deployment script.
• We execute sy.sp_cdc_enable_db to enable change data capture

SET XACT_ABORT, NOCOUNT ON;
GO
USE [$(DatabaseName)];
GO

IF (SELECT is_cdc_enabled FROM sys.databases WHERE name = DB_NAME()) = 0
BEGIN TRY

    EXEC ('ALTER AUTHORIZATION ON DATABASE::[$(DatabaseName)] TO [sa]');

	EXEC sys.sp_cdc_enable_db;

END TRY
BEGIN CATCH

    DECLARE @msg NVARCHAR(2048) = N'';

    IF @@trancount > 0
        ROLLBACK TRANSACTION;
    SET @msg = ERROR_MESSAGE();
    RAISERROR(@msg, 16, 1);

END CATCH;