Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
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 Feb 4 '13 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 Feb 4 '13 at 22:17
    
Thanks again. Good point on the 'make it work like it used to'. –  Ali Razeghi Apr 3 '13 at 21:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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