Can I revert a transaction after a certain number of days

Question

I have a Sql script file which does a bunch of operations. I have transaction inside the script so if something happens the whole script is rolled back.

The question I have is that say the script executed successfully and while testing the application certain errors were found.

Now I want to revert all the operations that happened inside the script after say x days. This is a transactional db and I don't have to worry about any new data addition since this is just a staging environment..All I care about is to revert all the structural or data modifications that the SQL Script did by itself. Can someone please tell me what the options are for that.

Do I need to write the exact opposite statements in the rollback script for the operations that the original script did.

Example:
Main Script -Insert Into Customers values(1,'Test','USA') Rollback script - delete from Customers where id = 1 ..and so on for other transactions(I have simplified this I have over 300 operations in the SQL Script file)

Thanks

Thanks

Answer 1

There isn't anything built into SQL Server that will undo statements. Your options are:

• Restore from backup or from database snapshot
• Buy a third party development tool like Red Gate's SQL Compare & Data Compare - which may require creating another database, making your changes, and then comparing the differences between them
• Buy a log reader tool like Quest Litespeed - which can read Litespeed's transaction log backups and generate undo scripts for specific transactions
• Writing your own undo script - whatever changes you plan to make, write the undo scripts yourself to undo your changes

Answer 2

Database migration tools

You may be interested in database migration (a.k.a. schema migration) tools such as FlyWay or Liquibase.

Write a series of database modification scripts to be automatically applied as needed by the migration tool. Like version control for your code, but for your database.

Such a tool does not undo your changes. Instead you would start with a new database and apply all the DDL and DML statements in your scripts to recreate the database up to any point in it's evolution.

Fits your needs for testing/staging.

Flyway is Java-based but comes wrapped as command-line tools. Can be invoked from command-line, from your build tools and your integration tools, and from Java code.