Tracking Database Changes Using Working Folder Source Control

This article talks about a new method to version control a database using a working folder so that historical changes made to the database can be traced back.

Overview

Since this article is based on the new approach to source control a database by overcoming the working folder limitation, it is better to get some basic understanding of the working folder and related things.

Background

Working folder, when used as a source control, has a limitation that it cannot keep the history of the database changes. But in this article, we are going to focus on the method of using a secondary source control (behind the scenes) with a working folder that can overcome the limitation.

Pre-Requisites

This article assumes that the readers are familiar with the basics of database version control using Working Folder and Git along with an understanding of Visual Studio Team Services (VSTS) which is now called Azure DevOps.

It is recommended to use the following sets of tools to run through all the steps mentioned in this article:

1. dbForge for SQL Server
2. dbForge Source Control
3. Git for Windows (or any Git client)
4. Azure DevOps (formerly VSTS)

This article also assumes you signed up with Azure DevOps and already got an account, or you can sign up and create a new account now if you wish to follow all the steps in this article.

Alternatively, any source control which offers the Working Folder option can be used with SSMS (SQL Server Management Studio) provided you have the required skills to take the conceptual approach from this article and put it into action.

Reference

In order to develop a basic understanding of using the working folder to source control database, please go through my previous article by clicking the link below:

Using Working Folder to Source Control Database in Simple Steps

Working Folder Limitation

We must first understand the limitation of using Working Folder to source control a database. If you have read my previous article you already know the limitation.

Working Folder Scenario

If we closely observe the following steps we can easily understand how the Working Folder source control option is limited when it comes to database versioning:

1. Dev1 creates a new database about wrist watches and calls it Watches as per requirement.
2. Dev1 further creates a new table and calls it Watch with WatchId and WatchName columns as per requirement.
3. Dev1 has not been asked to use any particular source control and the project itself is in the development test phase so he decides to use Working Folder source control.
4. Dev2 has been asked to create a new table DigitalWatch with DigitalWatchId column so he deletes the Watch table thinking that with the DigitalWatch table the Watch table is not required anymore.
5. There is no way to revert the changes done by Dev2 and create the Watch table using the working folder source control once again because the working folder has just got the latest version of the database code.

This is illustrated as follows:

Using Working Folder to Track Database Changes

There is a way to enforce Working Folder to keep track of database changes which can help us to restore the lost database objects, although using Working Folder by default does not maintain the history of database changes.

Use of Secondary Source Control (Git)

This can be achieved by using a secondary Source Control side by side with using the Working Folder option which is a bit complicated to manage but works well.

We are going to use Git as the secondary Source Control with Working Folder in this article.

Git is a distributed version control system and also one of the most commonly used source controls today.

Why Git with Working Folder?

One would argue why we need to use Git side by side with Working Folder when we can directly use Git with dbForge Studio for SQL Server to version control our database.

The answer is to understand the flexible nature of the Working Folder Source Control option along with exploring the further potential to carry on with Working Folder rather than just using it as a temporary solution.

Download Any Git Source Control Client or Git for Windows

Before we move any further, please install any Git Source Control client of your choice which is going to help us to save database changes with time.

This article walkthrough is using Git for Windows client.

Install the Git for Windows with the options of your choice, we have used the default options to install Git for Windows.

Create Azure DevOps Project using Git

Sign into your Azure DevOps account and create a new project SQLBookShopV3-Using-Working-Folder-with-Git and choose the Git Source Control option to create a private repository as follows.

Go to Repos on the left navigation bar and copy the Repo (Git repository) link by clicking the clipboard icon next to the link.

The plan is to create local repo based on the Git Repo link and then empower Working Folder through this.

Create Working Folder under Git Local Repos

If you have already got Git Local Repos folder, then create your working folder SQLBookShopV3-Working-Folder-with-Git there:

C:\Users\<UserName>\Source\Repos\SQLBookShopV3-Working-Folder-with-Git

Alternatively, create the Repos folder any place of your choice and then create the subfolder SQLBookShopV3-Working-Folder-with-Git.

Create New Git Local Repository

We are now going to create a local Git repository so that the working folder can fit into it.

Open Git GUI which should be present after Git for Windows installation.

Create the local repository by choosing the Create New Repository option.

Create Git Local Repo (Repository).

The local Git repository has been successfully created.

Link Remote Git Repo with Local Repo

Creating Git Local Repository is not enough, we have linked it with our Git Remote Repository created through Azure DevOps.

Link Remote Git Repo with Git Local Repo by selecting the Remote option from the main menu and then clicking Add New Remote and then type in your Azure DevOps Project location.

Create SQLBookShopV3 Database

Open dbForge Studio for SQL Server and create a new database SQLBookShopV3.

Create Book Table

Create the Book table with the BookId, Title and Author columns as follows.

CREATE TABLE SQLBookShopV3.dbo.Book (
  BookId INT IDENTITY
 ,CONSTRAINT PK_Book_BookId PRIMARY KEY CLUSTERED (BookId)
 ,Title VARCHAR(100)
 ,Author VARCHAR(50)
)
GO

Link Database with Working Folder Source Control

In the next step, we are going to link the database to Working Folder Source Control.

Right-click the database (SQLBookShopV3) and select Source Control, and then Link Database to Source Control.

Next, Locate the working folder created earlier to link it with the database.

Commit Changes to Working Folder

Go to Source Control Manager and check (Commit) the newly created Book table into the Working Folder source control.

Check the Working Folder to see the Book table there.

Push Changes to Git Source Control (Book Table)

Open Git GUI again and click Rescan button which should show the table object now, add the following initial commits:

Initial Commit (the Book table created the first time)

Then do the following steps:

1. Stage Changes
2. Commit Changes
3. Push (Changes)

Alternatively, you can use Git Bash to run Git from the command line.

View Changes Committed to Git Source Control

Navigate to Azure DevOps webpage, provided you are already signed and the SQLBookShopV3-Using-Working-Folder-with-Git project is active.

Click Repos on the left navigation bar to view the changes just committed to Git Source Control.

Next, check the table script.

Add Stock and Price Columns

Now add two more columns Stock and Price to the Book table by using the following script.

CREATE TABLE SQLBookShopV3.dbo.Book (
  BookId INT IDENTITY
 ,Title VARCHAR(100) NULL
 ,Author VARCHAR(50) NULL
 ,Price DECIMAL(8, 2) NULL
 ,Stock SMALLINT NULL
 ,CONSTRAINT PK_Book_BookId PRIMARY KEY CLUSTERED (BookId)
) ON [PRIMARY]
GO

The table should look like as below.

Commit Changes to Working Folder

Save the most recent definition of the Book table which now contains two extra columns to Working Folder Source Control as shown below.

Locate Working Folder using Windows Explorer and open the dbo.table.sql in notepad to see the code.

The Working Folder contains the most recent definition of the table and does not provide any information about the first shape of the table.

As discussed, this is the limitation of Working Folder that we can only see the latest version of the database which gets overwritten by newer versions thereby leaving no room to trace back the (database change) history.

Push Changes to Git Source Control (Stock and Price Columns)

In the next step, we are going to push the newly added columns of the table to the Git Remote Repository as shown below.

Trace Database Changes with Git Source Control

So far, we have done two main database changes in the following order:

1. The Book table was created with the BookId, Title and Author columns
2. The Price and Stock columns were added to the Book table

There is no way to see the first change when еру Book table was originally created using Working Folder.

However, it is possible to see all the history of database changes using Git as long as we have pushed those changes to Git Remote Repository.

On the Azure DevOps, please click Pushes on the left navigation bar to see the historical changes of the database.

Navigate to Commits to view the order of database changes in the form of commits.

Click the first Commit a99df4b5 to see the first definition of the Book table.

Go back and click the next commit 6f863f0a to see the next database change(s).

Congratulations! We have successfully tracked the database changes using Working Folder with a secondary Source Control (Git).

We can now revert to the first version of the table if desired or carry on adding more database objects.

Things to Do

You can now comfortably not only put your database objects under Working Folder source control but also keep track of all the database changes there by maintaining database history.

1. Please try to create another database by linking the Book table with the BookType table in such a way that the BookTypeId primary key of the BookType table is passed as the BookTypeId foreign key column in the Book table and use working folder source control to track database changes.
2. Please try creating the Watches database as seen in the first diagram of the article and follow the steps by maintaining the database changes history using Working Folder with Git
3. Please try to revert changes mentioned in the first diagram of the article when Dev2 accidentally deletes the Watch table created by Dev1 using Working Folder to track database changes.

Useful tool:

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