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 a database from source control by creating a batch file and using dbForge Source Control for SQL Server.

Deploying a database with a batch file

To begin with, let’s copy the files from the remote repository to a local working copy on your computer.

For demonstration purposes, I will use Visual SVN. You can use any 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 image demonstrates the case:

Commit changes to source control

To start working with the database, you need to check out all files to a local working copy folder. For this, you can create a folder on your computer (sales_demo in our case) and execute the SVN checkout command. For your convenience, I would recommend you to use TortoiseSVN — a Subversion SVN client.

To check out files to the local working copy, right-click the folder you’ve created and then click SVN Checkout:

Check out files to the local working copy

Once done, the sales_demo folder contains all the SQL files from the remote repository. Now, we can start deploying the database.

One way to do this is 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

In the text editor, create a batch file with the following content:

sqlcmd -S "server"-U "login"-P "password"-i "input_file" 
PAUSE 

where input_file is a path to a SQL file that will create a database. In our case, the input file is D:\sales_demo_build\sales_demo_build.sql.

Then, create a single SQL file that will add a new database:

SET NOCOUNT ON
 GO
 PRINT 'Creating sales_demo1 database'
 USE [master]
 GO
 DECLARE @db_name NVARCHAR(255);
 SET @db_name = N'sales_demo';
 IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = @db_name)
 BEGIN
   EXEC (N'ALTER DATABASE '[email protected]_name+N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
   EXEC (N'DROP DATABASE '[email protected]_name);
 END;
 EXEC (N'CREATE DATABASE '[email protected]_name);
 GO
 USE sales_demo
 GO
 :On Error exit
 :r "D:\sales_demo\Tables\dbo.Customers.sql"
 :r "D:\sales_demo\Tables\dbo.OrderLines.sql"
 :r "D:\sales_demo\Tables\dbo.Orders.sql"
 :r "D:\sales_demo\Tables\dbo.Products.sql"
 PRINT 'Creation is Completed'
 GO

Note that the file also contains a list of SQL files to be executed.

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

In the command prompt, run the batch file:

Run the batch file from the command prompt

Now, refresh the SSMS Object Explorer to see the database deployed.

The deployed database is displayed in Object Explorer

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

To simplify the process, we recommend using dbForge Source Control for SQL Server, a powerful SSMS add-in that can link databases to the supported source control systems and deliver a smooth and clear workflow in a familiar interface.

Deploying a database with the help of dbForge Source Control for SQL Server

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

Then, open SSMS and in the Object Explorer, create a new database, for example, sales_demo2. Right-click the database and select Source Control > Link Database to Source Control.

Link the database to SVN using dbForge Source Control

In the Link Database to Source Control window, under Source control repository, click + to select SVN source control and configure its settings.

Select a source control repository in the Link Database to Source Control window

In the Source Control Repository Properties dialog box, select a source control system, specify the URL to the repository, source control settings, and then click OK.

Configure source control settings in dbForge Source Control

Then, select a database development model and click Link. That’s it! Note that the database icon has changed its appearance in the Object Explorer.

The linked database is displayed in Object Explorer

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

Conclusion

In this article, we explored 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 one is to use dbForge Source Control for SQL Server. As discussed, the dbForge Source Control allows you to save time and avoid some extra steps while deploying the database.

Overview the main features and capabilities available in the SQL Tools pack

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