Database Version Control Using Source Control for SQL Server

May 5th, 2021

There are many solutions for version-controlling databases. We are going to review one of them, namely Source Control which comes in the SQL Tools pack. As an example, we will use the JobEmplDB database for a recruitment service:

Displaying the recruitment service database schema in Source Control
Img. 1: The recruitment service database schema

We have covered the database design basics in SQL Database Design Basics with Example.

Overview of the Source Control Menu Options

Now, we are going to review the options available on the Source Control shortcut menu:

The Source Control shortcut menu
Img. 2: The Source Control shortcut menu

The main options of the Source Control menu include the following commands: 

  1. Show Source Control Manager displaying the local changes window.
  2. Commit displaying the preparation to commit window.
  3. Get Latest showing the latest changes in the remote repository (in fact, it precedes the Pull procedure).
  4. View Changes History showing the database changes history.
  5. Link/Unlink Static Data that can link and version-control the static table data (references, regulatory references, lists, etc.). More information is available at Linking Static Data, and the worked examples are present at Version-controlling Static Data: Conflicts.
  6. Unlink Database from Source Control that can unlink the current local database from the source control repository.

Connecting the Database to the Source Control 

To start with, connect the database to the source control repository.

For this, right-click the necessary database, point to Source Control, and then click Link Database to Source Control on the shortcut menu.

Linking a SQL database to the source control
Img. 3: Connecting the database to the source control repository

In the Link Database to Source Control window that opens, navigate to the Source control repository field, and click the plus icon:

Selecting a source control repository in the Link Database to Source Control window
Img. 4: Selecting the source control repository

In the Source Control Repository Properties window, select the required source control system:

Selecting Git source control repository in the Source Control tool
Img. 5: Selecting the source control system

Note: The Source Control tool supports various types of source control systems, including Working Folder, Git, SVN, and Mercurial.

In our example, we select Git and enter the required details:

Specifying source control settings for the Git repository in the Source Control tool
Img. 6: Configuring the connection to the Git repository

Here we need to fill the following fields:

1. The Repository folder is the path to the local repository on your  PC. Thus, you need first to clone a Git repository. For instance, GitHub allows cloning via a link – you click Clone in the necessary repository and copy the link provided there:

Getting a link to clone a repository in the Source Control tool
Img. 7: Getting the link to clone a repository

2. A repository name is the local repository name.

After filling in the necessary fields, click Test and make sure that the connection has successfully been established:

Testing the connection to the Git repository in the Source Control tool
Img. 8: Testing the connection to the repository

After that, click OK, and then click OK again.

Now, select the created source and click OK:

Selecting the source control repository
Img. 9: Selecting the source control repository

For more information about how to connect to Git, see Linking a Database to Git.

Then, select the database development model and click Link

Selecting a shared database development model for the source control system
Img. 10: Selecting the database development model

There are two database development models: 

  1. Dedicated is the model where each developer works on his/her database definition copy.
  2. Shared is the model where all developers work on the same database definition copy.

In our case, we select the shared database development model for simplicity. However, in projects, we often need a dedicated model.

Committing Changes to a Repository

Adding changes to a repository

After configuring all settings, click Link and wait for the connection process to finish:

Committing changes to the repository using Source Control
Img. 11: First commit

A window that displays a table with the following columns will appear: 

  1. A check box to include the object into the commit
  2. A change type
  3. An object type
  4. An object name
  5. An object schema 

Have a look at the bottom pane. To the left, it displays the object description in a local repository (the change we want to deliver with the commit), and the object description in a source control repository (what we want to update).

Note: The object definition is present on the left but is absent on the right. It means that the object doesn’t exist in the repository yet. We intend to add this object to the version control repository.

Next, select all objects and add them to commit. Then, write a comment there and click Commit.

If the commit is successful, you’ll see the following window at the end of the process. Then, click OK.

The progress bar of the commit process using Source Control
Img. 12: The successful commit process

Go to the GitHub repository to make sure that all the necessary files are created: 

View the files committed to the GitHub repository
Img. 13: The created files located in a GitHub repository 

As you see, the object definitions in a repository are grouped by types: 

1. The Programmability folder contains the respective user object definitions grouped by functions and stored procedures.

User object definitions grouped by functions and stored procedures in the GitHub repository
Img. 14: Users programming objects 

2. The Security/Schemas folder contains the schema definitions: 

Schema definitions located under the Schemas folder in the GitHub repository
Img. 15: User schemas

3. The Tables folder contains all user table definitions with their indexes and constraints, as well as primary and foreign keys:

View user table definitions along with their indexes and constraints, 
 primary and foreign keys in the Tables folder of the GitHub repository
Img. 16: Database schemas

4. The Views folder contains the definitions of all views:

Definitions of views are located under the Views folder in the GitHub repository
Img. 17: Database views

Deleting objects from a repository

To delete all unnecessary objects, use the following script:

USE [JobEmplDB]
GO

DROP VIEW [test].[GetNewID];
GO

DROP VIEW [test].[GetRand];
GO

DROP VIEW [test].[GetRandVarBinary16];
GO

DROP FUNCTION [test].[GetListSymbols];
GO

DROP FUNCTION [test].[GetSelectSymbols];
GO

DROP FUNCTION [test].[GetRandString];
GO

DROP FUNCTION [test].[GetRandString2];
GO

DROP FUNCTION [test].[GetRandVarbinary];
GO

Then, commit all changes. For this, right-click the necessary database, point to Source Control, and then click Show Source Control Manager or Source Control from the shortcut menu:

Committing changes to the source control uisng the Source Control tool
Img. 18: Preparing to commit

After that, the tool displays the Update window, and then opens a window containing the table of changes:

View changes for commit in the Update window
Img. 19: Table of changes for commit

Take a look at the bottom pane. The object definition is absent on the left but is present on the right. It means that we are going to delete an object from a repository. 

We select all changes, add a comment, and click Commit. Then, in a new window, click OK:

View the successful commit of the changes to the repository using Source Control
Img. 20: The successful commit completion

If we go to the database view in the GitHub repository, we can see that there is just one necessary view left:

The remaining database view after deleting the unnecessary views
Img. 21: The remaining database view after deleting the unnecessary views 

Changing objects in a repository

Let’s alter the Company table by adding the IsDeleted column to the Company table:

ALTER TABLE [dbo].[Company]
ADD [IsDeleted] BIT NOT NULL DEFAULT(0);

Have a look at the Company table definition in a repository at GitHub:

Company table definition in the GitHub repository using Source Control
Img. 22: Company table definition in a GitHub repository 

As you see, the source control repository does not include the IsDeleted column in the definition of the [dbo].[Company] table so far. 

To move on, commit the changes, as before. For this, open the Source Control window:

Committing changes using Source Control Manager available in Source Control
Img. 23: Preparation to commit

If the window is already open, click Refresh:

Refreshing the changed definitions of database objects using Source Control
Img. 24: Refreshing the changed definitions of database objects

After refreshing the data about the state of our local repository, we again get the commit preparation table: 

Preparing database objects to commit using Source Control
Img. 25: Preparing database objects to commit

Have a look at the bottom pane. The object definition is present both on the right and on the left. Thus, it is present in both the version control repository and in our local repository. Therefore, we alter the object definition. 

Select all changes, add a comment to describe them, and click Commit. Then, in the new window, click OK

Committing changes to the source control repository using the Source Control tool
Img. 26: Successful commit completion

Now, go to the page that contains the Company table definition in the GitHub repository: 

View the Company table definition in the GitHub repository after the applied changes
Img. 27: The Company table definition in the GitHub repository after the applied changes

We can see a new IsDeleted column added to the [dbo].[Company] table definition.

Canceling changes

Now, delete a previously created IsDeleted column from the Company table by using the following script: 

ALTER TABLE [dbo].[Company] DROP CONSTRAINT [DF__Company__IsDelet__01142BA1];
GO

ALTER TABLE [dbo].[Company]
DROP COLUMN [IsDeleted];

Important: You need to find the correct name of the DF constraint for the IsDeleted column.

Searching for the DF constraint name for the IsDeleted column
Img. 28:  Searching for the DF constraint name for the IsDeleted column

Open again the Source Control window:

Preparing data to commit using the Source Control tool
Img. 29: Preparation to commit

At the bottom, we see the differences in the Company table definition between our local repository and the remote repository.

Select all changes and click Undo. This way, we cancel all selected changes. Then, in a new window that appears, click Yes

Confirming the selected local changes rollback using the Source Control tool
Img. 30: Confirming the selected local changes rollback

When the rollback operation is successfully completed, a new window will appear. Click OK.

View the rollback operation using the Source Control tool
Img. 31: The local changes rollback is successfully completed

SSMS shows that the IsDeleted column returns to the Company table: 

The structure of the Company table after rolling back local changes
Img. 32: The structure of the Company table after rolling back local changes

Viewing the Changes History

We can view the history of changes in a database.

To view the history of changes, right-click the database, point to Source Control, and then click View Changes History on the shortcut menu:

View the history of changes using the Source Control tool
Img. 33: Calling the Changes History View

The database changes history window appears. On the top left of the window, see the changes log with the following columns: 

  1. Revision 
  2. Date
  3. Author
  4. Comment 

