Fallback script for an INSERT/UPDATE script.

• Michel M.

  SSC Veteran

  Points: 212

  More actions

  June 15, 2012 at 8:41 am

  #260011

  Hi!

  => My problem

  Despite the XACT_ABORT statement in a specific script I've written my client asked me for a "fallback script".

  I've never heard the word "fallback"* before! I've never read something concerning fallback strategies.

  Important: I cannot restore any backup to a point before the execution of my script because it will certainly make me lose data in the production environment.

  => My questions:

  1) Does it exist a strategies for fallback after the execution of insert/update scripts?

  2) Does it exist objects in the database able to provide some helpful information in order to "undo" the data changes done by the script?

  => My scenario

  I use to insert and update data into tables by creating simple scripts under the following structure:

  USE mydb;

  GO

  SET XACT_ABORT ON;

  GO

  BEGIN TRAN T1;

  GO

  /* SQL statements */

  COMMIT TRAN T1;

  GO

  I'm developer and I never have access to the client's production database.

  Then I use XACT_ABORT set to ON for every script I create and put my statements in a transaction even when my script does work perfectly in my development database. This practice avoids a lot of problems that a partial execution of my scripts may cause.

  Ok, I'll try to create manually a set of statements in order to provide this "undo script" my client asked me today.

  However does someone have any sugestion of a good practice or a book concerning this issue?

  Thank you all in advance!

  * I'm not native English speaker but, as you can see, it's not a matter of semantincs!