Sql-server – Rolling Back DB Changes After Deployment Edge Cases

deploymentrollbacksql server

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!

Best Answer

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.

Related Solutions

Sql-server – Visual Studio 2010 deployment script creates objects in wrong order – trigger after stored procedure

You may have run into a known issue with Visual Studio 2010:

That said, Microsoft recommends you help Visual Studio detect the object dependencies it seems to be ignoring in its deployment script by:

schema-qualify[ing] the names of objects that are involved in dependent relationships.

So if everything you are developing sits in the dbo schema, make sure to prepend the proc and view names giving you trouble with their schema name. For example: dbo.MyView instead of just MyView. Visual Studio has a "Fully Qualify Name" feature under the Refactor menu when you right-click on an object that'll help you do that very quickly.

If that doesn't help, give Google a spin and take a look at some of the workarounds suggested on the pages linked above. Let us know what ended up working for you.

If an ‘after’ DDL trigger causes an error, is the DDL rolled back

The answer, at least on 11.2, is "It depends":

This create is rolled back:

create trigger trig_foo after create on schema
begin
  raise_application_error(-20001, 'Dont do it!');
end;
/
--
create table foo as select level as id from dual connect by level<=10000;
/*
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Dont do it!
*/
--
select count(*) from foo;
/*
SQL Error: ORA-00942: table or view does not exist
*/

But this truncate is not:

create table foo as select level as id from dual connect by level<=10000;
--
create trigger trig_foo after truncate on schema
begin
  raise_application_error(-20001, 'Dont do it!');
end;
/
--
truncate table foo;
/*
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Dont do it!
*/
select count(*) from foo;
/*
COUNT(*)               
---------------------- 
0                  
*/

Best Answer

Related Solutions

Sql-server – Visual Studio 2010 deployment script creates objects in wrong order – trigger after stored procedure

If an ‘after’ DDL trigger causes an error, is the DDL rolled back

Related Question