none
Added a user database as distributor. RRS feed

  • Question

  • Hi Guys,

    I added a user database as <g class="gr_ gr_41 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="41" id="41">distributor</g> by mistake. I marked the database offline, dropped the database (so it won't delete data files), disabled replication and reattached the database. 

    The database shows up as system database. Is there any way to mark the database as user database now? I want to configure replication on it and I can't do that unless it's marked as a user database.

    Any quick help is appreciated.


    Let's Do It!!

    • Moved by Tom Phillips Wednesday, May 17, 2017 12:05 PM Probably better answer from Replication forum
    Wednesday, May 17, 2017 11:43 AM

Answers

  • Hi,

    I did some tests on my side. I already have distributor configured with few other distributor dbs configured.

    Here are the steps:

    1.Added user database TEST on distributor 

    2. Then run this: EXEC sp_adddistributiondb test . This moved TEST db to system databases and treated TEST db as a distribution db.

    To remove that DB I went into GUI Replication->Distributor Properties  and there I found TEST db on the list and choose it to DELETE. It did not remove entire database,it  just removed all system objects related to replication and move TEST db back to User databases. Did not delete data and log file at all.

    Do not disable entire replication on that distributor before you do that as you want have access to Distributor Properties.

    Test this maybe on some dummy user db first:)

    Wednesday, May 17, 2017 1:42 PM

All replies

  • What you seem to have done is the following:


    EXEC sp_adddistributiondb MyUserDatabase

    What you need to do is run this in your user database.

    sp_removedbreplication 'MyUserDatabase'

    Then backup the database and restore it.

    Wednesday, May 17, 2017 11:56 AM
    Moderator
  • Unfortunately backup and restore is not an option as it's a huge database (about 5 TB). We can detach/attach the database.

    Let's Do It!!

    Wednesday, May 17, 2017 11:59 AM
  • I don't believe that will work either. The only way I know of doing this is via a backup and restore after removing replication from the database.
    Wednesday, May 17, 2017 12:25 PM
    Moderator
  • Hi,

    I did some tests on my side. I already have distributor configured with few other distributor dbs configured.

    Here are the steps:

    1.Added user database TEST on distributor 

    2. Then run this: EXEC sp_adddistributiondb test . This moved TEST db to system databases and treated TEST db as a distribution db.

    To remove that DB I went into GUI Replication->Distributor Properties  and there I found TEST db on the list and choose it to DELETE. It did not remove entire database,it  just removed all system objects related to replication and move TEST db back to User databases. Did not delete data and log file at all.

    Do not disable entire replication on that distributor before you do that as you want have access to Distributor Properties.

    Test this maybe on some dummy user db first:)

    Wednesday, May 17, 2017 1:42 PM
  • Bartosz, I tried this myself on SQL 2016. I was unable to get it to work. What version of SQL did you try it on?
    Wednesday, May 17, 2017 1:50 PM
    Moderator
  • Sorry forgot to mention.

    Im using SQL 2014. Did u get any errors while trying to do this?

    Wednesday, May 17, 2017 1:53 PM
  • I got the database could not be deleted as it was in use.
    Wednesday, May 17, 2017 1:58 PM
    Moderator
  • Did u try to close all the connections to that database? There was no connection to that db while I was dropping from Distributor Properties.
    Wednesday, May 17, 2017 2:01 PM
  • No, I did not. I figured it was trying to drop it.

    I will try again.

    Wednesday, May 17, 2017 2:16 PM
    Moderator
  • I also tried to do the same thing and I thought it's trying to drop the database. so I didn't move any further. I will try creating a test <g class="gr_ gr_106 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="106" id="106">db</g> and try with that.

    Let's Do It!!

    Wednesday, May 17, 2017 3:12 PM
  • I tried creating a test database and adding that to <g class="gr_ gr_152 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="152" id="152">distributor</g> and then removing it. It did not drop the database although, it doesn't want anyone connecting to the database while doing so.  Thanks, it worked

    Let's Do It!!

    Wednesday, May 17, 2017 11:45 PM
  • Punteetm, I am running into the same problem you are with creating new publications. I think you should open up a support incident with Microsoft on this one.

    Note that my method of restoring the database will remove the database from the system databases folder in SSMS, but will not allow new publications to be created in that database. You will also be unable to create subscriptions to publications created in other databases.


    Thursday, May 18, 2017 12:18 PM
    Moderator