How to rollback transactions SQL Server Scripts?

Question

I am using DbUp (Documentation) package to maintain and execute scripts on the database. Currently, I am using

var builder = DeployChanges.To
                        .SqlDatabase(connectionString)
                        .WithExecutionTimeout(TimeSpan.FromSeconds(300))
                        .WithTransactionPerScript()
                        .WithScriptsFromFileSystem(rootPath, new FileSystemScriptOptions { IncludeSubDirectories = true })

I want to rollback all the transactions if any scripts fails while executing a bunch of scripts. Using,

WithTransactionAlwaysRollback()

Transaction rolls back even if the all the scripts execute successfully. I only want to rollback on failure.

Is there another way to solve this problem?

Answer 1

Use

.WithTransaction()

instead of

.WithTransactionPerScript()

And here's an implementation of these extension methods:

public static UpgradeEngineBuilder WithTransaction(this UpgradeEngineBuilder builder)
{
    builder.Configure(delegate (UpgradeConfiguration c)
    {
        c.ConnectionManager.TransactionMode = TransactionMode.SingleTransaction;
    });
    return builder;
}

public static UpgradeEngineBuilder WithTransactionPerScript(this UpgradeEngineBuilder builder)
{
    builder.Configure(delegate (UpgradeConfiguration c)
    {
        c.ConnectionManager.TransactionMode = TransactionMode.TransactionPerScript;
    });
    return builder;
}