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 & 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

StackExchange Database Administrators Q#184386, answer score: 7