SQL Compare is a core product from Redgate and I’ve got a series on some of the interesting things I’ve found. Download a trial today if you haven’t tried it.
There’s a neat switch in SQL Compare that lets you build rollback scripts. It looks like this:
I’ve used this before to help with deployments, as many of you have. The idea is that when you finish with your code in your dev environment, you run a comparison with production, doing something like this:
I’ve set the source as my development place, and the destination as production. I click “Compare Now”, get a script that will deploy the changes from development to production, and save it. I can use that script as part of my deployment process.
However as soon as I’ve saved that script, I return to this screen, and I click the “Switch” button at the bottom.
If you look closely, you’ll see that my source (on the left) and destination (on the right), have changed places. This means I’ll now generate a script that takes my production environment and generates the script to get back production from development.
This is a rollback script.
It doesn’t work in all situations, and you really have to think about what you’re changing, but if you’re just doing views/procedures/functions, this is a great way to get that quick rollback script that you store alongside the deployment script in case things go badly.