Sql-server – Drop a replicated subscription table that has been orphaned after restore migration

replicationsql serversql-server-2012sql-server-2016transactional-replication

We restored a database on a new server for a migration.

When attempting to set a subscription to a transnational publication, we get the error:

Can't drop the table because it is set for replication.

However, it is not being replicated to.

I have already set several push subscriptions up from this server that go to another so I don't want to completely wipe out all replication and rebuild everything.

I was wondering if there is a place that I could delete the piece of information that makes the article think it is still replicated to, so that I can add it back to a subscription

Date        1/29/2018 3:42:13 PM
Log     Job History 

Step ID     1
Server      Server1Pub
Job Name        RepJobName
Step Name       Run agent.
Duration        00:00:05
Sql Severity    0
Sql Message ID  0
Operator Emailed    
Operator Net sent   
Operator Paged  
Retries Attempted   0

Message
2018-01-29 21:42:13.174 Copyright (c) 2016 Microsoft Corporation
2018-01-29 21:42:13.174 Microsoft SQL Server Replication Agent: distrib
2018-01-29 21:42:13.174 
2018-01-29 21:42:13.174 The timestamps prepended to the output lines are expressed in terms of UTC time.
2018-01-29 21:42:13.174 User-specified agent parameter values:
            -Publisher Server1Pub
            -PublisherDB DBPub
            -Publication PubName
            -Distributor Server1Pub
            -SubscriptionType 1
            -Subscriber Server2Sub
            -SubscriberSecurityMode 1
            -SubscriberDB db1
            -XJOBID 0x466A0D3EF212C442B2B0E6521144ED71
            -XJOBNAME ReplicationJobName
            -XSTEPID 1
            -XSUBSYSTEM Distribution
            -XSERVER Server2Sub
            -XCMDLINE 0
            -XCancelEventHandle 0000000000006F44
            -XParentProcessHandle 000000000000592C
2018-01-29 21:42:13.174 Startup Delay: 2294 (msecs)
2018-01-29 21:42:15.474 Connecting to Subscriber 'Server2Sub'
2018-01-29 21:42:15.509 Connecting to Distributor 'Server1Pub'
2018-01-29 21:42:16.279 Parameter values obtained from agent profile:
            -bcpbatchsize 2147473647
            -commitbatchsize 100
            -commitbatchthreshold 1000
            -historyverboselevel 1
            -keepalivemessageinterval 300
            -logintimeout 15
            -maxbcpthreads 1
            -maxdeliveredtransactions 0
            -pollinginterval 5000
            -querytimeout 1800
            -skiperrors 
            -transactionsperhistory 100
2018-01-29 21:42:16.864 Initializing
2018-01-29 21:42:17.239 Snapshot will be applied from the alternate folder '\\192.168.5.124\Replications\unc\Folder'
2018-01-29 21:42:17.914 Agent message code 3724. Cannot drop the table 'dbo.table' because it is being used for replication.
2018-01-29 21:42:18.144 Category:COMMAND
Source:  Failed Command
Number:  
Message: drop Table [dbo].[table]

2018-01-29 21:42:18.194 Category:NULL
Source:  Microsoft SQL Server Native Client 11.0
Number:  3724
Message: Cannot drop the table 'dbo.table' because it is being used for replication.

Best Answer

When you migrate the database all the information goes together.

Try this at the new server:

USE master
GO

EXEC sp_replicationdboption @dbname = N'YOUDATABASENAME', 
                            @optname = N'publish', 
                            @value = N'false';
GO

EDIT

OK, my first answer will kill all replication at the new server.

This will kill the subscriber at the table/article and after drop the article itself from replication:

USE YOUR_DATABASE
GO

EXEC sp_dropsubscription @publication = N'YOUR_PUB', 
                         @subscriber = N'SUBSCRIBER_SERVER', 
                         @destination_db = N'DATABASE_DSESTINATION', 
                         @article = N'TABLE';
GO

EXEC sp_droparticle @publication = N'YOUR_PUB', 
                    @article = N'TABLE', 
                    @force_invalidate_snapshot = 0;
GO