How to manually unlink a database from SQL source control (4230284)

During the database linking process to the SQL source control repository, based on the chosen development model, required framework objects will be created either in the database or on the SQL Server instance. Besides that, a working copy of the repository (basically additional folders containing scripts for all database objects, from the database and the SQL source control repository) will be created on the machine where ApexSQL Source Control is installed for every linked database.

Use this article as a guide on how to manually unlink a database from SQL source control if for any reason automatic unlink does not give the expected results.

Working copy of repository

At the same time the framework objects are created, during the linking process, the working copy of the repository will be created as well for the linked database.

To check where is the location of the working copy of repository go to ApexSQL main menu in SSMS, ApexSQL Source Control menu then click the Options command:

This will open the Options window, where under the General tab the location of the working copy of repository will be present:

On this location will be stored in the following folders and files:

• Data folder – scripts from the linked database as well as the scripts present on the SQL source control repository where the database is linked to, will be present in this folder, grouped by SQL Server and database accordingly
• Temp folder – here will be stored all temporal object scripts during the compare process in the Action center tab between the linked database and the SQL source control repository
• MyDefaults.xml file – this file will be created only if in the Options window the Save as my defaults button is clicked. It contains information about the option set from the tabs where the Save as my defaults button is present and clicked
• Options.xml file – this file contains all options set in the Options window for every linked database from machine and user
• RecentConnection.xml file – this file contains all information regarding connection of the last database linked to the SQL source control repository

• Note: The Working copy of repository location can be changed after the database is linked, but once it is changed, it will be changed for all linked databases as well as the ones that will be linked in the future

Under the Data folder, will be created the SQL Server folder where will be created separate folders for every linked database from that SQL Server:

Under the database folder, will be present all scripts that are necessary for the add-in to work properly on the linked database and to track changes against all linked objects from that database.

• Note: It is strongly recommended that all scripts present in the database folder on the Working copy of repository location never be altered/deleted manually by the user

All database folders in the Data folder on the Working copy of repository location will be created in the same way and contain the same type of information regardless of the chosen development model.

The Options.xml file

Besides the framework objects created in the database, and the working copy of repository folders with all needed object scripts, the Options.xml file will be created/updated as well during the linking process. The Options.xml file, by default, is stored in the working copy of repository location, by default C:\Users\\AppData\Local\ApexSQL\ApexSQL Source Control:

In the Options.xml file all information about the repository path, chosen development model, database name, included/excluded objects, script options settings, etc. All those information will be listed under the Connection section (between the and ):

Dedicated development model

This development model, by default, is used in the environment where every database developer has a copy of the same database on its SQL Server and connection between database developers are made through the SQL source control repository. This means that each database developer must link its database from its machine to the same SQL source control repository. During that process, the framework objects will be created in each linked database separately as well as the background folders on the machine where ApexSQL Source Control is installed.

The created framework objects, in the linked database under the dbo schema, are:

• Under the Programmability node, the Database Triggers sub-node – ApexSQL_SourceControl_DDLTrigger trigger:

  

• Under the Tables node, the System Tables sub-node – ApexSQL_SourceControl_DatabaseLog table:

  

• Note: For Azure SQL Database the ApexSQL_SourceControl_DatabaseLog table will be created under the Tables node. For Amazon RDS the framework objects will be created on the same location in the database like explained above

Besides the framework objects created in the linked database, the additional folders will be created on the Working copy of repository location as well.

So, in order to unlink manually a database linked to the SQL source control repository in the dedicated development model, the next steps must be followed:

• Delete the dbo.ApexSQL_SourceControl_DDLTrigger trigger
• Delete the dbo.ApexSQL_SourceControl_DatabaseLog table

• Dropping the dedicated framework objects can be done by executing the following SQL script:

  USE linked_database>
  DROP TRIGGER [ApexSQL_SourceControl_DDLTrigger] ON DATABASE
  DROP TABLE dbo.ApexSQL_SourceControl_DatabaseLog

• Close SQL Server Management Studio
• Go to the Working copy of repository location (explained above), under the Data folder, then the SQL Server folder and delete the database folder
• Open the Options.xml file (explained above) and navigate to the SCConnections section
• For the linked database will exist the Connection section with the database name written in the first row (DevelopmentDB)