On the top right of the window, see the comment to the change itself, the type of operation defined, and the full path to the changed file. 

The differences are displayed at the bottom of the window: to the left, we see the previous state, and to the right, we see the current state.

Reverse-engineering a database using Source Control Tool

Let’s create an empty JobEmplEmpty database:

Creating a new empty JobEmplEmpty database
Img. 34: Creating a new empty JobEmplEmpty database

Right-click the database you’ve created, point to Source Control, and select Link Database to Source Control:

Calling the window to link the database to the source control repository
Img. 35: Calling the window to link the database to the source control repository 

In the Link Databases to Source Control window that opens, configure the necessary parameters and click Link:

Configuring the parameters to link the database to the source control repository using the Source Control tool
Img. 36: Configuring the parameters to link the database to the source control repository

Note: For reverse engineering, you need to select the Dedicated model. 

The window showing the differences between local and remote repositories appears: 

View the differences between the local and the remote repositories in the Source Control tool
Img. 37: The window showing the differences between the local and the remote repositories

We must ensure that the JobEmplEmpty database is really empty:

View the structure of the JobEmplEmpty database in the Source Control tool
Img. 38: The JobEmplEmpty database

Now, return to the Source Control window, select all changes, and then click Undo

Reverse engineering from the source control repository to the new JobEmplEmpty database
Img. 39: Reverse engineering from the source control repository to the new JobEmplEmpty database

After that, a new window appears asking to confirm the rollback of local changes. To confirm, click OK

In the window informing that the local changes have been rolled back successfully, click OK.

Now, the new JobEmplEmpty database definition completely matches the database definition from the source control repository at GitHub:

View the JobEmplEmpty database definition after the reverse engineering
Img. 40: The JobEmplEmpty database definition after the reverse engineering

This way, we’ve performed reverse engineering. We used an empty database and restored the database definition from the source control repository to it. Similarly, we can connect any database to the source control and view the differences: 

View the differences in the database definitions using the Source Control tool
Img. 41: The differences in the database definitions

For the demo purpose, review the definition changes for absolutely different databases:

View the differences in the database definitions using the Source Control tool
Img. 42: The difference in database definitions – 2

Updating the table from the repository

What if we update the table from a repository? Will it delete the table data? Let’s check it.

Create a JobEmpplDB2 database – a copy of JobEmplDB, using the JobEmplDB database backup. Then, in the JobEmpplDB2 database, execute the following code fragment: 

ALTER TABLE [dbo].[Company] DROP CONSTRAINT [DF__Company__IsDelet__01142BA1];
GO

ALTER TABLE [dbo].[Company]
DROP COLUMN [IsDeleted];

ALTER TABLE [dbo].[Company]
ADD [Source] NVARCHAR(255);

By running this script, we’ve deleted the IsDeleted column and added the Source column to the Company table. 

Now, link the JobEmplDB2 database to a repository we created earlier. 

Right-click the JobEmplDB2 database, point to Source Control, and then click Link database to Source Control on the shortcut menu:

Linking the database to the repository using the Source Control tool
Img. 43: Linking the database to the repository

In the Link Database to Source Control window, configure necessary parameters and click Link:

Configuring the database connection to the repository using the Source Control tool
Img. 44: Configuring the database connection to the repository

In the window that opens, select changes for the Company table and click Undo:

The Company table gets synchronized with the repository
Img. 45: The Company table gets synchronized with the repository 

When a warning appears, click OK to roll back the local changes we’ve selected (those that mismatch the source control repository):

The warning appeared before rolling back the selected local changes
Img. 46: The warning appeared before rolling back the selected local changes

At the end of the rollback process, click OK in a new window:

Successful completion of the selected local changes rollback
Img. 47: Successful completion of the selected local changes rollback

Thus, we’ve synced the Company table definition with the source control repository at GitHub:

View the Company table structure after rolling the local changes back
Img. 48: The Company table structure after rolling the local changes back

To ensure that the data has not been deleted, use a simple SELECT query: 

Using a SELECT query to retrieve data in the Source Control tool
Img. 49: The number of entries in a company table

It turns out that the Source Control in SQL Tools updates the database definition incrementally, without recreating the changed tables. This way, it grants safe reverse engineering to a non-empty database. 

Conclusion

To sum it up, we have exemplified how to use dbForge Source Control for SQL Server to version-control and reverse-engineer the SQL Server database.

Download a free 30-day trial version of the dbForge SQL tool pack, which includes dbForge Source Control for SQL Server, to evaluate the features and capabilities that will help you perform SQL Server tasks easily and effectively.

Download a 30-day trial version of the dbForge SQL tool pack

Related articles

Evgeniy Gribkov
Leave a Comment