easy tool to capture data changes
-
Hi,
Is it possible to enable CDC on all tables in the database ?
also is it possible to find which tables are changed ? and find changes from them.
How to do this ?
Thanks
-
spectra (7/27/2014)
Hi,Is it possible to enable CDC on all tables in the database ?
also is it possible to find which tables are changed ? and find changes from them.
How to do this ?
Thanks
This can be done, check Features Supported by the Editions of SQL Server what is available for your SQL Server Edition.
Suggest you have a look at these pages and come back if you have any questions
😎
About Change Tracking (SQL Server)
-
Eirikur Eiriksson (7/27/2014)
This can be done, check Features Supported by the Editions of SQL Server what is available for your SQL Server Edition.
Suggest you have a look at these pages and come back if you have any questions
😎
About Change Tracking (SQL Server)
I am getting more confused in those jungles of links and pages ....I did not get you.
Its SQL Server 2008 R2
I am looking for a way to put CDC on database level ( not on individual table) so that I can track down two things ..
1. which tables got affected
2. what are the data changes ( insert/update/delete)
If there is any tool which does this and make life simpler ....please let me know.
alternatively ... Can you be please be specific what to look at to give it a try ?
thanks
-
spectra (7/27/2014)
Eirikur Eiriksson (7/27/2014)
This can be done, check Features Supported by the Editions of SQL Server what is available for your SQL Server Edition.
Suggest you have a look at these pages and come back if you have any questions
😎
About Change Tracking (SQL Server)
I am getting more confused in those jungles of links and pages ....I did not get you.
Its SQL Server 2008 R2
I am looking for a way to put CDC on database level ( not on individual table) so that I can track down two things ..
1. which tables got affected
2. what are the data changes ( insert/update/delete)
If there is any tool which does this and make life simpler ....please let me know.
thanks
I'll try to be more clear;-)
The Sql Server Edition is more important in this scope as it determines which features are supported, hence the first link in my post
Features Supported by the Editions of SQL Server
The other three links are to Microsoft documentation on Change Tracking
About Change Tracking (SQL Server)
How to: Use SQL Server Change Tracking
If you have a Datacenter or Enterprise Edition of SQL Server 2008 R2, you can use Change Data Capture otherwise you will probably have to use Change Tracking.
😎
-
We are deviating from the solution.
The Sql Server Edition is more important
its a SQL Server 2008 General sub forum ...right ? version is of course Sql Server 2008 ! ....and so I posted in this sub forum.
There should not be any confusion....you may have not read my query (CDC) properly in the beginning.
Anyway ...I guess change tracking is not applicable here as you pointed out ...I require CDC ...that was my original query in the very first post.
Could you please look at the query again and let me know how to go about this ?
-
spectra (7/27/2014)
We are deviating from the solution.The Sql Server Edition is more important
its a SQL Server 2008 General sub forum ...right ? version is of course Sql Server 2008 ! ....and so I posted in this sub forum.
There should not be any confusion....you may have not read my query (CDC) properly in the beginning.
Anyway ...I guess change tracking is not applicable here as you pointed out ...I require CDC ...that was my original query in the very first post.
Could you please let me know how to go about this ?
Let me clarify this; Each version of SQL Server, that is SQL Server 2008 R2, SQL Server 2012 and so on, comes in a different flavour called Edition. There is a common code base for each version but there is a difference in supported features between Editions.
Now going back to your original question "Is it possible to enable CDC on all tables in the database ?", the answer is yes, you only have to create a script that alters all tables, the command for each table is
ALTER TABLE [schema_name].[table_name]ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
This is only supported in Datacenter and Enterprise Edition of SQL Server 2008 R2.
😎
-
I am not quite sure whether its called Enterprise version or not.
Could you please have a look at this ...
Can I use your script in this database ?
-
Sorry, should have included this in the previous post:-)
😎
Run this query
SELECT @@VERSIONThe last line of the output will tell you, here is an example
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
-
Yes ...I run that query and I get output which prints "Enterprise Edition". 🙂
Thank you .
Now I can use your script.
However i am worried at one place... Do I need to write this way 100 times ? because I have 100 tables ...I guess you mean this..
ALTER TABLE [schema_name].[table_name1]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE [schema_name].[table_name2]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE [schema_name].[table_name3]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE [schema_name].[table_name4]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
.....................................
.....................................
ALTER TABLE [schema_name].[table_name100]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
Ok ..so far its good .....now after executing this script...how do I view which table's data has been been changed ?
I just want to see which table got modified and what are the changes. what to do for this ?
-
Before going further, I recommend that you read through the documentation, see this link: Change Data Capture and then come back with any questions you may have. It will take an hour or two to read but it is well worth it.
😎
-
Simple Talk suggests this:
USE [DataBaseName]GO
EXEC sys.sp_cdc_enable_db
GO
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply