Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How does System table MSReplication_options created?


How does System table MSReplication_options created?

Author
Message
Thomas LeBlanc
Thomas LeBlanc
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3790 Visits: 900
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, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
mangeshd
mangeshd
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 556
"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?
Thomas LeBlanc
Thomas LeBlanc
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3790 Visits: 900
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, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
mangeshd
mangeshd
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 556
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search