--数据库上启用CDC
exec sp_cdc_enable_db
--CDCTest表上关闭CDC
exec sp_cdc_disable_table 'dbo','CDCTest',@capture_instance='all'
--CDCTest表上启用CDC
EXEC sp_cdc_enable_table 'dbo', 'CDCTest', @role_name = NULL, @supports_net_changes =1
--查询变更记录
declare @begin_lsn binary(10),@end_lsn binary(10)
select @begin_lsn=sys.fn_cdc_get_min_lsn('dbo_CDCTest')
select @end_lsn=sys.fn_cdc_get_max_lsn()
select * from cdc.fn_cdc_get_net_changes_dbo_CDCTest(@begin_lsn,@end_lsn,'all');
select * from MyTest.cdc.fn_cdc_get_all_changes_dbo_CDCTest(@begin_lsn,@end_lsn,'all');
--DDL变更查询
Select OBJECT_NAME(Source_Object_ID) As [Table Name],
OBJECT_NAME(Object_ID) As [CDC Table Name],
DDL_Command As [DDL Command],
DDL_LSN As [Log Sequence Number],
DDL_Time As [DateModified]
From CDC.ddl_history
几个问题:
--1.Enable后的所有列的值变更后 立刻能查到
--2.更改DDL,增加列后无法捕捉到
--3.使用Disable命令后,原有的跟踪信息被清除,同时可以记录下新的改动
求解:DDL变更后,如何能不禁用CDC而获得新增列的变更信息?
exec sp_cdc_enable_db
--CDCTest表上关闭CDC
exec sp_cdc_disable_table 'dbo','CDCTest',@capture_instance='all'
--CDCTest表上启用CDC
EXEC sp_cdc_enable_table 'dbo', 'CDCTest', @role_name = NULL, @supports_net_changes =1
--查询变更记录
declare @begin_lsn binary(10),@end_lsn binary(10)
select @begin_lsn=sys.fn_cdc_get_min_lsn('dbo_CDCTest')
select @end_lsn=sys.fn_cdc_get_max_lsn()
select * from cdc.fn_cdc_get_net_changes_dbo_CDCTest(@begin_lsn,@end_lsn,'all');
select * from MyTest.cdc.fn_cdc_get_all_changes_dbo_CDCTest(@begin_lsn,@end_lsn,'all');
--DDL变更查询
Select OBJECT_NAME(Source_Object_ID) As [Table Name],
OBJECT_NAME(Object_ID) As [CDC Table Name],
DDL_Command As [DDL Command],
DDL_LSN As [Log Sequence Number],
DDL_Time As [DateModified]
From CDC.ddl_history
几个问题:
--1.Enable后的所有列的值变更后 立刻能查到
--2.更改DDL,增加列后无法捕捉到
--3.使用Disable命令后,原有的跟踪信息被清除,同时可以记录下新的改动
求解:DDL变更后,如何能不禁用CDC而获得新增列的变更信息?