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

  • 2nd commit – added job_position column

    ALTER TABLE dbo.jobs ADD job_position NVARCHAR(50);
    
  • 3rd commit – dropped the job_desc column from the jobs table

    ALTER TABLE dbo.jobs
    DROP COLUMN job_desc;
    
  • 4th commit – granted INSERT and SELECT permissions to the SteveBrown user

    GRANT INSERT
    	,SELECT
    	ON [dbo].[jobs]
    	TO [SteveBrown]
    GO
    
  • 5th 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
    
  • 6th 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 4th 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 6th changeset thus a SQL script in the lower right section represents a version of the jobs table from the 6th 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 6th 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 3rd 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 2nd changeset is selected from the drop down list, the result is as follows:

The version of the jobs table from the 3rd changeset (the right side) does not contain the job_desc column since that was a change we made prior to committing the 3rd changeset, while in the 2nd 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 4th 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 3rd changeset appears in the lower right section, along with the comparison results between the version of the jobs table from the 3rd 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 3rd changeset, changes committed in the 5th and the 6th 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 3rd changeset, the Object history form shows no differences between the version of the jobs table from the 3rd 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 3rd 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.

December 30, 2016