How does System table MSReplication_options created?

  • When we restore a database from production to a development environment, we get some erros. Running sp_removedbreplicaiton on the database fixes the error, but it will happen again.

    A DBA here create the table from production and populated it, then ran sp_ms_marksystemobject to make the table a system table in master.

    Still would like to know why the development server does not have this system table in master to begin with.

    God Bless,

    ThomasLL

    Thomas LeBlanc, MVP Data Platform Consultant

  • "When we restore a database from production to a development environment, we get some erros."

    >> what type of errors? does the database has replication turned on in production when you take backup? if yes, do you restore with "keep replication" option while restoring in dev?

    "Still would like to know why the development server does not have this system table in master to begin with."

    >> this table should be there in every master database irrespective of whether you have replication turned on or not.

    I am not sure why the table is not there on the server unless there are any service pack/hotfix installations which did not complete cleanly.

    Is your issue solved now or you still need active help?

  • My main object of this thread is to find out where\shen the system table is created. It is no longer a problem on this Dev mahcine because the DBA created the table from produciton and flagged it a system table in development.

    To answer your questions:

    what type of errors?

    Database 'DB' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online

    does the database has replication turned on in production when you take backup?

    Yes

    if yes, do you restore with "keep replication" option while restoring in dev?

    We use LiteSpeed, not sure how to do this

    Is your issue solved now or you still need active help?

    -- still would like to know where/when the system table is created.

    Thomas LeBlanc, MVP Data Platform Consultant

  • AFAIK, this table is shipped with the product and subsequently updated by service packs (if required). That's the reason why I mentioned about checking if any service packs/hotfixes might have encountered failures while running upgrade scripts.

    Can you check if LiteSpeed has by default "keep replication" set to on. If yes, turn it off then attempt restore again to check if it gets the errors.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply