Rolling Back DB Changes After Deployment Edge Cases 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!
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.