Similarly, as described previously in this article, where the revision history is covered for the Git source control system, we’ll present the workflow of reviewing the history of committed SQL database objects using Team Foundation Server (TFS) source control system. In order to use TFS and have SQL database objects being version controlled, Visual Studio is required, as well as TFS server, either installed on a machine or TFS through Team Services, which is actually TFS “in the cloud”.
This article covers the following: revision history review, comparing between versions of the same SQL database object in two changesets, getting specific version of an object, and applying it against a database.
For the purpose of this article, TFS server is installed and initially set. Using Visual Studio, a new TFS project (repository) called StoreDB is created. It is assumed that the Visual Studio is installed in order to access the TFS server (in particular TFS repository). In addition to this, SQL database objects are already scripted, initially committed to the repository, and additional changes (shown below) are made against a database and committed to the TFS project in order to show the history of committed changesets. There will be no explanation about the installation process or setting up for the TFS server/repository, performing commits, and making changes in SQL database.
As a starting point, the following changes are committed to the repository:
Initial commit of all database objects
Created a new table dbo.Currency using the following script:
1234CREATE TABLE [dbo].[Currency]( [CurrencyCode] [nchar](3) NOT NULL ,[Name] NVARCHAR(20) NOT NULL ,[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Currency_ModifiedDate] DEFAULT(getdate()) );A column renamed from Name to CurrencyName in the dbo.Currency table using the following script:
1EXEC [sys].[sp_rename] N'dbo.Currency.Name' ,'CurrencyName'
Revision history review using Visual Studio
In order to get to the history of committed changes in Visual Studio, navigate to the Source Control Explorer pane, from the Team Explorer pane:
This shows a list of all objects that are being version controlled, under the StoreDB project:
To access the project history, make sure that the Folders icon is selected in the main toolbar, which will show the TFS project structure. Right click the project (in this case StoreDB) and select the View history option:
This initiates the History tab, showing the list of all committed changes:
For instance, the list shown in the above image represents changes performed against a database that are committed to the TFS project. From this point, the only way to explore the exact changeset is using the Comment column. To inspect the specific changeset in details, right click on it and select the Changeset Details option:
This will open the changeset details in the Team Explorer pane, showing all information from the previously inspected list of committed changesets that includes the timestamp of the commit (in this case 05/27/2016 11:04:14 AM), changeset ID (Changeset 7), comment (Created new table dbo.Currency) and a list of all files included in the changeset (in this case it is a single SQL file for the dbo.Currency table):
In the right click menu of any file from the changeset, there are options to open/review the file (either in the Source Control Explorer pane or to review the actual script in the new tab):
For instance, clicking the Open command opens the actual SQL script of the selected object:
The above image shows the version of the dbo.Currency table from the specific changeset (in this case Changeset 7). To view the entire history for the single object, right click on it (from the specific changeset through the Team Explorer menu as shown above, or from the Source Control Explorer tree), and select the View history option. Since the above right-click menu is shown from the Team Explorer pane, the following is the option shown in the Source Control Explorer right click menu:
Choosing any of the above presented options to review the history of committed changesets for the specific object gives the same result:
The above image shows the list of all changes made against the specific object (in this case the dbo.Currency table), along with the information who made changes, when, and what is the comment used when the change was committed.
Compare between versions
There are a lot of comparison options in TFS. On top of that, you can compare one project with any other project as well as any folder/file under the project with any folder/file from other project. For expedience sake, we’ll not go in details about explaining all of the combinations. Instead, we’ll focus on a comparison between different version of the same object in two changesets. For this purpose, we’ll use the dbo.Currency table since it is initially added in the first commit, the Name column is renamed to CurrencyName, and committed in another changeset.
To compare two changesets, right click on one of them, from the history of committed changesets and choose the Compare option (the same can be achieved by highlighting the changeset and clicking the Compare button from the toolbar):
This initiates the Compare form, where the source and the target for the comparison should be specified. By default, the changeset that is highlighted in the list or right-clicked, will be set as a source (The Source Path field is set to point to the StoreDB project, and the previously highlighted Changeset 7 is already specified). The user should specify the target changeset (in this case, we specified the same project as the Target Path value).
The target version can be either a changeset, the latest version, a label, or the version of an object from the local workspace, which is actually the latest state of the object that is not yet committed to the repository. Since the goal is to compare the version of the dbo.Currency table from the Changeset 7 (where the initial version of the table is committed), and the Changeset 8 (one of the columns is renamed), we will set the target to Changeset 8:
After clicking the OK button, the comparison will run, giving the following results in this particular case:
At this point, the comparison confirms that there are differences. In case there are multiple files committed in a single changeset, all of them will be shown after the comparison, on the appropriate side (source or target), along with the information about the differences. In order to see the exact differences, right click on the pair of objects in the list, and select the Compare Files option (or highlight the pair and click the Compare button from the upper toolbar):
This will give the line-by-line comparison of two different version of an object:
The result shows that there are actually two different lines, the one for the renamed column, and another one that holds the timestamp of saving the script. This way, the version of an object from one changeset can be compared with another version of the same object from another changeset.
Get specific version
To get specific version of an object, navigate to the history for that object, and right click on the changeset and select the Rollback Entire Changeset option:
All changes from the selected changeset will be reverted locally, and prepared to be committed:
In case there are multiple files in a single changeset, but only specific ones are meant to be reverted, simply undo the unwanted files using the Undo option from the right-click context menu:
Since the changeset where the dbo.Currency table is committed contains a single object, there’s nothing to be reverted. By providing the check-in comment and clicking the Check-in button, the previously reverted change will replace the latest version of the dbo.Currency table on the repository. This way any version of an object from the history of committed changesets can be reverted.
He uses his spare time to play guitar, ride a bike and hang out with his friends. During winter, he likes skiing the most, but all other snow activities, too.
He is also author of various SQL Shack articles about SSIS packages and knowledgebase articles about ApexSQL Doc.
View all posts by Marko Radakovic