Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 20 April 2020

How to solve User database listed in system databases

Due to some replication troubleshooting situation we ran some commands wrongly due to  we can't add publication in replication as it shows invalid object name of system replication tables. now user database shows in system databases tab as below



how to solve it to bring back to user database section.

solution:

it is due to if you run this below command user database replication tables could be added but his properties of is_distributor value change from 0 to 1.

as below


to solve this we need to run this below command in master




now u can check database listed in user database as see below



thats it . we bring user database to user database section from system database as see below

script:

select is_distributor,* from sys.databases where is_distributor=1

--Exec sp_adddistributiondb 'pubs'-------------Dont run it on prod server

--Exec sp_dropdistributiondb pubs