How to Enable Change Data Capture(CDC) on a Database in SQL Server - SQL Server Tutorial

Scenario:

You are working as SQL Server DBA or developer, you need to write script that you can use to enable Change Data Capture (CDC) on any database.

The below script can be used to enable Change Data Capture (CDC) on any database, you simple have to provide database name to variable @DBName.

/*--------------------------------
1: Enable CDC ON Database
----------------------------------*/

DECLARE @DBName NVARCHAR(100)

DECLARE @Cdc_Status BIT

--> Provide your Database Name on which you want to Enable CDC
SET @DBName='TESTDB' 

SET @Cdc_Status=(SELECT is_cdc_enabled
                 FROM   sys.databases
                 WHERE  name = @DBName)
IF @Cdc_Status = 1
  PRINT ' CDC is already enabled on Database:: '
        + @DBName
IF @Cdc_Status = 0
  BEGIN
      DECLARE @SQL NVARCHAR(500)

      SET @SQL=@DBName + '.sys.Sp_cdc_enable_db'

      EXEC (@SQL)

      PRINT ' CDC Enabled on ' + @DBName + ' successfully'
  END