Deploying Database from Source Control

These days, development teams start new projects from choosing a version control system. There are many benefits of source control systems in coordinating the efforts of the development team. It ensures a complete audit trail of all changes to the code and allows the team to reproduce any specific revision or build. Along with the programming code, databases can and should be placed under source control. In this article, we will have a look at how to deploy a database that is in the remote repository.

Assume that you have a remote repository that contains a database script folder. The folder contains DDL scripts for database objects. The task is to deploy a database on the local PC. For the demonstration purpose, I will use Visual SVN and Tortoise SVN Repository Browser. The following picture demonstrates the demo repository that hosts the database script folder.

SVN_repo

The first step is to create a local working copy. To do so, you need to create a folder on the local hard drive and perform the SVN checkout operation.

svn_scheckout

Once the operation is completed, all SQL files will be stored in the local folder. Now, you can deploy the database. Obviously, it is not a good practice to execute manually all SQL files one-by-one. To automate the deployment you can create a batch file.

Creating BATCH file

You need to create a batch file. In this demo, I will create the sales_demo_build.bat file with the following content:

I use the SQLCMD utility in the batch file. To get more information about SQLCMD arguments, read the following article: sqlcmd Utility.

With the help of SQLCMD, we can execute the sales_demo_build.sql file. It will take all SQL files from the working folder and build the database. The sales_demo_build.sql file contains the following code:

The script 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 into the statement cache.

Now, we can run the batch file:

cmd_report

That is it! Now, we can refresh the SSMS Object Explorer and start working with the database:

deployed_database

As you can see, it is a simple way of deploying a database that is stored in source control. However, it is not the only way to resolve this task.  We will talk about alternative approaches in the next articles.

Andrey Langovoy

Andrey Langovoy

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server and contributes to open source projects, MSDN and MDN.
Andrey Langovoy

Latest posts by Andrey Langovoy (see all)

Andrey Langovoy

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server and contributes to open source projects, MSDN and MDN.