SQL Server Is it possible to retrieve the CDC data that the cleanup job deleted?

xxhby3vn  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(83)

We are using CDC of a SQL Server database for the audit trail of an application.

Data capture was enabled and works correctly, however the retention of the cleanup job was too short and deleted relevant information from the audit trail.

Is there a way to recover the CDC data that was deleted?

If I understand correctly the MS documentation, data should be in the transaction logs:

https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

So I am guessing if it is possible to retrieve the changes from those logs. The recovery mode of the database is "full", the SQL Server are always on high availability.

PS: I am totally new to SQL Server

Adding some context: we are using a tool to model physical objects for monitoring manufacturing processes. Monitoring requires setting up the assets, events, notifications etc. We are required to have an audit trail for these models. The information about the models and the audit trail is a database in always on high availability SQL servers. Audit trail works via CDC.

von4xj4u

von4xj4u1#

CDC data is just like any other data in your database. Treat this as any other accidental deletion of data. Which is too say, restore the database to a separate location and extract what you need.

相关问题