Devart Blog

How to Build a Database from Source Control

Posted by on 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:

svn_initial_commit

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:

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"
PAUSE

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

SET NOCOUNT ON
GO

PRINT 'Creating sales_demo1 database'

USE [master]
GO
DECLARE @db_name NVARCHAR(255);
SET @db_name = N'sales_demo1';
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = @db_name)
BEGIN
  EXEC (N'ALTER DATABASE '+@db_name+N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
  EXEC (N'DROP DATABASE '+@db_name);
END;
EXEC (N'CREATE DATABASE '+@db_name);
GO

USE sales_demo1
GO

: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'
GO

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:

cmd_build_sales_db

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

sales_demo_in_ssms

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:

sales_demo2_in_ssms

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

link_db_to_sc

 

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

manage_sc_repo

 

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

sc_repo_prop

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

linked_database_to_sc

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 Reply