用户怀疑系统数据经常性变化,要求我们验证,之前没有对数据库进行过任何设置,以前的情况不想在查了,只能现在开始监视,于是网上看到了sqlserver 2008的新功能,正好可以用上,把网上具有借鉴的整合到一起,供快速设置。
要捕获某个表的变更,必须先开启数据库级别的捕获,再开启表级的捕获
- 启用数据库:在当前数据库下执行命令
USE financetrade;
GO
其中financetrade是数据库名称。
EXEC sys.sp_cdc_enable_db
这个过程会在当前数据库下创建6个系统表
cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
dbo.systranschemas
2查询哪些数据库启用了CDC功能
select * from sys.databases where is_cdc_enabled = 1
3启用表,例如表名为”dbo.t1”
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 't1',
@role_name = null;
注:为第一个表启用CDC后,SQLServer生成两个Agent作业
cdc.dbname_capture
cdc.dbname_cleanup
4. 查看表是否启用了CDC
select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('dbo.t1')
5禁用表(“dbo.t1”)
EXEC sys.sp_cdc_disable_table
@source_schema = 'dbo',
@source_name = 't1',
@capture_instance = 'all';
6 禁用数据库
EXEC sys.sp_cdc_disable_db;
7一些异常情况的处理
(1) 数据库从2005恢复到2008,打开表级别的附加日志报错
未针对数据库 'AdventureWorks2014' 启用变更数据捕获。
请确保已设置正确的数据库上下文,然后重试该操作。若要报告数据库已启用变更数据捕获这一情况,
请查询 sys.databases 目录视图中的 is_cdc_enabled 列.
解决办法:
ALTER AUTHORIZATION ON DATABASE::[数据库名] TO [sa]
相关说明:
我们可以来解释一下上面结果的含义:
__$operation=2的情况,表示新增
__$operation=3或者4,表示更新,3表示旧值,4表示新值
__$operation=1的情况,表示删除
很好理解,不是吗?
但是,我们一般都是需要按照时间范围进行检索,对吧,所以,需要使用下面的语法进行查询
DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)
DECLARE @start_time DATETIME = '2011-8-10 00:00:00'
DECLARE @end_time DATETIME ='2011-8-11 00:00:00'
SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)
SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)
SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')