How to rollback Dacpac applied

  • binutb

    SSCommitted

    Points: 1741

    Hi,

    How to rollback Dacpack applied in a database.
    SQL Server version used is 16.

    Regards
    Binu

  • binutb

    SSCommitted

    Points: 1741

    Hi,

    Anybody have any idea about dcapac rollback

    Regards
    Binu

  • goher2000

    SSCertifiable

    Points: 5459

    I think you need to restore database from pre-depolyment backup.

  • binutb

    SSCommitted

    Points: 1741

    Hi

    any other method for rollback dacpac applied.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Unless the whole thing was run in a transaction and not committed (so connection still open), your 'rollback' option is going to be restore from a backup taken before the deployment,

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • binutb

    SSCommitted

    Points: 1741

    Hi,

    SqlPackage.exe is used for apply dacpck and dacpac is applied successfully and have done new data modifications.
    I want rollback all changes (new tabels , new procedures etc.) applied from dacpack without restoring old backup

    Regards
    Binu

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32363

    What the others are saying is that that's not possible.

  • binutb

    SSCommitted

    Points: 1741

    Hi,

    Have any method for rollback dacpac applied ?

    Regards
    Binu

  • quackhandle1975

    SSChampion

    Points: 10963

    Binu, have you read this? https://stackoverflow.com/questions/39707351/rolling-back-a-failed-dacpac-with-sqlpackage-exe

    But like others are kindly trying to point out, there isn't really another way.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • KamilN78

    SSC Enthusiast

    Points: 198

    In VS/SSDT project you can create a snapshot of the database you might want to rollback. If your deployment failed you can deploy it back to the snapshot version from pre-deployment moment.
    Anyway, it's not a recommended way as it does not cover other changes you could have done (data changes, pre or post deployment). The best way is to enclose whole deployment script in the transaction, if possible.

  • binutb

    SSCommitted

    Points: 1741

    Hi,

    Thanks for info

    I wish to know any method like SqlPackage.exe for rollback dacpack applied.

    Regards
    Binu

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32363

    This is making me laugh now 😀.

  • KamilN78

    SSC Enthusiast

    Points: 198

    binutb - Tuesday, January 30, 2018 4:40 AM

    I wish to know any method like SqlPackage.exe for rollback dacpack applied.

    @binutb, there is no direct method to "rollback dacpac". Firstly, because "dacpac" is a model of a database. Secondly, once you've applied the new model to the database - how you want to rollback it?
    SqlPackage.exe is only a tool for generating SQL script of changes by comparing source model (dacpac) to target database. Having this in mind you can:
    1) enclose deployment (changes) script in one big transaction and rollback the transaction when deployment failed
    2) create snapshot of dacpac in the SSDT project (version before you change the model) or get a specific version of code from repo (I really hope you're using a repo for code). Then you can generate deployment SQL script which make the changes-back to your previous situation in database (be careful as this approach as it doesn't cover many scenarios, so review the whole script before you apply it)
    3) restore the database from backup as your target is a database, right? A drawback of this solution is losing all the potential changes that users could be made.
    HTH
    Cheers,
    Kamil

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715401

    No, Please don't ask if there's some other method. There isn't.

    If you have a dacpac of the previous state of the db, you can use sqlpackage.exe to apply that, which should undo changes, but there are edge cases, depending on what you've done with your deployment, that can break the process.

    Databases  don't ever rollback. You can apply reversing changes, such as dropping a column that was added, but there are potential issues with doing so if data has changed or you meet an edge case that the dacpac doesn't handle.

    Disclosure: I work for Redgate Software, and our deployments have the similar problem and rollback isn't something we offer right now.

  • rahrah

    Newbie

    Points: 2

    If the dacpac only includes changes to stored procs / functions / triggers, and no table or data changes, you can absolutely script out the existing objects (pre-deployment), and upon noticing an issue, simply run that script to revert your changes.

    I'm actually quite surprised no one mentioned this. SSDT hasn't been out for all that long...So excuse my "shock and awe" from seeing all of the "restore from backup" suggestions. lol

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

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