Post reply

How to rollback Dacpac applied

  • January 23, 2018 at 11:39 pm

    #335381

    Hi,

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

    Regards
    Binu

  • January 25, 2018 at 8:09 am

    #1977350

    Hi,

    Anybody have any idea about dcapac rollback

    Regards
    Binu

  • January 25, 2018 at 1:44 pm

    #1977428

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

  • January 25, 2018 at 11:06 pm

    #1977481

    Hi

    any other method for rollback dacpac applied.

  • January 26, 2018 at 12:26 am

    #1977485

    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

  • January 26, 2018 at 12:43 am

    #1977486

    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

  • January 26, 2018 at 3:16 am

    #1977493

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

  • January 29, 2018 at 8:38 am

    #1977703

    Hi,

    Have any method for rollback dacpac applied ?

    Regards
    Binu

  • January 29, 2018 at 9:05 am

    #1977707

    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]

  • January 30, 2018 at 4:01 am

    #1977829

    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.

  • January 30, 2018 at 4:40 am

    #1977832

    Hi,

    Thanks for info

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

    Regards
    Binu

  • January 30, 2018 at 4:57 am

    #1977834

    This is making me laugh now 😀.

  • January 30, 2018 at 5:24 am

    #1977835

    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

  • January 30, 2018 at 9:03 am

    #1977874

    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.

  • September 11, 2019 at 6:38 pm

    #3679271

    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 18 total)

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