Skip to content

SQL Server schema drift

Understand environment differences before deployment.

The problem

An emergency production change or a missed deployment can leave environments with different definitions. A deployment that succeeds in test can then fail or behave differently in production.

Recognise the symptoms

  • A column, index or procedure differs between environments
  • Deployment scripts assume an object definition that no longer exists
  • An undocumented change appears during migration planning

A practical investigation workflow

  1. Establish the baseline

    Identify the approved schema source, environment scope and object versions. Distinguish expected differences from accidental drift.

  2. Compare definitions and dependencies

    Review tables, columns, constraints, indexes and programmable objects. Include dependent code and environment-specific settings in the assessment.

  3. Review a reconciliation plan

    Decide which definition is intended and assess data conversion, locking and dependency risks before generating or running changes.

  4. Verify the deployed state

    Compare the resulting definitions with the baseline, run relevant application checks and document approved exceptions.

What success looks like

Known, explained differences and a reviewed deployment plan instead of surprise production changes.

Schema drift and data drift need different checks. Object Search supports discovery; this page does not claim an automated schema deployment engine.

Microsoft: SQL Server catalog views

Related workflows

SQL Object SearchDiscuss your DBA workflow