Emergency changes can leave database documentation behind. Putting a database under source control gives the team a shared record of intended schema changes and a way to review the next one before it reaches production.
Decide What a Database Under Source Control Should Represent
A database under source control needs a clear unit of truth. For some teams that unit is the desired current definition of every object. For others it is the ordered sequence of changes that transforms one version into the next. Both approaches can be useful, but mixing them without a rule creates confusion. A stored procedure file that shows its latest definition cannot, by itself, explain how an existing table was migrated safely.
I begin by asking how this team deploys and rolls back changes today. If deployments are assembled manually, a state model makes object review easier but still needs deployment planning. If deployments already run numbered change scripts, a migration model preserves that history naturally. The answer is tied to operational practice, not the name of a tool.
Use a State Approach for Current Definitions
In a state approach, each table, view, procedure, function, and other selected object has a stable script representing its desired definition. A comparison or build process can show the difference between those files and a target database. Reviewers see the intended final shape, which is especially helpful for procedures and views. File paths stay stable even as the object changes.
The difficult part is turning a difference into a safe deployment. A renamed column can look like a drop and an add. That operation can lose data unless the deployment script handles the rename deliberately. Seed data, permissions, SQL Agent jobs, and database settings also need explicit treatment. A state snapshot is a strong description, not an automatic promise of a safe transition.
Use Migrations for Ordered Changes
A migration approach stores each approved change as an ordered script. One script adds a column, another backfills rows in batches, and a later script makes the column required after the application is ready. That history explains how the database arrived at its current state. It also gives deployment tooling a sequence to apply.
I prefer migrations when a release needs staged data movement or coexistence with two application versions. A migration should say what it expects before it runs and what successful completion means. Re-running it accidentally needs a defined result. Even then, rollback is not simply the inverse of every statement: an irreversible data correction needs a recovery plan and a backup, not a hopeful DROP command.
Inventory the Objects Before Putting the Database Under Source Control
First choose the boundaries of the database project. Include application owned schema objects and any permissions or reference data required to make a new environment behave correctly. Exclude secrets, environment specific connection details, and transient operational objects. Record the SQL Server version and database compatibility level used to validate scripts.
The following inventory is read only. It helps reveal which user objects need a deliberate destination in the file tree. It does not generate a complete deployment package. Tables, constraints, indexes, and security objects require more than OBJECT_DEFINITION alone, so an inventory is a starting point rather than a shortcut to a finished project.
SELECT s.name AS schema_name, o.name AS object_name,
o.type_desc, o.create_date, o.modify_date
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
ORDER BY s.name, o.type_desc, o.name;Script Objects Consistently
Choose a repeatable path such as Schema/ObjectType/ObjectName.sql and use the same casing and line endings throughout the project. Keep one object definition in each file when practical. Script dependencies in a known order, and document any required SET options for modules. An object name in a file should match its actual schema qualified name. Review a generated script before treating it as authoritative.
For a quick check, OBJECT_DEFINITION exposes the text of a visible SQL module. It does not return a table definition and can return NULL when permissions or encryption prevent access. Use the established scripting tool for the full object set, then compare its output to the inventory. If one object silently vanishes from a baseline, the first deployment can become an unpleasant surprise.
SELECT s.name AS schema_name, o.name AS module_name,
OBJECT_DEFINITION(o.object_id) AS module_definition
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.type IN ('P', 'V', 'FN', 'IF', 'TF', 'TR')
AND o.is_ms_shipped = 0
ORDER BY s.name, o.name;Make the First Commit of the Database Under Source Control Reviewable
The first commit should be a clean baseline from one agreed source database, not an unsorted export from several environments. Record the source instance and capture time in a project note without storing credentials. Put the schema scripts in predictable folders, include a short deployment guide, and list intentional exclusions. Review the baseline against the object inventory before committing it. A reviewer should be able to answer which database the files describe.
Keep that first commit focused on the baseline. Do not combine it with unrelated refactoring or a production repair. When the baseline is accepted, the next change can show a clear difference. If production differs from the baseline at that moment, reconcile the difference explicitly. Otherwise the new history starts with an undisclosed gap, and every later comparison inherits the uncertainty.
Review Deployment as a Separate Step
A change request needs both the source diff and a deployment plan. Reviewers should check object dependencies, data preservation, lock exposure, permissions, and the order in which the application and database will change. Build the project in a disposable environment when possible, then apply the proposed deployment to a recent test copy and check the expected objects and data. Keep environment values outside the schema files.
What should happen if deployment stops halfway through? Answer that before release night. Some changes fit one transaction, while others need phased execution and a restart point. Save the exact script that ran and the outcome so the shared history represents reality. A database under source control gives accountability for intended changes, while verification confirms what the target actually received.
Which object types remain outside the baseline? List them explicitly, including jobs, linked servers, and instance settings when they are part of deployment. I also save a build check that creates the schema in a disposable database. A clean baseline that cannot build is a collection of files, not a dependable starting point.
Related reading on this blog: Team Database Development and Version Control with SQL Source Control and Automating SQL Server Deployments Across Multiple Databases Using Python.
A database baseline is not a deployment plan, it is the starting point for one.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
