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.

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.

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:

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

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:

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

That is it! Now, we can refresh the SSMS Object Explorer and start working with the 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.

Useful tool:

dbForge Source Control – powerful SSMS add-in for managing SQL Server database changes in source control.