Applies to
Summary
This article explains how to get (pull, apply) changes from the source control repository against a database in the database source control using ApexSQL Source Control.
Description
When talking about database development, tracking changes made against a database is one of the most important options. Besides that, the imperative is to have a possibility to revert a database to the last committed state without jeopardizing its stability. Both of these options will become available in the database source control.
Depend on the used source control system, a number of database developers and a number of the changes made by them, reverting the database to the last committed state process can be complicated and time-consuming. Using ApexSQL Source Control for the database source control, no matter which source control system is used, or how many database developers are working on a database at the same time, or even how many changes are there, the getting changes from the source control repository process will be the same and it will be done in a couple of clicks and more important will preserve the database stability.
This article will explain how to get changes from a source control repository for databases linked in the dedicated or shared development model using ApexSQL Source Control for the database source control.
Linking and initial commit for database source control
The prerequisite for using this feature is to have a database under the source control, the database must be linked to the source control repository and initial commit performed. This feature is not related to the development model (dedicated or shared) in which the database is linked to the source control repository.
In the How to link a SQL Server database to a source control system article additional information about the linking process to the source control repository can be found.
After the initial commit is performed, and the database and source control repository are synced, the Action center tab will look like this:
|
Quick tip:
The Undo button is available for databases linked to the dedicated development model. In the How to revert local changes for database source control article, additional information about how to revert local changes before they are committed to the source control repository can be found |
After a linked database is changed (any database object schema is changed) the Action center tab message will be changed as well:
In the database source control, to start the comparison process between the database and the source control repository state, click on that message, or click the Refresh button in the top right corner of the Action center tab. Results of the comparison process will be shown in the Action center tab in a way that all changed objects, the objects which database and repository scripts are not the same, will be listed:
The shown state is a result of the comparison between the linked database at that point in time and the last committed changes on the repository.
Working in the database source control, committing to the source control repository does not have to be performed after every change made against a database. Commit can be performed once a day if the workflow allows it, but refreshing the Action center tab after every made change will provide the current differences between the linked database and source control repository state.
The only difference between the dedicated and shared model in the Acton center tab, beside the Undo button, is that for the shared development model additional column, the Edited by column will be present:
Note: In the database source control, working in the shared development model assumes that more than one developer is working on a database, which is not the case with the dedicated model, and only because of that the Edited by column is present in the Action center tab.
Getting schema changes from the source control repository against a database
Since the whole get changes from the source control repository process is the same for both development models, the dedicated model will be used as an example.
Changes made against listed database objects in the Action center tab can be reviewed in the differences preview section before they are committed to the source control repository or reverted to the previously committed state. To review made changes, click on the listed object and the database script in the left script panel will be shown, as well as the repository script in the right script panel:
Through the Action center tab, the last committed state of any of the listed objects or all of them at the same time can be applied against a database. The whole process of getting the changes from source control in the database source control will be explained using changes on several database tables under the same schema, the Person schema:
- AddressType
- BusinessEntityAddress
- EmailAddress
The database source control schema changes made against these tables are adding a new column, deleting and renaming the existing columns:
Getting changes from the source control repository, revert the made changes against a database, means that the newly created column will be dropped, the delete column will be re-created (added) and the renamed column will be renamed to its original (before the change) name.
Check the objects from the list for which the database state will be replaced with the last committed repository state. In our case it is all of them, so right-click on any listed object and from the context menu choose the Check all command:
Right-click again and from the context menu choose the Change action to Get command to change the action in the Action column from commit to source control (blue arrow pointing to the right) to commit to the database (black arrow pointing to the left):
And click the Apply button:
This action will open the Get changes from the repository window where all necessary information for this process will be listed in three tabs:
- Sequence – this tab contains all actions that will be performed listed in the execution order. In our case, it will look like this:
-
Script – this tab contains the SQL script that will be executed in order to apply changes from the source control repository against a database. To save this SQL script and execute it in SQL Server Management Studio, click the Save as button located in the lower-left part of this tab:
-
Warnings – in this tab will be listed all potential problems that may affect the applying changes from the source control repository against a database. In our case, it will be listed only one potential problem related to the column data:
|
Quick tip:
To prevent this type of potential problem in the database source control, before any table schema changes, link static data to source control as well, so all changes that can affect data change, be tracked and applied if needed |
To apply the last committed state from the source control repository against a database, click the Apply button:
If the process was successful, the Action center tab will be refreshed, and the database and repository state will be synced again:
Getting data changes from the source control repository against a database
In the database source control, table data changes are often and with ApexSQL Source Control, they are tracked in the same way as the schema changes.
By default, after the database is linked to the source control repository, the static data from the tables are not linked and tracked. More information about how to link static data for the tables can be found in the Working with static data article.
|
Quick tip:
After the static data for the table is linked, any schema change will be followed with the static data change as well |
Even though in the database source control, the getting data changes from the source control repository process is the same as for the schema changes, few rules must be followed. For the purpose of this article, two tables will be used; one with schema and data changes (Person.Person) and one with only data changes (Person.PersonPhone). After the changes are made, and the Action center tab is refreshed, this will be listed:
Static data changes will be listed under the Static data type in the Type column and with [Data] added to the object name in the Database and Repository columns, as shown in the image above.
Since the Person.Person table has schema changes as well when only the static data is checked, the action changed to the commit to the database and the Apply button clicked the following message will appear:
In this case, when the schema and data changes are made against the same table, to get the last committed data changes from the source control repository and apply them against a database, both changes listed in the Action center tab must be checked and action changed to commit to the database before the Apply button is clicked:
When static data is tracked in the database source control, the Get changes from the repository window will have different sequences than before when only schema changes are tracked. One of the sequences will be Insert Into Person.Person table:
And the Warnings tab will be empty:
If there is only static data change in the database source control, like in this case for the Person.PersonPhone table, there will not be this type of restriction. To get the last committed static data change from the source control repository and apply it against a database, check the listed change, change the action to commit to the database and click the Apply button:
Under the Sequence tab, only one step will be listed since only the static data will be changed:
The rest of this process in the database source control will be the same and it has already been explained.
Getting specific change from a source control repository
The explained process is applied only to the last committed repository state. When talking about the database source control, there is often a need to get the specific version of an object or the whole changeset from the source control repository. That can be done through the Project and Object history window.
The Project history feature is only available on a database level, and the Object history feature on an object level:
More about these features can be found in the Historical view article.
