How to see the full version history of a SQL Server database object under source control and, if needed, revert changes

Challenge

One of the main benefits of a SQL database version control is that any version of an object committed to the repository, is available through the revision history. With that being said, browsing the history allows seeing all versions of the specific database object, committed over time, and reverting any version from the history in order to apply it against a database. By utilizing such functionality, a database can be brought back to a working state in case some change caused a problem

Solution

ApexSQL Source Control is a SQL Server Management Studio add-in that will be used through this article. It allows you to put a database under version control, commit all changes to the repository and easily revert any committed change from the history and apply it against a database.

Let’s introduce this particular use case of how to browse through the history of committed changes and revert specific changes from the history. We’ll have a sample database Pubs, linked to the repository with all objects initially committed. In order to have a proper list of changesets in the revision history, we’ll make the following changes (all changes are made against the jobs table) and commit them in a way described below:

• Initial commit of all objects

• 2^nd commit – added job_position column

  ALTER TABLE dbo.jobs ADD job_position NVARCHAR(50);
  

• 3^rd commit – dropped the job_desc column from the jobs table

  ALTER TABLE dbo.jobs
  DROP COLUMN job_desc;
  

• 4^th commit – granted INSERT and SELECT permissions to the SteveBrown user

  GRANT INSERT
  	,SELECT
  	ON [dbo].[jobs]
  	TO [SteveBrown]
  GO
  

• 5^th commit – changing the type of min_lvl and max_lvl columns from TINYINT to INT

  ALTER TABLE dbo.jobs
  
  ALTER COLUMN min_lvl INT NOT NULL
  GO
  
  ALTER TABLE dbo.jobs
  
  ALTER COLUMN max_lvl INT NOT NULL
  GO
  

• 6^th commit – adding a default constraint for the job_position column

  ALTER TABLE dbo.jobs ADD CONSTRAINT DF_job_position DEFAULT 'New position, not yet determined'
  FOR job_position
  

Assuming that the 4^th commit, where INSERT and SELECT permissions are granted to the user (SteveBrown) was not a good change, we’ll show how to browse through the commit history, find the version of the jobs table that contains the change related to permissions, revert that version and apply it against a database.

Browsing the history of the specific object

In order to browse the object history, right click on the jobs table in the Object Explorer pane and select the Object history item from the context menu:

The Object history form shows the list of all changesets in the upper left section (marked in the image below), that contain the selected object (in this case the jobs table).

The upper right section shows all the objects committed within a single changeset (marked in the image below), including the specified object, which is also highlighted. Since we changed the jobs table for the purpose of this article, that is the only object that will be shown. In case other objects are committed, in the same changeset, the user will be able to view the list of the objects in the upper right section, but other objects cannot be selected due to a fact that this is the object history dedicated to the jobs table only, so the jobs table will be highlighted by default.

The DDL of the jobs table from the highlighted changeset will be shown in the lower right section (marked in the image). In this case, we have highlighted the 6^th changeset thus a SQL script in the lower right section represents a version of the jobs table from the 6^th changeset:

Picking any other changeset from the list will show all the containing objects and a SQL script will be shown in the lower right section for the currently highlighted object. This way, the user can easily review the history of the committed changesets, find out which version of an object is committed in which changeset, when, and who performed the commit.

By default, the lower left section shows a version of the highlighted object from the database. For instance, when the jobs table is highlighted in the 6^th changeset, the lower left section loads the version of the jobs table from a database (no differences since we have committed the final change we made against the jobs table):

The two windows show the DDL of each version, but also do a character level comparison of the scripts

Besides comparing a version of an object from the specific changeset with a version in a database (current version), a selected object can be compared to any other version of the same object that resides in another changeset. For instance, if the 3^rd changeset is highlighted, we can compare it with a version from other changesets, just by choosing the one from the drop down list:

For instance, if the 2^nd changeset is selected from the drop down list, the result is as follows:

The version of the jobs table from the 3^rd changeset (the right side) does not contain the job_desc column since that was a change we made prior to committing the 3^rd changeset, while in the 2^nd changeset the job_desc column still exists (shown on the left side).

Reverting specific version from the history

We want to revert a version of the jobs table where the SELECT and INSERT permissions are not granted to the user, because that user shouldn’t have been given those permissions. We’ll treat this as a “bad change”, for the purpose of this article, so we’ll revert the version of the jobs table where such permissions are not granted. Since the “bad change” was committed in the 4^th changeset, we’ll highlight the previous changeset (3rd) in the upper left section of the Object history form:

The version of the jobs table from the 3^rd changeset appears in the lower right section, along with the comparison results between the version of the jobs table from the 3^rd changeset and a database version (current version):

By applying the version from the right side against a database, we’ll revert the jobs table to a state where permissions are not granted. Note that when applying a version of the jobs table from the 3^rd changeset, changes committed in the 5^th and the 6^th changeset are about to be reverted. See below on how to avoid this.

Applying changes against a database

In order to apply this change, we’ll click the Get button, in the lower right corner of the Object history form.

This gives us the exact sequence of tasks that will be performed against a database:

The Script tab shows the actual SQL script that will be executed against a database in order to revert the selected change:

The script is wrapped in a transaction, and in case it cannot be executed for any reason, it will be automatically rolled back and nothing will be applied against a database.

The script can be reviewed at this point, opened for additional editing in a new query window, if needed (using the Open button), or saved for later usage (using the Save as button).

At this point, it is important to mention that the SQL script can be edited in order to apply just a change related to permissions and avoid altering columns and dropping a constraint as shown above. In order to apply only the part of the script related to permissions that are granted to the user, we will use the Open button, to open a script in a new query window, and comment (or remove) the part of the script that is related to changes we don’t want to apply:

By executing the script, only specific changes will be reverted (changes related to permissions).

If we didn’t modify the script, but applied all differences from the 3^rd changeset, the Object history form shows no differences between the version of the jobs table from the 3^rd changeset and a current version in a database:

Since we applied only specific changes, the comparison between the version of the jobs table from the 3^rd changeset and the current version in a database is as follows:

This is a confirmation that no other changes were applied expect the ones related to permissions.

Committing new changes

Since we changed a database when applying changes from the repository, those will be shown in the Action center tab. Now, the part of the script related to permissions does not exist on the left side (database) but it exists on the right side which is the latest state on the repository:

By committing this, we’ll have a version of the jobs table without the SELECT and INSERT permissions granted to the user, as the most recent (latest) version on the repository.

Using the described approach, the change history for any object can be reviewed, any version of an object can be compared to any other version of the same object from a different changeset and applied against a database.