Rolling back a failed DACPAC with SqlPackage.exe

Question

I have regularly used EF migrations to deploy databases, both from scratch and for evolution. I am currently however working on another teams project that use DACPACs, which seemed just as good, until today. Today, I added a Unique Key Constraint to a table that lacked it and as you can probably imagine, it blew up, causing the DACPAC deployment via sqlpackage.exe to fail.

What I now realise is that the failure is in no way rolled back, so now I have a database in a partly converted state and because I have a pre-deployment script referring to a column that was removed after the pre-deployment script ran the first time, the state is now such that I cannot run the dacpac again because it fails for that reason instead.

I assumed, wrongly, that sqlpackage.exe would wrap up the deployment in a transaction and roll it back on failure. Then on realising my error, assumed, wrongly again, that I'd missed a flag somewhere.

Can anyone tell me how to run one safely without essentially corrupting the database...

Answer 1

You can use /p:IncludeTransactionalScripts=true to have SqlPackage.exe execute the main schema change operations as a single transaction. Note, though, that the pre and post-deployment scripts are not included in that transaction logic. The suggested practice is to author pre and post-deployment scripts in such a way that they can be safely re-executed (i.e. as idempotent T-SQL).

Answer 2

Run into the same issue, in addition to what Steven wrote I put in place a wrapper in the Pre & Post deployment scripts that will Rollback the script in case of failure: (You can un-comment the IF EXISTS part for further validation/testings)

SET IMPLICIT_TRANSACTIONS ON    --By default it is OFF

BEGIN TRY
    /*
    IF (EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'DW' 
                     AND  TABLE_NAME = 'TBL_Users'))
    BEGIN
    */
        DROP TABLE DW.TBL_Users
        DROP TABLE DW.NOT_EXIST_TABLE
    --END
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION;
    DECLARE @ERROR_MESSAGE  VARCHAR(2000);
    SET @ERROR_MESSAGE = CONCAT('Pre deployment script failed failed. ', ERROR_MESSAGE());
    THROW 51000, @ERROR_MESSAGE, 1
END CATCH

COMMIT TRANSACTION;
SET IMPLICIT_TRANSACTIONS OFF --Set back to OFF default