DbUp is the dependable choice for scripted SQL deployments, and its default of one transaction per script catches a lot of mistakes. The catch is that some statements, including Full-Text Catalog creation, cannot run inside a user transaction at all. There are two clean ways to handle that without giving up transactional safety on the rest of your scripts. Both approaches keep the rest of your deploy pipeline intact.
At a glance
- DbUp defaults to `WithTransactionPerScript`, which breaks Full-Text Catalog statements.
- The error is `CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction`.
- Option 1: a custom `IScriptPreprocessor` wraps most scripts in transactions on the fly.
- Option 2: partition the script list and run multiple UpgradeEngines, one per partition.
- Keyword checks or file-name conventions decide which bucket each script lands in.
Why Full-Text breaks the default
Scripts that use `CREATE FULLTEXT CATALOG` or `CREATE FULLTEXT INDEX` cannot run inside a transaction. Run them under DbUp's default and the engine returns the explicit error `CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction`. That is a known limitation, not a bug.
The naive fix is to call `WithoutTransaction` on the builder. That turns the whole deployment into a single transaction, which removes the safety net for every other script. Most teams do not want to give that up.
Option 1: a script pre-processor
DbUp exposes an `IScriptPreprocessor` interface that lets you rewrite script contents before they are executed. The implementation checks whether the script text contains `CREATE FULLTEXT`. If it does not, the pre-processor wraps the script in a `BEGIN TRANSACTION`, `BEGIN TRY`, `COMMIT`, `END TRY`, `BEGIN CATCH`, `ROLLBACK`, `THROW`, `END CATCH` block. If it does, the script passes through unchanged.
In use, the builder is configured with `WithoutTransaction`, and the pre-processor reintroduces transactions on the scripts that can safely run inside one. The trade-off is trusting your keyword check to draw the line correctly. File-name conventions or any other rule work equally well.
Option 2: partition the script list
The second approach leaves DbUp's defaults alone and runs multiple UpgradeEngines. The implementation iterates over all script files, splits them into batches based on whether they can run inside a transaction, and runs each batch with its own configuration. The transaction-aware batch gets `WithTransactionPerScript`. The non-transactional batch gets `WithoutTransaction`.
If any batch fails, the deployment terminates. That preserves the original safety story for the parts that can use transactions, and cleanly skips it for the parts that cannot.
Our analysis
Both approaches work, and the right one depends on your team's appetite for custom code.
| Pre-processor | Partitioned engines | |
|---|---|---|
| DbUp engine instances | 1 | Multiple |
| Custom code scope | Single class | Batching logic + multiple calls |
| Ease of debugging | Easier | Requires tracking which engine failed |
| Ease of adding new rules | Edit the check | Edit the batch predicate |
The pre-processor is more elegant—it keeps DbUp's single-engine story intact—but it adds an extension point that you have to maintain. The partitioned engine is more explicit and easier to reason about, but it duplicates some of DbUp's bookkeeping across engines.
Who wins:** Small teams with few Full-Text scripts will favor the pre-processor for its simplicity. Large teams with complex script portfolios will prefer the partitioned approach for its auditability.
What to watch:** Ordering. If a transactional script depends on a Full-Text Catalog that has not yet been created, the deploy will fail mid-run. Keep non-transactional scripts at the start of the run, with the transactional scripts after.
Bottom line
Full-Text Catalog creation is the canonical DbUp edge case, and there is no reason to weaken the default for the whole pipeline to handle it. Pick the pre-processor for elegance or the partitioned engine for explicitness, and keep the transactional default where it belongs.
Key takeaways
- DbUp's `WithTransactionPerScript` is the right default, but Full-Text statements cannot use it.
- A custom `IScriptPreprocessor` is the cleanest way to wrap most scripts while skipping the ones that cannot be transactional.
- Running multiple UpgradeEngines against partitioned script lists is the explicit alternative.
- Keep non-transactional scripts ordered before transactional ones if dependencies matter.
Frequently asked questions
Why does Full-Text Catalog creation fail inside a transaction?
SQL Server does not allow `CREATE FULLTEXT CATALOG` inside a user transaction. It has to run outside one.
Which DbUp option is simpler, the pre-processor or the partitioned engine?
The pre-processor has fewer moving parts. The partitioned engine is more explicit, which some teams prefer.
Can I just turn off transactions for the whole deploy?
Yes, by calling `WithoutTransaction` on the builder. The downside is losing the safety net for scripts that do not need it.
How does the pre-processor decide which scripts to wrap?
The implementation checks for the string `CREATE FULLTEXT`. File-name conventions or any other rule work equally well.
Does the partitioned engine approach require multiple database connections?
No. Each UpgradeEngine uses the same connection string; they just run different script batches with different transaction settings.