Applies to
Summary
This article explains the best practices to revert local changes for database source control when the dedicated development model is chosen.
Description
ApexSQL Source Control supports two types of database development model when it comes to the database source control, dedicated and shared. Which one will be chosen depends on the work organization, environment and number of database developers.
No matter which database source control development model is chosen, the reality is that there will be a need for reverting database changes in the development process. ApexSQL Source Control provides an option for reverting the local changes when the database is linked in the dedicated development model.
Linking a database to source control
The pre-requisite for using this option is to have a database under the source control, linked to the source control repository in the dedicated development model.
To link a database to source control, right-click on a database in the Object Explorer pane, and from the context menu, select the Link a database to source control command:
Under the Development model tab in the Source control setup window, choose the Dedicated development model:
Additional information about linking a database to source control can be found in this article.
After the initial commit is done through the Action center tab, the database development can start.
The Action center tab options
The main source of communication between the database and the source control repository is the Action center tab, no matter which development model is chosen for the database source control.
If the dedicated development model is chosen for the database source control, the Action center tab will have an additional option for reverting the local changes made against a database before they are committed to the source control repository, the Undo option:
|
Quick tip: The dedicated development model allows only one developer per database; therefore, the Edited by column in the Action center tab will not be preset |
Reverting local schema changes using the Undo option
To revert a change made against a database, before the change is committed to the repository, using the Undo option, check the object(s) listed in the Action center tab and click the Undo button:
The Undo changes to database window will be shown:
All information about the reverting changes made against a database is divided into three tabs in the Undo changes to database window:
- Sequence tab – under this tab, all actions that will be performed to revert changes for the checked objects will be listed in the execution order. In our case, for the chosen changes, this tab will look like this:
-
Script tab – under this tab the SQL script, which will be executed to revert changes for the checked objects, will be shown:
To save the SQL script present in this tab, for later execution in SQL Server Management Studio, click on the Save as button in the lower-left part of the window:
- Warnings tab – all potential problems that may affect the object updating/reverting to its previous state will be listed under this tab. In our case, this tab looks like this:
Note: When working with tables, the above-shown warnings will be present if the static data for the selected tables are not included in the reverting part.
To revert changes made on the selected object(s), click the Apply button in the Undo changes to database window:
When the changes are reverted, the Action center tab will be refreshed and the objects, for which changes were reverted, will no longer be listed in the Action center tab:
Deleting the newly created object from a database
The already explained process for reverting database source control changes applied to objects that have already been committed to the repository in the initial commit. Using the reverting option, the Undo option, the newly created object that has not been committed to the repository yet, can be deleted from a database.
After a new database object is created and the Action center tab is refreshed, the object will be listed as a difference between the database and the source control repository state:
The Repository column, in this case, will be empty because the commit to the source control repository has not been performed yet.
To delete this object(s), to revert the change made against a database, the already mention approach can be used. The whole process of reverting/deleting the object is the same and has been already explained, using the Undo option in the Action center tab.
Reverting local static data changes using the Undo option
When talking about database source control, changes made against tables, besides the schema changes, include the static data changes as well, since both are included in the database development process. One of the biggest concerns when working with tables is how to preserve the table static data throughout the table schema changes.
ApexSQL Source Control provides the same tracking change options for table schema as for the static data. The workflow for database source control, when working with tables, should be:
- Link a database to source control repository
- Initial commit to source control repository
- Link static data for tables that will be worked on
- Commit static data to source control repository
From this point on, every schema change made against any table (adding/deleting columns) will be followed with the static data change as well and will be listed in the Action center tab as a separate change:
|
Quick tip: The static data change, besides the ‘’Static data’’ type in the Type column, will have [Data] added in the name of the table in the Database column |
Reverting local schema and static data change at the same time
To revert table schema change and corresponding static data change, both changes listed in the Action center tab must be first checked:
Then click the Undo button:
Now the sequences will be a little bit different. First, the schema changes will be reverted and then the static data changes:
Click the Apply button in the Undo changes to database window to revert changes:
When the process is finished, the Action center tab will be refreshed, and the selected changes will no longer be present in the Action center tab:
|
Quick tip: Reverting table schema and data change can be done in two separate steps. First, revert the schema change, then repeat the same process and revert the static data change |
Reverting local static data change only
When there is only table static data change (the table schema was not changed in any way), the listed changes in the Action center tab will be:
To revert only static data change, the process is the same as already explained. Check the static data change, and click the Undo button in the Action center tab:
The Undo changes to database window will be shown, in this case with only one sequence:
In this case, under the Script tab of the Undo changes to database window, the SQL insert data script will not be shown due to performance impact that may be caused by the script size:
After the Apply button is clicked and the reverting process is done, the Action center will be refreshed, and the reverted object will not be present in the Action center tab anymore:
Conclusion
The Undo option in ApexSQL Source Control provides sort of the ‘’safety net’’ for database development since every local change can be reverted without any consequences to the database state.
