How to rollback Dacpac applied

Question

Post reply

How to rollback Dacpac applied

binutb

SSCommitted

Points: 1741
More actions
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

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

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

binutb SSCommitted Points: 1741 More actions · Answer 1

Hi,

Anybody have any idea about dcapac rollback

Regards
Binu

goher2000 SSCertifiable Points: 5479 More actions · Answer 2

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

binutb SSCommitted Points: 1741 More actions · Answer 3

binutb

SSCommitted

Points: 1741

January 25, 2018 at 11:06 pm

#1977481

Hi

any other method for rollback dacpac applied.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 More actions · Answer 5

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: 32407 More actions · Answer 6

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

binutb SSCommitted Points: 1741 More actions · Answer 7

Hi,

Have any method for rollback dacpac applied ?

Regards
Binu

quackhandle1975 SSChampion Points: 11055 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

Hi,

Thanks for info

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

Regards
Binu

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 11

Beatrix Kiddo

SSC-Dedicated

Points: 32407

January 30, 2018 at 4:57 am

#1977834

This is making me laugh now 😀.

KamilN78 SSC Enthusiast Points: 198 More actions · Answer 12

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: 736567 More actions · Answer 13

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 More actions · Answer 14

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