Lone Badger Tech

Schema drift

Find out what changed outside bsm, or how two databases differ.

After every deploy and revert, bsm records a canonical dump of the schema and its fingerprint. bsm verify reports when the live fingerprint no longer matches. bsm diff-schema shows what changed.

What changed since the last deploy

bsm diff-schema -e prod
-              prod as recorded 2026-11-03 20:15Z
+              prod live
+ column dbo.Customer.Phone
+ column dbo.Hotfix.Id
~ module dbo.vCustomerOrders
    ...
    -   SELECT c.Id, c.Name, c.Email
    +   SELECT c.Id, c.Name, c.Email, c.Phone
    ...
+ object dbo.Hotfix
3 added, 0 removed, 1 changed.

+ exists only live, - was removed, ~ changed (with a line diff for views, procedures, functions and triggers). Then undo the change, or capture it as a new change so every environment gets it.

Two environments

bsm diff-schema -e test --against prod
bsm diff-schema -e test --against prod --exit-code --json   # exit 3 when they differ

Compares two live databases: - is only in the first, + only in the second. Add --script file.sql to get T-SQL that turns the first into the second (see Generating changes). Each environment uses its own connection, sign-in and fingerprint settings, so if only one of them sets schemaFingerprint.partitionBoundaries: ignore, partition functions and schemes show as changed.

What is compared

Schemas, tables and table types with their columns (type, nullability, identity, collation, computed and default definitions, encryption, period columns), indexes, foreign keys, check constraints, views, procedures, functions, triggers, user types, sequences, synonyms, system versioning (period, history table, retention) and partitioning (functions, boundaries, schemes, and the scheme and column of each partitioned table and index). The bsm journal schema is excluded. Names SQL Server generates for unnamed constraints differ on every server, so they are compared as <system>; whitespace around a view, procedure, function or trigger definition and how its CREATE was written (which depend on the tool that sent it) are ignored; columns are compared by position, not by SQL Server's internal column id; Always Encrypted columns compare by key name, never key material, so environments with different keys still match.