How to Build a Database from Source Control

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:

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:

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 '+@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_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:

Now, refresh the SSMS Object Explorer to see the 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 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.

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

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.

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.

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.