Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How does System table MSReplication_options created? Expand / Collapse
Author
Message
Posted Thursday, April 8, 2010 7:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 3,120, Visits: 792
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/
Post #899558
Posted Thursday, April 8, 2010 8:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 11, 2012 6:00 AM
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?
Post #899611
Posted Thursday, April 8, 2010 8:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 3,120, Visits: 792
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/
Post #899643
Posted Thursday, April 8, 2010 9:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 11, 2012 6:00 AM
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.
Post #899671
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse