SQL Server labels

Applies to

ApexSQL Source Control

Summary

This article explains how to work with SQL Server labels in ApexSQL Source Control.

Description

SQL Server labels, in source control context, represent a snapshot of the current state of all objects in the source control repository. That state can be restored at a later stage if there is a need for that. There is no limitation in the number of created labels.

Labels are useful in case when some major changes will be done to the database, so that version can be reverted to a baseline if some issues appear. It is also helpful for creating builds so that a particular label can be tested and any subsequent changes won’t affect the integrity of the tested build.

Creating SQL Server label can be done through the Object Explorer panel from the context menu, using the Labels option:

The Object Explorer panel right-click context menu Label command

This action will open the Labels window:

The Labels window

To create a new label click the New button. When the New label dialog is opened, type in the name of the new label and comment that will be related to that label and click the Create button:

The New label dialog

After the label is created, the following confirmation message will be shown:

Label successfully created

Closing this message will trigger the SQL Server label list refresh and when that is done the newly created label will be present in the Labels window:

Newly created label

Once the label is created, it is stored in the source control repository so it can be accessed by all developers. For any database linked to that source control repository, the created label will be shown in the Labels window as well and can be applied as well.

SQL Server label cannot be created on an empty source control repository, at least the initial commit should be done. Try to create a label before the initial commit is done will result in this message:

Label cannot be created in an empty repository

To delete any of the present labels on the source control repository, select it from the list in the Labels window and click the Delete button:

Deleting a label

After the Yes button is clicked and if the deleting a label is gone without any issue, the following confirmation message will be shown:

Label deleted successfully

  • Note: Anyone who has access to the repository with proper rights can delete a label

Closing this message will trigger once again the label list refresh that will result in showing all available labels from that source control repository. In this case, the deleted label will not be present.

The Labels window

To get the latest labels list from the source control repository, click the Refresh button in the Labels window:

The Refresh button in the Labels winndow

To apply a created SQL Server label against a database, select the label from the list in the Labels window and click the Get button.

The Get button in the Labels window

This will open the Get changes from the repository window containing three tabs:

  • Sequences – list of the action that will be performed against a database in order to apply the selected label, sort out by the execution priority:

    The Get changes from the repository window - Sequences tab

  • Script – generated script that will be build/update the database to incorporate all changes from the selected label. From this tab, generated script can be saved by click on the Save as button and executed later:

    The Get changes from the repository window - Script tab

  • Warnings – all possible issues that may cause the script execution to fail will be listed under this tab:

    The Get changes from the repository window - Warnings tab

After the Apply button in the Get changes from the repository window is clicked, the selected label will be applied against a database, and if everything goes properly, the following confirmation message will be raised:

Label successfully applied to database

If there are any errors that will prevent applying the selected SQL Server label against a database, when the Get button is clicked in the Labels window the Script errors dialog will be shown:

The Script errors dialog

Once a selected SQL Server label is applied against a database is all the differences between the database and the current state of the source control repository will be shown on the Action center as any other change.

Applying a SQL Server label against a database linked in the shared development model where the database policies are set (Permissive, Restrictive) can be a little bit tricky. Applying a label is treated the same way as any other edit of database objects, so this wouldn’t be allowed unless all objects that are affected by the label are checked out (checked out and locked), as requested by the set database policy. If those conditions are not met, when the Apply button is clicked in the Get changes from the repository window for applying a label, the following messages will be raised (according to the set database policy):

Permissive policy is enabled on this database.

Restrictive policy is enabled on this database.

FAQs

Q: If one developer creates a label, can others apply it?

A: Yes, it can be applied by any developer who has a database linked to the same repository.

Q: What source control systems do ApexSQL Source Control work with that support labels?

A: Labels are supported in all source control systems that are available through ApexSQL Source Control, which is: Git, Mercurial, Subversion, Perforce, and Azure DevOps Server/Services.

Q: Can I see the exact script differences between objects in selected label and objects in a database or objects between two labels?

A: You cannot do that from the ApexSQL Source Control but it can be done using ApexSQL Diff.

Q: Can I build a new database directly from a label? If so how?

A: Yes. It can be built using the ApexSQL Source Control by creating a new database, linking it to the same repository where the label is stored, and applying that label against the database. A database can also be built from a label using ApexSQL Build, or ApexSQL Diff.

Q: Can I edit the comment in a label?

A: Not at this point. It will be possible in future releases, though.