• Delete everything between and for the specified database:

  

• Note: If that is the only database linked from the specific SQL Server, the SQL Server folder can be deleted as well

When SQL Server Management Studio is open, the manually unlinked database will be ready for linking again:

Shared development model

The shared development model is always used in the environment where several database developers are working at the same time on one shared database linked to one SQL source control repository. For this type of development model, slightly different framework objects will be created.

First of all, the framework objects will be stored in the separate database, by default ApexSQL database.

• Note: For Amazon SQL Database and Amazon RDS the framework objects will be created in the database that is being linked

Only one framework object database can be created per SQL Server, for all linked databases from that SQL Server. In that database under the ApexSQL_SourceControl schema will be created the following framework objects:

• Under the Tables node will be created the following tables:

   

  • ChangeLog
  • ExcludedObject
  • Object
  • Policies
  • UserMappings

   

• Under the Programmability node, the Stored procedures sub-node:

   

  • UpdateExcludedObjects
  • UpdateObjects

   

The ApexSQL_SourceControl_DDLTrigger will be created on a Server level under the Server Object node, the Triggers sub-node:

In addition to this, the database developer who linked a database in the shared development model will be automatically written in the ApexSQL_SourceControl.UserMappings table:

The shared development model requires that every database developer link the shared database from its machine to the same SQL source control repository. This way, all database developers will have their Working copy of repository and all the needed information stored on its machines regardless of the number of database developers working on the shared database.

Manual unlink of the shared database for only one database developer

Unlinking a database linked in the shared development model for only one database developer, while other database developers will continue to work on it, implies that everything related to the framework objects for that database should be left as is.

The framework objects for the specified database linked in the shared development model will be deleted only when the last database developer unlinks the last database from that SQL Server linked in the shared development model.

When only one database developer wants to manually unlink a database linked in the shared development model, and there are other database developers still working on that database, the next steps should be followed:

• Close SQL Server Management Studio

• Go to the Working copy of repository location, under the Data folder navigate to the SQL Server folder, find the database folder and delete it:

  

• Go back to the working copy of repository start location (by default C:\Users\\AppData\Local\ApexSQL\ApexSQL Source Control) and navigate to the Options.xml file:

Open the Options.xml file and navigate to the Connection section for the chosen database

Delete for the chosen database section Connection (everything between and )

Open SQL Server Management Studio

Check if in the ApexSQL_SourceControl.UserMappings table in the framework object database, the database developer information is still present by using the following query

SELECT *
FROM [ApexSQL].[ApexSQL_SourceControl].[UserMappings]

Note: To find a database name from DatabaseID specified in the ApexSQL_SourceControl.UserMappings table, execute this query:

SELECT DB_NAME(DatabaseID>) AS Database_Name

If it is still present, execute the next query:

DELETE [ApexSQL_SourceControl].[UserMappings]
WHERE DatabaseUser = '' AND DatabaseID = database_ID_from_UserMappings_table>

• Or by using the user ID form the ApexSQL_SourceControl.UserMappings table

  DELETE [ApexSQL_SourceControl].[UserMappings]
  WHERE ID = database_user_ID_from_UserMappings_table>

From that point on, the specified database will be unlinked for the current database developer and all information will be deleted from the framework objects, but the database will remain linked for all other database developers working on it.

Manual unlink of one shared database for all database developers

When several different databases on the same SQL Server instance are linked in the shared development model, all information regarding the database objects from all linked databases will be stored in one framework object database, by default ApexSQL database. When talking about the unlinking process for one database for all database developers working on it, it implies that only information about the unlinked database should be removed from the framework objects and all information will remain intact for all other databases from that SQL Server linked in the shared development model as well.

To achieve this when manually unlink a database linked in the shared development model, all database developers who linked that database in the first place should do the following:

• Close SQL Server Management Studio

• Go to C:\Users\\AppData\Local\ApexSQL\ApexSQL Source Control\Data\ on database developer machine and delete the database folder:

  

• Go back to C:\Users\\AppData\Local\ApexSQL\ApexSQL Source Control and open the Options.xml file

• Delete for the chosen database section Connection (everything between and )

  

When all database developers have done the above steps, the last database developer, with the most permissions, should do the following:

