Rollback scripts

ergoface

I have been using SQL Compare for a long time. Lately I have started using it together with source control to do migration scripts.

Is there any way to have SQL compare create rollback scripts to reverse what a migration script does?

I know this wouldn't be possible for some types of changes, but for things like proc, constraint, view, and index modifications it would be wonderful, since I am required to have a rollback script for every production ddl modification script.

Thanks,

John Palmer

EDIT: I'm sorry, after rereading your question, I now realize you wanted to be able to reverse a migration script. Sorry I missed that detail initially. I'll leave my somewhat out of context answer in the hopes that it may prove useful to someone!

---

As you noted, this isn't possible for all types of changes, but I've taken to using this technique with good success:

1) Run your compare, select your objects and create your deployment script through the deployment wizard. I like to save it under a name similar to:

your_deploy_name.date.deploy.sql

2) After the wizard completes, you are dropped back in the compare screen. Do not change any selections!

3) Near the top of the compare screen, right-click the blue arrow between the two databases under compare and select 'Switch deployment direction.' The arrow changes to green and now points to the left. (Ctrl-D is a shortcut for this)

4) Rerun the compare wizard only this time name you file something simtlar to: your_deploy_name.date.rollback.sql

You now have two script files, one to deploy and one to backout.

As an extra verification step, I have TeamCity running locally and use it and various RedGate tools to:

1) Build a brand new image of our production database on our development server. 2) Run a compare to insure the two are identical 3) Apply the deployment script 4) Run a compare against source control. Either insure they're identcal or account for any differences. 5) Apply the rollback script. 6) Run another compare to insure we're again identical to production

msandico

Hi All,

Wondering if there was a built-in way to create a rollbak script now? I see the thread is a few months old so wondering if there were any product developments to support this,, OR if John's method above is the only way to go right now?