How to rollback Dacpac applied

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716314

    You can script out the objects and make changes, but this is not a rollback. It's a roll forward.

    If you are just changing procs/functions/views, just make a dacpac of the existing state and apply that, as noted above.

    However, if you have table changes, this is dangerous. It's not simple and a db restore might be needed. It's just hard to tell what you need to do with a simple "rollback a dacpac" question

  • rahrah

    Newbie

    Points: 2

    @steve-2 - While I certainly agree with you in that "How do I rollback a dacpac" is a completely LOADED question. What I strongly disagree with is the notion of table / data changes being dangerous and potentially prompting a restore. Planning a deployment *should* involve capturing all the necessary object level backups (ie. table exports, scripting of existing objects being updated in the deployment, etc.) needed to *revert* a deployment (which is, in fact, logically synonymous to a rollback and / or a restore) available prior code drop, and a full DB restore, IMO, should NEVER be required. This is exactly what they've built into SSDT when one deploys updates to populated tables. SSDT creates a backup of the existing table (select * into...), applies the updates to the live table, then copies the original data back into the updated table. It's all wrapped in a transaction, and if an error is thrown, a SQL rollback is performed.

    My point is, there's no reason that same process can't be mimicked outside of SSDT , and I guess I was just surprised that this sort of information wasn't provided initially when the question was asked.

    But, I will admit, my response here is in fact outside the scope of the initial question as I'm not providing instructions for "rolling back a dacpac applied", and more using it as a teaching moment to advise devs to do due diligence to prepare for deployments instead of just hitting that magic "update" button in SSDT and hope that everything works out :). So at the end of the day, you are correct, and none of my advice pertains to a situation in which the team is unprepared for deployment, applies a dacpac, and needs a magic "undo" button that doesn't come standard with SSDT :).

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716314

    It's not that a rollback is always dangerous, but it can be. Rolling back 1,000 narrow rows is much different than 100,000,000 wide rows. The select * into isn't always a good choice and it causes numerous problems at times with deployments. SSDT works amazing well and amazingly poorly in different situations.

    The process can be mimic'd, but the point is that the individual should understand the requirements and limitations and make a decision accordingly. We, or at least I, am not trying to say "Do this", but understand what things might affect your system. Time is a factor that some don't think about. We often consider rolling back inside of a few minutes. What if it's a few hours? We want a rollback to "Save" data often, which is something SSDT doesn't do.

    I think we mostly agree, just view the possibilities differently.

  • This was removed by the editor as SPAM

Viewing 4 posts - 16 through 19 (of 19 total)

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