Link to home
Start Free TrialLog in
Avatar of Shelwin Wei
Shelwin Wei

asked on

How can I capture deleted records in SQL Server 2005?

Hi experts,

I get a requirement to monitor deleted records in SQL server 2005. As I know, neither change tracking or change data capture is available in this version of SQL server. Any idea would be appreciated!

Best Regards,
Shelwin Wei
Avatar of Duy Pham
Duy Pham
Flag of Viet Nam image

Maybe you could use DML Triggers ON DELETE if you are allowed to modify databases. Also consider writing a generic script to iterate through all tables to create triggers for each if not exists.
Just a comment:

SQL Server 2005 is no longer supported by Microsoft. No security updates since 2016.

This is a serious security issue.

You should really consider updating to SQL Server 2017.
If you want to intercept deletion, you best add a trigger to the corresponding table.

If you want to query deleted records your database must be configured with bulk-logged or full in order to get details.

You can then query the deleted records like so:
USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation  = 'LOP_DELETE_ROWS'

Open in new window

This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.