Can I revert a transaction after a certain number of days 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
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 &amp; 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