How to Build a Database from Source Control

December 24th, 2015

There is no doubt that database developers can and should benefit from using source control systems. Regardless of the type of source control system, developers also must think over the development model. They must also consider how they wish to build and deploy databases from source control.

In this article, we will discuss several approaches of how to build databases form source control.

For the demonstration purposes, I will use Visual SVN. You can use one of your favorite source control systems.

Assume that you have a script folder in the remote repository. The script folder contains DDL scripts for schema objects.

The following picture demonstrates the case:


To start working with the database, you need to checkout all files to a local working copy folder. For this, you can create a folder on your HDD and execute the SVN checkout command. For added convenience, I would recommend you to use TortoiseSVN — a Subversion SVN client.

Right-click the folder and then click SVN Checkout:


Now, the sales demo1 folder contains all the sql files from the remote repository. Let’s start deploying the database.

One way is to start executing sql files one-by-one. This is the worst solution I can imagine and we are not going to do this. Instead we will create a batch file to automate the process.

Creating a BATCH file to deploy the database

First, we need to create a batch file with the following content:

sqlcmd -S "ANDREYL\SQLEXPRESS" -U "sa" -P "sa" -i "D:\sales_demo_build\sales_demo_build.sql"

Then, you need to create a single sql file with the following code:


PRINT 'Creating sales_demo1 database'

USE [master]
DECLARE @db_name NVARCHAR(255);
SET @db_name = N'sales_demo1';
IF EXISTS (SELECT 1 FROM sys.databases d WHERE = @db_name)
  EXEC (N'DROP DATABASE '+@db_name);

USE sales_demo1

:On Error exit

:r "D:\sales_demo1\Tables\dbo.Customers.sql"
:r "D:\sales_demo1\Tables\dbo.OrderLines.sql"
:r "D:\sales_demo1\Tables\dbo.Orders.sql"
:r "D:\sales_demo1\Tables\dbo.Products.sql"

:r "D:\sales_demo1\Tables\Constraints\Foreign Keys\dbo.OrderLines.FK.sql"
:r "D:\sales_demo1\Tables\Constraints\Foreign Keys\dbo.Orders.FK.sql"

PRINT 'Creation is Completed'

The script creates a new database. Note, it also contains a list of  sql files to be executed.

:r is an SQLCMD command that parses additional Transact-SQL statements and sqlcmd commands from the file specified by <filename> into the statement cache.

Now, we can run the batch file:


If we refresh the SSMS object explorer, we find our database deployed.


This approach requires some time to prepare the batch file and main sql file that builds the database.

To simplify the process, we can use dbForge Source Control for SQL Server —  a powerful SSMS add-in that can link databases to all popular source control systems, and delivers smooth and clear workflow in a familiar interface.

Using dbForge Source Control for SQL Server

First, you need to download and install dbForge Source Control for SQL Server.

Then, you can open SSMS and create a new database: sales_demo2:


Right click the database, point to Source Control Tasks, and then click Link Database to Source Control.



The next step is to set up the connection to the repository. Click the source control repository text box and then click Manage.



Select a required source control system. It is SVN in our case. Provide the URL to the repository. Click OK.


That’s it! Note the database icon has changed its appearance in the Object Explorer.


Now you can work with sql scripts as application developers work with code files. You can change it and commit to the source control or get remote changes to your local database.

In this article, we discussed the ways to deploy a database from source control. There are two methods to complete this task: the first one is to create a batch file that would build the database and the second is to use dbForge Source Control for SQL Server. I personally prefer the second way, because it allows me to avoid some extra steps while deploying the database!



2 Responses to “How to Build a Database from Source Control”

  1. Alex Trodder Says:

    I have a little bit of experience with website management. I know that keeping your SQL databases secure is essential to prevent hacks or SQL injections. I’ve never had to build one from a source control system. I like how your article includes the use of Visual SVN. I’m still pretty inexperienced with Linux code. I’ll have to read your article again to help learn more about site security.

  2. Patzylin Says:

    I trying out a demo of source control but when i try to connect with TFS show me an error message “Please update local client for TFS 2012”, i don’t understand… I ‘ve installed SQL Server 2012 (no express) team Explorer, VS 2013 and TFS 2012 on my server

Leave a Comment