---
title: Microsoft SQL Server - Delta Deployment with Liquibase
description: Deploy the changes of a Microsoft SQL Server DEV database to a PROD database that holds data, with Liquibase Community in Docker.
---

[Skip to content](https://knowledge.bigenius-x.com/microsoft-sql-server-delta-deployment-with-liquibase#main-content)

[Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en)

[![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/)

Open main navigation

Close main navigation

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new)
- [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal)

 How can we help you?

- There are no suggestions because the search field is empty.

1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en)
2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en)
3. [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment)

# Microsoft SQL Server - Delta Deployment with Liquibase

**Applies to**  
Platform: Microsoft SQL Server

Deploy the changes of a Microsoft SQL Server DEV database to a PROD database that holds data, with Liquibase Community in Docker. Liquibase writes the differences of tables, indexes and views as SQL files that you review and run in PROD, and a Python script copies the stored procedures. Afterwards PROD has the structure of DEV and keeps its data and its load history.

biGENIUS-X itself always deploys the complete solution, see [Do a delta deployment](https://knowledge.bigenius-x.com/do-a-delta-deployment?hsLang=en).

### Prerequisites

- DEV and PROD are deployed from the same project, and their databases and schemas have the same names, for example on two SQL Server instances. The generated views and stored procedures name their database, so objects copied from DEV read the DEV database names.
- DEV holds the new version, [deployed](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment?hsLang=en) and loaded without errors. You have its replaced artifacts.
- Docker Desktop, PowerShell 7 and sqlcmd.
- Python with the package `pyodbc` and the Microsoft ODBC Driver 17 for SQL Server.
- A SQL Server login for Liquibase with the role `db_owner` in the DEV and the PROD database.

The steps cover one database. When the layers use several databases, repeat them per database with its schemas.

### Prepare Liquibase

1. Create a folder with the subfolders `conf`, `changelog` and `lib`, and open PowerShell 7 in it.  
   ![Folder liquibase with the subfolders changelog, conf and lib](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Feb-03-2026-09-32-54-2464-AM.png?width=640&height=190&name=image-png-Feb-03-2026-09-32-54-2464-AM.png)
2. Pull the image: `docker pull liquibase/liquibase:5.0.4`.
3. Download the JDBC driver [mssql-jdbc-13.2.1.jre11.jar](https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/13.2.1.jre11/mssql-jdbc-13.2.1.jre11.jar) into `lib`.
4. Create `conf\liquibase.properties`. The target `url` is PROD, the reference is DEV:
   
   ```
   url=jdbc:sqlserver://<prod server>:<port>;databaseName=<database name>;encrypt=false;trustServerCertificate=true
   username=<login>
   password=<password>
   referenceUrl=jdbc:sqlserver://<dev server>:<port>;databaseName=<database name>;encrypt=false;trustServerCertificate=true
   referenceUsername=<login>
   referencePassword=<password>
   classpath=/liquibase/lib/mssql-jdbc-13.2.1.jre11.jar
   ```
   
    Here `<prod server>` and `<dev server>` are the two servers, `<port>` is the port of each instance, 1433 by default, `<database name>` is the database name of both, and `<login>` and `<password>` belong to the Liquibase login. From the container, a SQL Server on the same machine is `host.docker.internal`.
5. Download [Export\_SP\_from\_DEV\_v2.py](https://f926da42ec3845beb4acf72f.blob.core.windows.net/hubspotknowledgebase/Export_SP_from_DEV_v2.py) into the folder. In its configuration block, set `server`, `database`, `username`, `password` and `schemas` of DEV, and `output_file`, or leave `password` empty to enter it when the script runs.

Each change type uses two commands and one edit. `diffChangeLog` compares DEV with PROD and writes `changelog\<change type>.xml` and ends with `Liquibase command 'diffChangelog' was executed successfully.`:

```
docker run --rm `
  -v "$PWD\conf:/liquibase/conf" `
  -v "$PWD\changelog:/liquibase/changelog" `
  -v "$PWD\lib:/liquibase/lib" `
  liquibase/liquibase:5.0.4 `
  --defaultsFile=/liquibase/conf/liquibase.properties `
  --changeLogFile=/liquibase/changelog/<change type>.xml `
  --schemas=<schemas> --referenceSchemas=<schemas> `
  --includeSchema=true --outputDefaultSchema=true `
  --log-level=warning --diffTypes=<diff types> diffChangeLog
```

![Console output of diffChangeLog: the Liquibase banner, the version line and the last line Liquibase command diffChangelog was executed successfully](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Feb-03-2026-10-16-58-2076-AM.png?width=433&height=290&name=image-png-Feb-03-2026-10-16-58-2076-AM.png)

`diffChangeLog` writes the file as XML 1.1. Change the first line to XML 1.0:

```
(Get-Content changelog\<change type>.xml -Raw).Replace('<?xml version="1.1"', '<?xml version="1.0"') | Set-Content changelog\<change type>.xml -NoNewline
```

Java's XML parser can change single characters when it reads a large XML 1.1 file, for example in a view definition. It reads XML 1.0 files unchanged.

`updateSQL` writes the SQL for PROD into `changelog\<change type>.sql`:

```
docker run --rm `
  -v "$PWD\conf:/liquibase/conf" `
  -v "$PWD\changelog:/liquibase/changelog" `
  -v "$PWD\lib:/liquibase/lib" `
  liquibase/liquibase:5.0.4 `
  --defaultsFile=/liquibase/conf/liquibase.properties `
  --changeLogFile=<change type>.xml --schemas=<schemas> `
  --outputDefaultSchema=true updateSQL > changelog\<change type>.sql
```

`<schemas>` lists the schemas of the layers, for example `st,rv,bv`.

### Deploy the changes

#### Back up PROD and rename tables and columns

1. Back up PROD: `BACKUP DATABASE [<database name>] TO DISK = N'<backup file>' WITH COPY_ONLY;`
2. Check the backup with `RESTORE VERIFYONLY FROM DISK = N'<backup file>';` and restore it once under another database name, with `MOVE` to new file names.
3. For each term that the new version renames, rename the column in PROD, for example `EXEC sp_rename N'<schema>.<table>.<old name>', N'<new name>', N'COLUMN';`
4. For each model object that the new version renames, rename its table in PROD, for example `EXEC sp_rename N'<schema>.<old name>', N'<new name>';` Otherwise `tables.sql` creates the new table empty and drops the old one.

#### Tables

1. Run both commands and the edit with `<change type>` `tables` and `<diff types>` `tables,columns`.
2. Open `changelog\tables.sql`. Delete the lines before `-- Create Database Lock Table` and the last line, `Liquibase command 'updateSql' was executed successfully.`  
   ![Start of a generated SQL file: the warning lines to delete, above the line -- Create Database Lock Table](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Feb-03-2026-10-21-11-6772-AM.png?width=496&height=160&name=image-png-Feb-03-2026-10-21-11-6772-AM.png)
3. Review each statement.
   
   Liquibase compares structures only. It writes a renamed column as a dropped and an added column, and it drops tables and columns that exist only in PROD; their data is lost. Review the generated SQL files and back up the PROD database before you run them.

    Keep `DROP TABLE` and `DROP COLUMN` only for model objects and terms you deleted. A changed data type is written as `ALTER COLUMN` without `NOT NULL`; add `NOT NULL` where the DEV column has it.
4. Run the file in PROD: `sqlcmd -S <prod server> -d <database name> -b -i changelog\tables.sql`. The file creates the Liquibase tables before its `USE` statement, so `-d` selects the database for them. With `-b`, sqlcmd stops at the first error. Without `-U` and `-P`, sqlcmd uses Windows authentication. No message starts with `Msg`.

#### Indexes and views

1. Run both commands and the edit with `<change type>` `indexes` and `<diff types>` `indexes,foreignkeys,primarykeys,uniqueconstraints,sequences`, clean the file as for the tables and run it in PROD with the same sqlcmd options. When `diffChangeLog` finds no difference, it writes no file; continue with the views.
2. Run both commands and the edit with `<change type>` `views` and `<diff types>` `views`, and clean the file.
3. Run the file in PROD without `-b`: `sqlcmd -S <prod server> -d <database name> -i changelog\views.sql`. Views that use other views fail when those come later in the file, so run it again until only `Msg 3701` and `Msg 2714` remain, for views that an earlier run dropped or created.

#### Stored procedures and load control

1. Run `python .\Export_SP_from_DEV_v2.py`. It writes every stored procedure of the DEV schemas to the file of `output_file`, here `All_Procedures_DEV.sql`.
2. In `All_Procedures_DEV.sql`, replace each `CREATE` before `PROCEDURE` at the start of a line with `CREATE OR ALTER`. Then run the file in the PROD database: `sqlcmd -S <prod server> -d <database name> -b -i All_Procedures_DEV.sql`.  
   ![Exported procedure in All\_Procedures\_DEV.sql that starts with CREATE PROCEDURE, which the step changes to CREATE OR ALTER](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Feb-03-2026-10-44-01-0735-AM.png?width=530&height=177&name=image-png-Feb-03-2026-10-44-01-0735-AM.png)
3. List the stored procedures of PROD with this query in the PROD database:
   
   ```
   USE [<database name>];
   SELECT SCHEMA_NAME(schema_id) AS SchemaName, name
   FROM sys.procedures
   WHERE SCHEMA_NAME(schema_id) IN (N'st', N'rv', N'bv');
   ```
   
    Drop each procedure that is missing from `All_Procedures_DEV.sql`, the loader of a deleted or renamed model object, by name, for example `DROP PROCEDURE [rv].[<procedure name>];`.
4. In the artifacts folder of the new version, run `sqlcmd -S <prod server> -b -i LoadControl\configure_loadcontrol.sql`. It registers new load objects and keeps the execution plans and history. `L000_Loadcontrol_Deployment.sql` is not run, because it recreates the load control tables.

### Check the result

In the artifacts folder, run `sqlcmd -S <prod server> -i SQL\validate_deployment.sql`. It prints `Deployment validation succeeded.` Then load PROD, for example with [Microsoft SQL Server - Load data - Native Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-native-load-control?hsLang=en), and check that every loader succeeds and that the earlier executions are still listed.

### Example

A Data Vault project renames the satellite term `CUSTOMER` to `CUSTOMER_NAME` and widens `CUSTOMER_TYPE` from `nvarchar(100)` to `nvarchar(200)`. It adds the Stage term `CUSTOMER_UPPER`, switches a RawVault Composite from `Virtual` to `Cached` and deletes another one. After the manual rename, `tables.sql` contains these changes:

```
CREATE TABLE rv.RDV_RC_load_virtual_rvc (BG_SourceSystem
  nvarchar(255), BG_LoadTimestamp datetimeoffset NOT NULL,
  CUSTOMER_ID int NOT NULL, CUSTOMER nvarchar(100) NOT NULL);
ALTER TABLE st.STG_ST_CUSTOMER ADD CUSTOMER_UPPER nvarchar(100);
DROP TABLE rv.RDV_RC_load_with_join_rvc;
ALTER TABLE rv.RDV_SAT_load_customer_sat
  ALTER COLUMN CUSTOMER_TYPE nvarchar(200);
```

The review keeps the `DROP TABLE` of the deleted object and adds `NOT NULL` to the last statement. `views.sql` ran once without messages. Afterwards the validation succeeded, all 103 loaders of the next load succeeded, and the satellite kept its 34 rows with their values in `CUSTOMER_NAME`.

### Failure symptoms and recovery

#### Adding a column fails on a table with rows

Message: `Msg 4901` with `ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified`  
Cause: The file adds a `NOT NULL` column to a table with rows, typically a renamed column. With `-b`, sqlcmd stops at this statement; the statements before it have run.  
Fix: Restore PROD from the backup, rename the column by hand and generate the files again.

#### updateSQL does not find the changelog

Message: `The file indexes.xml was not found in the configured search path`  
Cause: `diffChangeLog` found no difference and wrote no file.  
Fix: None needed. Continue with the next change type.

#### A view fails in every run

Message: `Msg 207`, `Msg 156` or `Msg 4145` for the same view in each run of `views.sql`  
Cause: `views.xml` was still XML 1.1, and `updateSQL` wrote the view definition with changed characters.  
Fix: Change the first line of `views.xml` to XML 1.0, run `updateSQL` again and run the new `views.sql`.

#### The export script ends with UnicodeEncodeError

Cause: The console cannot print the final message of the script. `All_Procedures_DEV.sql` is already complete.  
Fix: None needed.

### Related articles

- Previous: [Microsoft SQL Server - Deployment](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment?hsLang=en)
- Next: [Microsoft SQL Server - Load data - Native Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-native-load-control?hsLang=en), [Microsoft SQL Server - Load data - Apache Airflow Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-apache-airflow-load-control?hsLang=en)
- Reference: [Configure LoadControl (Project)](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en), [Deploy LoadControl (Project)](https://knowledge.bigenius-x.com/deploy-loadcontrol-project?hsLang=en)
- See also: [Do a delta deployment](https://knowledge.bigenius-x.com/do-a-delta-deployment?hsLang=en), [Microsoft SQL Server - Artifacts](https://knowledge.bigenius-x.com/microsoft-sql-server-artifacts?hsLang=en), [Deploy a Project in production](https://knowledge.bigenius-x.com/deploy-a-project-in-production?hsLang=en)

- [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content)

    - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines)
    - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working)
    - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account)
- [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en)
- [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en)
- [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content)

    - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration)
    - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions)
    - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features)
    - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects)
    - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches)
    - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections)
    - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview)
    - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps)
    - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms)
    - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping)
    - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships)
    - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality)
    - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes)
    - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling)
    - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts)
    - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings)
    - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace)
- [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content)

    - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse)
    - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse)
    - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks)
    - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake)
    - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server)
    - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle)
    - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts)
    - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders)
    - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment)
    - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment)
    - [Deployment with an Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-devops-pipeline)
    - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment)
    - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment)
    - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control)
    - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow)
    - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type)
    - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties)
    - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms)
- [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content)

    - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations)
- [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content)

    - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches)
    - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases)
    - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules)
    - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules)
- [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en)
- [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content)

    - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application)
    - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application)
    - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators)
- [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content)

    - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs)
    - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits)

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en)
- [Customer Portal](https://knowledge.bigenius-x.com/customer-portal)
- [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login)

[![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com)

<https://www.linkedin.com/company/bigenius> <https://www.youtube.com/@biGENIUS_DWA>

All rights reserved © 2025, biGENIUS AG