Setup.
- First enable Change tracking. Here’s some sample T-SQL you can execute to start the process
ALTER DATABASE SAMPLEDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON);
GO
After it’s enabled and you want to check on which databases are affected, run this T-SQL
SELECT DB_NAME(database_id) [mydbname]
FROM SYS.CHANGE_TRACKING_DATABASES;
GO
To
enable change tracking on a specific table use a query like this:
ALTER
TABLE dbo.MYTABLENAME
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);
GO
Now
to check the tables that have tracking enabled, you can get that list here:
SELECT
OBJECT_NAME(OBJECT_ID) [MYTABLENAME]
FROM SYS.CHANGE_TRACKING_TABLES;
FROM SYS.CHANGE_TRACKING_TABLES;
GO
No
changes are required to statements for insert or update statements that are
enabled with change_tracking.
To
find out what’s changed after enabled, you can run a query similar to the
following
SELECT
ct.ID, ct.SYS_CHANGE_OPERATION, c.[NAME]
FROM
CHANGETABLE(CHANGES dbo.MYTABLENAME, 0) ct
JOIN
dbo.MYTABLENAME c ON c.[ID] = ct.[ID];
GO
This comment has been removed by the author.
ReplyDeleteI see its deleted :( This comment has been removed by the author.
DeleteThanks for sharing helpful power-shell command, i tried this sql server changes tracking tool from https://www.netwrix.com/sql_server_auditing.html. This tool to track all changes happening in SQL server and get required information from a huge pile of data by different filtering options available and generate real-time alerts for suspicious changes.
ReplyDelete