4

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

  • Say that the transaction involved adding a column to a table, or deleting a column from a table, or changing the data type of a field. What would you expect to happen when you undo that? – Brent Ozar Aug 25 '17 at 20:16
  • Reverse it back..so remove the column if it was added..change the data type back etc.. – SP1 Aug 25 '17 at 20:18
  • 1
    I use SQL Compare and SQL Data Compare for deployments like this. You can generate rollout and rollback scripts easily (by reversing the comparison direction) ahead of time so, in the event of a rollback, all you do is open the rollback script and hit F5. That only applies to fact/dimension tables though. If you're talking about transactional tables, you need to decide what you're going to do with any newly generated data since you migrated. Got any backups handy from before the deploy? – Kris Gruttemeyer Aug 25 '17 at 20:28
  • Thanks for your reply..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. – SP1 Aug 25 '17 at 20:30
  • 1
    I'd restore the backup that you took before making the changes (Which I'm sure you have, right! :) ) then run a comparison between the pre-deploy and post-deploy servers and sync them up. – Kris Gruttemeyer Aug 25 '17 at 20:45
7

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
| improve this answer | |
1

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.

| improve this answer | |

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.