We have 11 tables for transactional data. Now, this data may have some data integrity issues. We have prepared a set of SQL scripts to fix the data integrity issues, which customer can execute from UI but some may need inputs from end-user.

In some cases, end-users comes back to us and asks us to revert the changes, because of functional impact, so we have 11 backup tables, which are same as transactional tables but without any constraints as we need to store corrupted data.

Question: My senior architect has proposed we change our design to use a single table for this purpose, and take backup in clob using dbms_xmlgen and persist in a CLOB/XMLtype. His main concern is the processing and maintenance overhead for these new tables, for example, an introduction to a new column to a transactional table will require the alteration in the backup table as well.

In my use case, 1. transactional table row count can be in millions, 2. at a time, we backup rows ranging from 1 to 100000 approximately. 3. Initially, we will be requiring to restore data in 5 % of cases, but eventually, it may go down nearer to zero. 4. Also, this is required in the initial time period, unless our scripts are proved to be error proof.

  • How long is the time window between script execution and end-users asking to revert changes? Have there been transactions against the database in this time window which should not be reverted? – cdkMoose Apr 9 '18 at 14:40
  • That is not a concern, if we have the data, we can revert back the changes. Time window might not be more than 4 weeks. – Tejas Apr 10 '18 at 11:31
  • 1
    I ask because that would rule out some options for backing up. If it could be 4 weeks later, I would assume you can't just back up the whole database and restore it, as you would lose all of the transactions in that time window. – cdkMoose Apr 10 '18 at 14:11
  • Yes, Also, the update can be frequent, and sometimes, it will be 1 field updated in the table. So backing up entire database everytime does not seem like a good idea. – Tejas Apr 10 '18 at 17:18

From your description, it looks like you are simply reinventing the backup while making it unnecessarily complex.


  • If your database supports it (Microsoft SQL Server does; don't know for others), use snapshots before performing the risky operations. The benefit of snapshots is that you can revert the changes at any moment if you found that you made a mistake, and both the creation of a snapshot and the revert itself take milliseconds, compared to minutes or hours for an ordinary backup.

  • Otherwise, rely on standard backups, making a backup just before the risky operation. Make sure you chose carefully between complete and incremental backups, since they both have benefits and drawbacks in terms of space used, time to create a backup, etc.

Both snapshots and standard backups can be automated. In essence, the customer will still see a button in the UI which allows to run your original scripts, preceded by a command which creates a snapshot or a backup. A second button will allow the customer to revert to the original state if something goes wrong.

  • Okay, we also need to keep track of which rows the program modified, and delta is easier to derive if we have only those rows copied. also, in most of the scenarios, we will be updating only 1 few rows out of million rows, so taking an entire table/DB backup each time might not serve us well – Tejas Apr 10 '18 at 11:33
  • 1
    @Tejas: and this is exactly why I suggested to do snapshots. – Arseni Mourzenko Apr 10 '18 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.