Are you planning a big or lengthy update for your database? You have to disable all or some of your database’s objects, so they don’t interrupt with your update.  It sounds like lots of work. Not necessarily. Disabling the database’s objects can be done with PartitionDB Add-in. You can choose which objects to disable or you can choose to disable them all. Disabling the constraints, triggers and other objects will ensure a quick and smooth update to your database.

Once you are done, enable the database’s objects back, and resume the database’s daily routine.

Moreover, you can enable objects that were not disabled by this disable function. Any disabled object can be enabled using this Add-in.

Here is an example how a database’s objects are disabled, then changed and lastly, enabled back. Note that in order to use this Add-in you need to download PdbLogic.

How to use disable and enable database’s objects

The following example creates a database TestDisableDB and two tables: users and userTransactions . Users has a primary key and userTransactions is using a foreign key. In order to add new records we will disable the objects.

Create database and tables

First we create the tables and fill them out with data:

Having the database and tables up and running, we will now insert a new records to userTransactions table.

insert into userTransactions (userId,deposit,iTime) values (1,100,getdate())   -- succeded
insert into userTransactions (userId,deposit,iTime) values (6,100,getdate())   -- failed

Adding a record with id of 1 is successful, however, a record with id of 6 fails, since the ids in users table run from 1 to 5 (In real practical situation we will probably want to add a big amount of records). Here is the error returned message:

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint “FK__userTrans__userI__1273C1CD”. The conflict occurred in database “TestDisableDB”, table “dbo.users”, column ‘Id’.
The statement has been terminated.

Disable database’s objects

Adding this new record requires to disable the database’s objects, since the tables links will be temporarily broken. This example is dealing with foreign keys, but the function supports disabling all objects such as indexes, jobs, triggers and more. Please refer to the table of parameters at the end of this post to see the complete list of objects.

We will call the function passing no optional excluding parameter, meaning that all objects will be disabled. It will disable the foreign keys that we are interest in (plus all other objects):

exec PdbLogic..PdbdisableDatabase @TargetDatabaseName='TestDisableDB' -- disableDatabase
insert into userTransactions (userId,deposit,iTime) values (6,100,getdate())   -- succeded

Now adding new records is allowed. This is the the simplest way add big amounts of data to tables.

Enable back the database’s objects

At this point the relationship between the two databases is corrupted, and therefore, we will fail to enable the database:

exec PdbLogic..PdbenableDatabase @TargetDatabaseName='TestDisableDB'

Msg 50000, Level 16, State 1, Procedure printMessage, Line 51
MSG-90556 Failed to enable foreign key FK__userTrans__userI__1273C1CD on dbo.userTransactions on TestDisableDB
Msg 50000, Level 16, State 1, Procedure handleError, Line 201
ERR-00547 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK__userTrans__userI__1273C1CD”. The conflict occurred in

In order to resolve the problem, we first need to fix the relationship between the tables, and then we will be able to enable back the database’s objects.

insert into users (Username,Password,RoleId,DepartmentId) values ('User ' + cast(6 as nvarchar(max)),NEWID(),abs(cast(NEWID() AS binary(6))%100)+1,abs(cast(NEWID() AS binary(6))%10)+1);

exec PdbLogic..PdbenableDatabase @TargetDatabaseName=’TestDisableDB’

(1 row(s) affected)
MSG-60171 Successfully Enabled Unique UQ__users__536C85E425480DC4 on dbo.users on TestDisableDB
MSG-60171 Successfully Enabled Foreign Key FK__userTrans__userI__1273C1CD on dbo.userTransactions on TestDisableDB

Now the database is back to its regular activity. The constraints, triggers, jobs and foreign key are now active again. The indexes and the uniques will be rebuilt.

Here is the list of parameters for the PdbDisableDatabase function:

Parameter Description Mandatory
TargetDatabaseName Name of database to disable Yes
IsDisableCheckConstraints Pass true to disable constraints, pass false to keep enabled. Default is true Yes
IsDisableForeignKeys Pass true to disable foreign, pass false to keep enabled. Default is true Yes
IsDisableIndexes Pass true to disable indexes, pass false to keep enabled. Default is true Yes
IsDisableJobs Pass true to disable jobs, pass false to keep enabled. Default is true Yes
IsDisableTriggers Pass true to disable triggers and false to keep enabled. Default is true Yes
IsDisableUniques Pass true to disable uniques, pass false to keep enabled. Default is true Yes
IsOutputScript Decide if to print the command or run it No
TargetLinkedServerName Name of linked server No

Below is the list of parameters for PdbEnableDatabase:

Parameter Description Mandatory
TargetDatabaseName Name of database to enable Yes
IsEnableCheckConstraints Pass true to enable constraints, pass false to keep disabled. Default is true No
IsEnableForeignKeys Pass true to enable constraints on foreign key, pass false to keep disabled. Default is true No
IsEnableJobs Pass true to enable jobs, pass false to keep disabled. Default is true No
IsEnableTriggers Pass true to enable triggers, pass false to keep disabled. Default is true No
IsOutputScript Decide if to print the command or run it No
IsRebuildIndexes Pass true to rebuild indexes, otherwise pass false No
IsRebuildUniques Pass true to rebuild uniques, otherwise pass false No
TargetLinkedServerName Name of linked server No

To use PdbDisableDatabase and PdbEnableDatabase Add-ins and other PartitionDB Add-ins, download our PdbLogic and attach to your database. .
To learn more about PartitionDB capabilities check out our blog.