How to fix a messed up replication on MS SQL Server

TLDR:

It appears that disabling and re-enabling replication probably fixed the issue:

exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'false'
exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'true'

I guess this is the equivalent of switching it off and then back on again...

Longer version:

A workmate had a go at trying to fix it. He tried a few things but didn't get very far. The one change he did make before giving up was to disable the replication.

I then tried Cody's suggestion. The sp_dropsubscription command complained that no subscriptions exists. So I tried the sp_droppublication command. This complained that replication was not enabled on the database. So I enabled it and re-ran the command. This time it complained that the publication did not exist. I refreshed the Local Publications node in SSMS and sure enough it had gone. I ran the replication set up script, generated a new snapshot and every thing is now working properly. Joy!

I'm not 100% certain that disabling and enabling the replication is what actually fixed the problem, but it is definitely worth trying if the replication gets messed up.

I had a mess with replication and solved it with this

DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'DBName'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO 

That and:

exec sp_cleanupdbreplication

Are the saviours when cleaning up messed up replications.

Restoring the database will break replication, so that's normal. Also most of the other error messages are just follow ons because you haven't been able to remove all the subscriptions (or at least SQL thinks so).

You know you have your publisher (the source database), and at least one subscriber (the destination database), and that these are two different servers. I just want to mention that there's also a distributor which is on either of these servers or another, and likely in a database named distribution. Sometimes it has some useful info in there and sometimes things fall over because the information between the three doesn't match.

Anyway, when you checked the subscribers, did you also check that section on the publisher server to make sure there was nothing else listed? If you find any you can try to remove it manually:

exec sp_dropsubscription @publication = N'xxx', @subscriber = N'xxx', @destination_db = N'xxx', @article = N'all'
-- And if that doesn't work
exec sp_dropsubscription @publication = N'xxx', @subscriber = N'xxx', @destination_db = N'xxx', @article = N'all', @Ignore_Distributor = 1

But assuming they really are all gone, try this on the publisher database:

exec sp_droppublication @publication = N'xxx'
-- And if that doesn't work
exec sp_droppublication @publication = N'xxx', @Ignore_Distributor = 1

Let us know how it goes. Replication when it gets into this state confounds me and other good DBAs nothing to do with being a developer at all :-)