/******* Step1:创建示例数据库*******/
USE master
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'CDC_DB')
DROP DATABASE CDC_DB
GO
CREATE DATABASE CDC_DB
GO

/******* Step2:开启数据库CDC *******/
--查看数据库是否启用CDC
SELECT name,is_cdc_enabled FROM sys.databases WHERE name = 'CDC_DB'

--启用数据库CDC
USE CDC_DB
GO
EXECUTE sys.sp_cdc_enable_db;
GO

--检查启用是否成功
SELECT is_cdc_enabled,CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述
FROM sys.databases
WHERE NAME = 'CDC_DB'

/******* Step3:对表启用变更捕获*******/
--创建测试表
USE CDC_DB
GO
CREATE TABLE [dbo].[Department](
    [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](200) NULL,
    [GroupName] [nvarchar](50) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    [AddName] [nvarchar](120) NULL,
 CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED 
(
    [DepartmentID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

--对表启用捕获
EXEC sys.sp_cdc_enable_table 
    @source_schema= 'dbo',
       @source_name = 'Department',
       @role_name = N'cdc_Admin',
       @capture_instance = DEFAULT,
       @supports_net_changes = 1,
    @index_name = NULL,
    @captured_column_list = NULL,
    @filegroup_name = DEFAULT

--检查是否成功
SELECT name, is_tracked_by_cdc ,
    CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述
FROM sys.tables
WHERE OBJECT_ID= OBJECT_ID('dbo.Department')

--返回某个表的变更捕获配置信息
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'

/******* Step4:测试DML变更捕获*******/
--测试插入数据
INSERT  INTO dbo.Department(
    Name ,
    GroupName ,
    ModifiedDate
)VALUES('Marketing','Sales and Marketing',GETDATE())

--测试更新数据
UPDATE dbo.Department SET Name = 'Marketing Group',ModifiedDate = GETDATE()
WHERE Name = 'Marketing'

--测试删除数据
DELETE FROM dbo.Department WHERE Name='Marketing Group'

--查询捕获数据
SELECT * FROM cdc.dbo_Department_CT

/******* Step6:使用LSN 查看CDC记录*******/
--http://msdn.microsoft.com/zh-cn/library/bb500137%28v=sql.100%29.aspx
SELECT sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', '2013-07-24 09:00:30') AS BeginLSN

SELECT sys.fn_cdc_map_time_to_lsn
('largest less than or equal', '2013-07-24 23:59:59') AS EndLSN


/******* 查看某时间段所有CDC记录*******/
DECLARE @FromLSN binary(10) =
sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal' , '2013-06-23 09:00:30')

DECLARE @ToLSN binary(10) =
sys.fn_cdc_map_time_to_lsn
('largest less than or equal' , '2013-07-26 23:59:59')

SELECT CASE [__$operation]
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
END Operation,[__$operation],[__$update_mask],DepartmentId,Name,GroupName,ModifiedDate,AddName
FROM [cdc].[fn_cdc_get_all_changes_dbo_Department]
(@FromLSN, @ToLSN,  N'all update old')
/*
all 其中的update，只包含新值
all update old 包含新值和旧值
*/

/******* Step5:维护CDC *******/
--返回所有表的变更捕获配置信息
EXECUTE sys.sp_cdc_help_change_data_capture;

--返回某个表的变更捕获配置信息
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'

--查看对某个表的哪些列做了捕获监控,使用上面返回的capture_instance列值
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'dbo_Department'

--所有数据库CDC Job信息
SELECT B.name,A.* FROM msdb.dbo.cdc_jobs AS A
LEFT JOIN sys.databases AS B
ON A.database_id = B.database_id

--当前数据库CDC Job信息
EXEC sp_cdc_help_jobs