Fast Project Rollbacks

  • Comments posted to this topic are about the item Fast Project Rollbacks

  • Very informative article. Well written...

  • In the code that generates the DROP CONSTRAINTS for the foreign keys, I can't get this to work unless I change rkeyid in the WHERE clause into fkeyid, which kinda makes sense since that is used in the OBJECT_NAME function.

    Is this a mistake?

    Mike

  • just buy a tool... :hehe:

  • Some very nice techniques and ideas in the article

    But I have never rolled back a change to a production database in 10 years.

    Your energy would be much better spent ensuring that deployment does not *need* to be rolled back.

    And being ready to fix any minor issues that crop up ...

    If you are not 100% sure deployment will succeed - do not deploy.

  • Nice article! A suggestion to improve readability. If you have a table valued function which can split a delimited string then you could do the following at the top of the script:

    DECLARE @Tables VARCHAR(MAX), @Views VARCHAR(MAX), @Procedures VARCHAR(MAX)

    SELECT

    @Tables = 'Table1,Table2,Table3',

    @Views = 'View1,View2,View3',

    @Procedures = 'Procedure1,Procedure2,Procedure3'

    Then your WHERE clause would just look something like this

    WHERE name IN (SELECT Value FROM dbo.SplitString(@Tables))

    This would be more readable (you only have to look one place for which objects will be affected). If you don't like the TVF suggestion you could just declare table variables and insert the names in and your WHERE clause would just reference the table variable instead.

  • How do you handle objects (especially tables or columns) that were removed as part of a deployment? How does the rollback script get them back? Do you never actually delete any data?

  • this if fine for views and sp's but

    if you change an object

    drop a field or drop a table

    change foriegn keys this isnt going to be friendly

  • Interesting article, David, thanks for writing it, I'm going to need to devote some time to studying and playing with the code. I've never had to script out updates like this, but I can definitely see it as a good practice. I like the way you get things into the log, I can see using that in other processes that I currently have running.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • I really enjoyed the article and will refer back to the code snips later for sure. I'm awfully surprised by the people who 1) don't think a rollback plan is necessary (it must be nice to be perfect!) and 2) would rather buy their way out of the necessity to code. Unfortunately, I've never met a tool that would address a full migration rollback. I'm interested to know what tool we are referring to.

    As for the what abouts... some of these will simply require different coding techniques, others, such as a dropped column, may require partial restores, but I agree with the author that a full restore of a production environment to undo a deployment should never be your entire plan.

    😎 Kate The Great :w00t:
    If you don't have time to do it right the first time, where will you find time to do it again?

  • doobya (10/22/2008)


    Some very nice techniques and ideas in the article

    But I have never rolled back a change to a production database in 10 years.

    I rarely have to roll back a live deployment but deployments into a QA environment are another matter. And before anyone jumps up and down QA in this case is the environment where we throw testers and bloody minded users at systems to see if they can break the code.

    As we move towards more agile practices the ability to rollback/deploy on the fly are important.

    As far as tools are concerned you can use Red-Gate Compare and generate a snapshot of the schema. Cheap though the tool is not all companies see the benefit of buying a £400 tool against £x,000 in DBA time.

    In terms of deleting columns from existing tables I take either the backup table approach or bcp the file and table create script out to a tape before I do it. It depends on how big the table is.

    To be honest we are much more likely to add/change columns than delete them. When we do delete them we are pretty damn sure they will never be used again.

  • Hi,

    I can use something like SQL Compare which has been around for a while, has been thoroughly tested out by thousands of users, and probably does all (or almost all) this. Why should I spend my time on doing something which has been done before and works very well? Unless I am missing something, this sounds like reinventing the wheel. What am I missing?

  • Alexander Kuznetsov (10/22/2008)


    Hi,

    I can use something like SQL Compare which has been around for a while, has been thoroughly tested out by thousands of users, and probably does all (or almost all) this. Why should I spend my time on doing something which has been done before and works very well? Unless I am missing something, this sounds like reinventing the wheel. What am I missing?

    SQL Compare does not generate "rollback" scripts. The article was not about deployment scripts. It was about writing rollback scripts to undo the changes that SQL Compare would generate.

  • Stupid question here... but what happens if you reverse the order of the servers just after creating the migration scripts?

    Doesn't that help you create the rollback scripts.

    I'm sure it won't put back lost or altered data, but if that's the only thing left to worry about, it seems like a great start to me.

  • hurcane (10/22/2008)


    Alexander Kuznetsov (10/22/2008)


    Hi,

    I can use something like SQL Compare which has been around for a while, has been thoroughly tested out by thousands of users, and probably does all (or almost all) this. Why should I spend my time on doing something which has been done before and works very well? Unless I am missing something, this sounds like reinventing the wheel. What am I missing?

    SQL Compare does not generate "rollback" scripts. The article was not about deployment scripts. It was about writing rollback scripts to undo the changes that SQL Compare would generate.

    SQL Compare absolutely does generate rollback scripts. I do just that every time I deploy: open up SQL Compare, generate deploy/rollback scripts, test them both out, check them in in Subversion etc.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply