I am writing this post as I recently faced error mentioned in title while I was migrating all objects from one SQL instance to another SQL instance as part of decommission plan of source instance.
On source server, there was transactional publication was configured with local distributor. If you restore a replicated database to another SQL instance, replication settings are not preserved and you need to recreate all publications and subscriptions.
As per MSBOL link "Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must re-create all publications and subscriptions after backups are restored"
Once I restored all the databases to new SQL instance, I noticed that publication and its subscription entries were being shown under “replication” folder while I had not configured them yet. This was strange for me and I decided to remove any stale replication entries by using stored procedure sp_removedbreplication.
After removing replication objects, “replication” folder was clean so I decided to proceed further with distribution configuration however got error message as below
"The server 'repl_distributor' is already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the exisiting Distributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard."
Upon checking further I noticed that I had created linked server “repl_distributor” manually by scripting out from source server hence getting above error. When I tried to delete the linked server, it did not allowed me to do so with below error
Cannot drop server ‘repl_distributor’ because it is used as a Distributor in replication.
In order to delete ‘repl_distributor’, I changed the linked server property “distributor” as false and then I was able to delete it.
Once ‘repl_distributor’ was deleted, I was successfully able to configure distributor and publication successfully and added a subscriber as well.
I would encourage to read more about ‘repl_distributor’ here.
Hope this article will help in troubleshooting issues in your environment!
Happy learning!!