• Open SQL Server Management Studio

• From the framework object database, by default ApexSQL, everything regarding the unlinked database should be deleted. This can be done executing the following script:

  -- the ApexSQL_SourceControl.ExcludedObjects table
  DELETE [ApexSQL_SourceControl].[ExcludedObjects]
  WHERE DatabaseID = database_ID_from_ApexSQL_SourceControl.UserMappings_table>
  
  -- the ApexSQL_SourceControl.Objects table
  DELETE [ApexSQL_SourceControl].[Objects]
  WHERE DatabaseID = database_ID_from_ApexSQL_SourceControl.UserMappings_table>
  
  -- the ApexSQL_SourceControl.Policies table
  DELETE [ApexSQL_SourceControl].[Policies]
  WHERE DatabaseID = database_ID_from_ApexSQL_SourceControl.UserMappings_table>
  
  -- the ApexSQL_SourceControl.UserMappings table
  DELETE [ApexSQL_SourceControl].[UserMappings]
  WHERE DatabaseID = database_ID_from_ApexSQL_SourceControl.UserMappings_table>
  
  -- the ApexSQL_SourceControl.ChangeLog table
  DELETE [ApexSQL_SourceControl].[ChangeLog]
  WHERE DatabaseName = database_name_from_ApexSQL_SourceControl.ChangeLog_table

• Note: In the ApexSQL_Sourcecontrol.ChangeLog table are stored local changes made against the linked database. Only if there is no need to save those changes, the records should be deleted

After all the above-mentioned steps are done, the chosen database will be unlinked for all database developers, and all other databases linked in the shared development model from that SQL Server are preserved for further work:

Manual unlink of all shared databases on SQL Server for all database developers

When it comes to the manual unlink all databases linked in the shared development model from one SQL Server, it will mean that all additional framework objects should be deleted as well, not only data in them like in the previously mentioned cases.

In this case, the following steps should be fulfilled by all database developers that linked any database from that SQL Server in the shared development model:

• Close SQL Server Management Studio

• Go to C:\Users\\AppData\Local\ApexSQL\ApexSQL Source Control\Data\ and delete the database folder:

  

• Go back to C:\Users\\AppData\Local\ApexSQL\ApexSQL Source Control and open the Options.xml file

• For all databases linked in the shared development model from that SQL Server section Connection should be deleted (everything between and )

  

After this is done by every database developer, the database developer with the most permission on SQL Server (by default is the database developer who linked the database in the shared development model in the first place), should do the following steps:

• Open SQL Server Management Studio
• Navigate in the Object Explorer panel, Server Object – Triggers, and delete the ApexSQL.ApexSQL_SourceControl_DDLTrigger
• Expand the framework object database, by default ApexSQL database, navigate to the Tables node and delete all framework objects tables:

   

  • [ApexSQL_SourceControl].[ExcludedObjects]
  • [ApexSQL_SourceControl].[Objects]
  • [ApexSQL_SourceControl].[Policies]
  • [ApexSQL_SourceControl].[UserMappings]
  • [ApexSQL_SourceControl].[ChangeLog]
• In the same database, navigate to the Programmability – Stored procedures node and delete the add-ins procedures:

   

  • [ApexSQL_SourceControl].[UpdateExcludedObjects]
  • [ApexSQL_SourceControl].[UpdateObjects]

This can be done executing the following SQL script:

USE master
GO

DROP TRIGGER [ApexSQL_SourceControl_DDLTrigger] on ALL SERVER;

USE ApexSQL
GO

DROP PROCEDURE [ApexSQL_SourceControl].[UpdateObjects];
DROP PROCEDURE [ApexSQL_SourceControl].[UpdateExcludedObjects];

DROP TABLE 
[ApexSQL_SourceControl].[Objects],
[ApexSQL_SourceControl].[ChangeLog],
[ApexSQL_SourceControl].[Policies],
[ApexSQL_SourceControl].[UserMappings],
[ApexSQL_SourceControl].[ExcludedObjects];

DROP SCHEMA [ApexSQL_SourceControl];

EXEC sys.sp_dropextendedproperty @name = N'ApexSQL_SourceControl';

When these steps are done, all databases previously linked in the shared development model will be unlinked and ready for linking again: