How to manually unlink a database from SQL source control

Applies to

ApexSQL Source Control

Summary

This article will explain how to manually unlink a database from the SQL source control repository and remove all background files created by ApexSQL Source Control.

Description

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 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 file – this file contains all options set in the Options window for every linked database from machine and user
• RecentConnection file – this file contains all information regarding the 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 file

• Note: In ApexSQL Source Control 2021 version and higher, the extension of the Options file changed from .xml to .json

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

In the Options 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 <Connection> and </Connection>):

Options.xml

For the ApexSQL Source Control 2021 version and higher, all database information are listed between “DatabaseInfo” for that database (under the “DatabaseName” will be listed that database name) and “},” before the next “DatabaseInfo”:

Options.json

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:

  

• Under the Programmability – Functions – Scalar-valued Functions node, the ApexSQL_SourceControl_FrameworkVersion function:

  

• 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:

• Drop the dbo.ApexSQL_SourceControl_DDLTrigger trigger
• Drop the dbo.ApexSQL_SourceControl_DatabaseLog table
• Drop the dbo.ApexSQL_SourceControl_FrameworkVersion function

• 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
  DROP FUNCTION dbo.ApexSQL_SourceControl_FrameworkVersion

• 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 file (explained above) and delete everything related to 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 Functions node, the Scalar-valued Functions sub-node:
    • FrameworkVersion

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\<user>\AppData\Local\ApexSQL\ApexSQL Source Control) and navigate to the Options file:

  

• Open the Options file and delete everything related to the chosen database (explained above)
• 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 = '<user_name>' AND DatabaseID = < database_ID_from_UserMappings_table >

• Or by using the user ID from 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\<user>\AppData\Local\ApexSQL\ApexSQL Source Control\Data\<SQL_Server> on database developer machine and delete the database folder:

  

• Go back to C:\Users\<user>\AppData\Local\ApexSQL\ApexSQL Source Control and open the Options file

  Delete everything in the Options file related to the specified database (explained in the Options file section)

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.

• Note: For Azure SQL Database and Amazon RDS, the framework objects will be stored in the linked database

This can be done by 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\<user>\AppData\Local\ApexSQL\ApexSQL Source Control\Data\<SQL_Server> and delete the database folder:

  

• Go back to C:\Users\<user>\AppData\Local\ApexSQL\ApexSQL Source Control and open the Options file

  For all databases linked in the shared development model from that SQL Server Delete everything in the Options file related to the specified databases (explained in the Options file section)

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
  • Note: For Azure SQL Database, Amazon RDS, SQL Server versions 2005, 2008, and 2008RS, DDL Trigger will be stored in the linked database, under the Database Triggers node

• 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]

• Under the Functions – Scalar-valued Functions node delete the add-ins function:
  • FrameworkVersion

This can be done executing the following SQL script:

/* SQL Server 2012 and higher */
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 FUNCTION [ApexSQL_SourceControl].[FrameworkVersion]

DROP SCHEMA [ApexSQL_SourceControl];

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

/* SQL Server 2005, 2008 and 2008R2 */
USE < linked_database >

DROP TRIGGER [ApexSQL_SourceControl_DDLTrigger] ON DATABASE

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 FUNCTION [ApexSQL_SourceControl].[FrameworkVersion]

DROP SCHEMA [ApexSQL_SourceControl];

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

/* Azure SQL Database and Amazon RDS */
USE < linked_database >

DROP TRIGGER [ApexSQL_SourceControl_DDLTrigger] ON DATABASE

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 FUNCTION [ApexSQL_SourceControl].[FrameworkVersion]

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: