Red Gate forums :: View topic - Rollback scripts
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Rollback scripts

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
ergoface



Joined: 16 May 2013
Posts: 1
Location: Wichita, KS

PostPosted: Thu May 16, 2013 5:21 pm    Post subject: Rollback scripts Reply with quote

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,
_________________
Dave Bennett
Back to top
View user's profile Send private message
John Palmer



Joined: 21 Jun 2012
Posts: 6

PostPosted: Sat May 18, 2013 6:15 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
msandico



Joined: 16 Aug 2012
Posts: 6

PostPosted: Thu Sep 05, 2013 5:16 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group