3

I manage a mixed DB environment and our programmers could use some good rollback options. I was thinking of using SQL Compare, Red Gate Source Control, and a few other tools but I can't figure out what the best way would be to ensure rollbacks with minimal data loss.

Assuming we're in edge cases such as when a foreign key is added to another table which gets populated with customer data, and we need to roll back changes to the primary table. What are some of the industry standard best practices to ensure smooth rollbacks outside of transaction log restores, snapshot restores, redgate source control, etc. to deal with these harder to recover from scenarios?

Thanks!

Improve this question

1 Answer 1

Reset to default
3

Well I think that having a test system that supports the workload, data and edge cases you have in production should mitigate the need to roll back. If you can properly test the deployment in a similar system, you should have very few scenarios where you have to roll back.

That said, you can also do many things to make your code changes backward compatible. I wrote a series of tips on this a while back, including one where I change a relationship:

http://www.mssqltips.com/sqlservertip/2074/make-your-sql-server-database-changes-backward-compatible-when-changing-a-relationship/

http://www.mssqltips.com/sqlservertip/2071/make-your-sql-server-database-changes-backward-compatible-when-renaming-an-entity/

http://www.mssqltips.com/sqlservertip/2050/make-your-sql-server-database-changes-backward-compatible-when-dropping-a-column/

http://www.mssqltips.com/sqlservertip/2035/make-your-sql-server-database-changes-backward-compatible-when-adding-a-new-column/

Every application is different, so you're not going to find many "industry standard" solutions to this. But you should be able to whip together plenty of common sense that will reduce or eliminate your need to worry about it.

And no, I'm not saying you shouldn't have a rollback plan, but when changes are properly planned and tested, the rollback plan shouldn't require a significant effort to devise, and should rarely - if ever - be used.

Improve this answer
3
  • Thanks @Aaron, I will read all of those and I'm sure pick up a few things. In this case, we had a deployment which we wanted to roll back as the customer requested it mid deployment. It's an edge case, but would make everyone much more comfortable, and I wanted to see if we could make our rollback options better.
    – Ali Razeghi - AWS
    Commented Feb 4, 2013 at 22:11
  • 1
    @Ali you can, but there's no magic bullet IMHO. You have to consider exactly what you're changing in the system and exactly what it means to "revert" - sometimes "rolling back" just means "make it behave the way it did before" not "undo exactly everything that you did."
    – Aaron Bertrand
    Commented Feb 4, 2013 at 22:17
  • Thanks again. Good point on the 'make it work like it used to'.
    – Ali Razeghi - AWS
    Commented Apr 3, 2013 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.