Three phased approaches to getting your SQL Server database into source control


Database development teams show an increasing interest in getting their SQL Server databases into source control. However, depending on the team’s actual needs, their development plan and the level of source control integration required, there are various ways to achieve getting a database under source control.


We’ll address three phased approaches to source control integration from dipping your toes to jumping in with both feet, each one increasing more sophisticated and powerful, addressing both database objects and static data.

If you are not yet convinced that static data should be put under source control, the article Understanding SQL Server database static data and how it fits into Database lifecycle management can provide you with some additional details.

Periodically scripting a database into source control

The most basic form of source control implies scripting some (or all) database objects, and committing them to a local or remote repository, manually or automatically. In this way, source control is being used primarily to version the entire database (not changes) periodically in order to achieve a “snapshot” in time. The advantage is that it can be done easily, once the scripts are written and set up to be run scheduled and unattended nightly. It also requires no direct integration with source control among the developers on the team. The downside is that since the entire database is versioned, it isn’t possible to view or report on changes only, which limits the value of the repository, for reporting/informational purposes.

This can be accomplished either by using a PowerShell project and a batch file, or with a standalone 3rd party scripting tool like ApexSQL Script. Both methods are described in the articles:

Similarly, static data can be committed using ApexSQL Script, by following the steps in the article How to commit SQL Server table static data to a source control repository.

Although this approach gets the job of putting a database into a repository done, it does not differ much from simply backing up a database systematically.

Automatically keeping the repository updated with changes

A more useful implementation of getting a SQL Server database into source control, is by automatically comparing the local version of a database with the one on the repository and, if differences are detected, update the repository with only the differences. The following articles describes just that, by using ApexSQL Diff as a 3rd party comparison and synchronization tool, along with some PowerShell and a scheduler to automate the whole process:

Full source control integration

For bigger teams, where a number of developers are working and making changes on a certain database simultaneously, a more sophisticated source control system might be needed. The ability to track every change with details about who and when made it, check-in objects in real time, resolve conflicts among different versions of the same object, pull specific versions of certain objects and much more are all implemented in a 3rd party tool which integrates directly into SSMS, ApexSQL Source Control.

Linking a database to a source control system and initially committing objects is the first step, which is described in the article:

As we have already mentioned the importance of also getting SQL database static data into source control, using ApexSQL Source Control for committing and making changes to static tables is rather straightforward, and each step is explained in the respective articles:


Once you’ve made a commitment to version control, getting your SQL Server database into source control is the first step.

By following these three approaches, your team can ease into full SQL Server database source control integration gradually, in phases, with each one taking advantage of more of the advantages of source control integration, in turn.

January 25, 2017