1

I have SQL Server 2014, standard edition. We have several tables where we delete data from, then re-insert it under different primary keys (to merge records for two people in our system that are actually the same). All these changes are performed with a T-SQL transaction.

I understand how transactions and rollbacks work, but what I need is more of an audit/rollback since my users may need to rollback just this transaction only at a later date (not restoring the whole database or table). "Change Data Capture" is not an option since I only have standard edition.

My real question lies in how to store this auditing information. I imagine I'll need a unique key to keep track of this being one unit of work so all these table changes get tied to same group as far as the user is concerned. But if I have a DELETE WHERE ID = @ID query for example, how do I store all these deleted records before deleting so that I can re-insert them later if needed? I'm fine with even storing a large rollback T-SQL script of some kind, I'm just not sure how to generate INSERT scripts that I can store and run later for data that I'm about to delete.

I'm open to any ideas, I just need an architecture that's generic enough to handle multiple tables and the ability to rollback deletions and insertions. I care more about the rollback ability than keeping a pretty audit table.

Andy
  • 1,243
  • 3
  • 22
  • 40
  • why don't you use another column to identify if the row needs to be deleted instead of actually deleting it? – Lamak May 17 '16 at 16:05
  • That is a good idea, but unfortunately my boss says we need to completely remove the records not flag them like you are suggesting :( – Andy May 17 '16 at 16:07
  • Furthermore, I need the ability to later delete records inserted or even change updated records back to what they were. Only specific records done within this transaction/batch...not restoring the whole table or DB back to a point in time. – Andy May 17 '16 at 16:08
  • I mean, the requirement sound kinda nuts....you need to completely remove the row, but you also need be able to retrieve it again on a later date?. Does your boss understands what "completely remove" something means? – Lamak May 17 '16 at 16:08
  • @Lamak Yes, haha...I know this requirement sounds nuts, but that's what they are insisting on. And it's not just deletion, but this transaction could insert or update as well. – Andy May 17 '16 at 16:10
  • I've worked at a place that had a similar requirement. What they implemented was using a trigger to write the deleted record to a history table. – SQLChao May 17 '16 at 16:12
  • Ok, well, it isn't possible. What your boss wants is to completely remove some data, but being able to retrieve it later (and the same with insert and update). If you have that ability it means that the previous data is still stored somehow, otherwise how could you retrieve it?. You can't just leave open transactions waiting to be rollbacked, – Lamak May 17 '16 at 16:12
  • @SQLChao hence, not "completely" removing it – Lamak May 17 '16 at 16:13
  • @lamak it sounded like he just had to remove it from the table and that it could be stored somewhere else. At least that's the way I read it. If not, my mistake. – SQLChao May 17 '16 at 16:14
  • Yes @SQLChao I just need to remove it from the table and store somewhere else for a possible rollback later. I tried looking into generating a big rollback T-SQL script that I could store too, but that also sounds like a pain. I understand using audit/history tables, I was just wondering if there's a good way so it handles multiple tables with different schemas generically, and a way I could extract this audit/history to reinsert/un-update the original tables. To me, storing the rollback T-SQL scripts sounds better, but I'm not sure how to generate them with data in them from a sp. – Andy May 17 '16 at 16:18
  • And of course none of this takes into account other aspects of the system that might have occurred as a result of the data that was deleted. If you are making other decision based on data that was no longer there but suddenly reappears how would you go about reverting those other data modifications? This whole process sounds like it is absurd with a boss who doesn't have a concept of how databases work. – Sean Lange May 17 '16 at 16:29
  • If your server is MS SQL 2016 then you can use *history table* feature https://msdn.microsoft.com/en-us/library/bb933994.aspx – Alex Kudryashev May 17 '16 at 18:15

1 Answers1

0

You can not do that out of the box as even with full logging you can roll back an entire database to a point in time but not specific transactions.

You will have to code something for un-doing transactions but I believe simple audit triggers will give you the data you need to make it happen. Here is a good article to get you started.

https://www.mssqltips.com/sqlservertip/4055/create-a-simple-sql-server-trigger-to-build-an-audit-trail/

Joe C
  • 3,925
  • 2
  • 11
  • 31