How to work with SQL database source control labels (4307110)

A SQL database source control “label” or “tag” (aka revision tag) (name depends on the particular source control system) represents a snapshot in time of the source control repository and its contents. It can be saved as a reference for the future use. When the database development cycles reach a particular milestone e.g. a new build, a source control label can be created as a checkpoint. The team can continue to work on the database but revert to the source control label at any time.

Challenge

The source control label solution should provide a possibility to perform the following operations:

• Create a source control label by taking a snapshot of the current state of the repository
• Get the latest source control label (or any other version) from the repository
• Compare and synchronize a source control label with a database, database backup, or a local folder that contains SQL scripts and synchronize any of these with the selected label
• Compare and synchronize a source control label with the current state in the repository
• Apply a source control label from the repository to the database
• Build a new database, from a label, or update an existing database

In addition to this, the solution should provide a possibility for the user to create a SQL script from a source control label (to create or modify a database) and save it for later execution or modification.

Requirement

The following ApexSQL tools are required for the following example: ApexSQL Build, ApexSQL Diff, ApexSQL Source Control, and ApexSQL Script. Feel free to download them and work along with the article

Solution

Create a label

To create an entirely new version of label of a database schema, you can use either ApexSQL Source Control or ApexSQL Script. The result will be the same, but achieved in two ways.

Using ApexSQL Script to create a new label

Connect to a database that you want to create a source control label for, by initiating a new project and selecting the particular database:

After ApexSQL Script finishes analyzing the database, select objects that will be included in the label and click the Script button to initiate the Script wizard. Select the Structure scripting mode and the T-SQL output type:

In the Output file step, under the General tab, choose the Create and commit to source control option, and use the Edit button to specify the source control system and repository information where the label will be created. In the Label field, specify a label name:

ApexSQL Script will create a separate script for all selected database objects along with the appropriate structure on the repository that can be used by other ApexSQL tools. After the initial commit of all objects, which is done automatically by ApexSQL Script, a label with the specified name will be created.

For the purpose of the article, TFS source control system is used, therefore the created label can be found under the history of the source control project. If the Labels tab is set as current, previously created label will be shown:

Using ApexSQL Source Control to create a new label

ApexSQL Source Control, on another hand, requires the database to be linked to the repository where the label will be created, and at least having database objects initially committed. When the database is linked with objects committed to the repository, a label can be created from the ApexSQL Source Control menu, in the Object Explorer, by clicking the Create label option:

In the Label form, specify a label name and a description (optional) and click the Create button:

The label will be created on the repository:

Compare a label with a database

ApexSQL Diff is a tool that can compare and synchronize source control label with various database sources such as live database, database backup, database snapshot, or local folder that contains SQL scripts. To compare a label with any of the mentioned sources, in the ApexSQL Diff Project form, set the Source control option from the source drop down list:

From the Source control wizard, in the System login step, the Version section at the bottom must be set to Get by label and the appropriate label should be selected:

On a destination side, an appropriate target needs to be selected and configured:

The same repository can be set as a destination, and ApexSQL Diff can compare the current state on the repository (latest version), with the previously selected label.

When the comparison is done, ApexSQL Diff shows all the differences between the selected label and the destination database:

Synchronize a label with a database

After reviewing the differences and making a selection of objects that will be synchronized, clicking the Synchronize button from the Home tab will initiate the synchronization process:

The synchronization process is managed through the Synchronization wizard where dependent objects can be included; all actions that will be executed can be reviewed and in the final step, the application offers the Synchronize now or Create a synchronization script options:

Later one allows reviewing the synchronization script, modifying it additionally if needed, before the execution.

By clicking the Done button, the selected command will be executed.

Apply the label against a database

To apply a label against a database, ApexSQL Source Control or ApexSQL Diff can be used.

Applying a label using ApexSQL Source Control requires a database, where label will be applied, to be linked to the existing repository where the label is created. After linking the database, right click on it from the Object Explorer and from the ApexSQL Source Control menu, select the Show labels command:

From the labels form, select the label to apply, and click the Get button:

ApexSQL Source Control pulls the label from the repository and creates a script that will be applied against a database. The script itself, as well as all dependent objects, actions that will be performed are available for review before applying a label:

By clicking the Apply button, the selected label will be applied against the liked database.

Applying a label using ApexSQL Diff is nothing more than compare and synchronize a label with the selected database, which is already explained in this article.

Build a new database from a label

ApexSQL Build can use a label to build a database and deploy it directly to the specified SQL server or pack it as a T-SQL script, C#, or .NET executable. To build a database from a source control label, click the Build a new database option on the start screen:

In the next step, select the output type:

In the Input source step, select the Source control

Set the source control configuration options, in the same way as explained for ApexSQL Diff, to use the previously created label.

Specify the new database name that will be created from the label and deployed directly to a server:

Next, select object to be included in the deployment process:

Inspect for dependent objects and add them if exist and review the summary of the process:

In the final step, the build script can be saved as .sql file for later usage. Clicking the Build button will build and deploy a database from previously created label, directly on the specified server: