/******* 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