If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Restoring a db that was involved in replication

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Location: Newcastle, Australia
Posts: 51
Restoring a db that was involved in replication

Hi all

I have restored a db from a live environment to my dev envorinment. This db on live is a replication publisher.

My dev environment has no replication, but when I try to modify the schema in the db I restored I get the following error

"Server: Msg 4932, Level 16, State 1, Line 441
ALTER TABLE DROP COLUMN failed because 'url_web1' is currently replicated."

Does anyone know what I have to do to restore the database on my de box so it 'forgets' its live replication settings?
On my dev server I have no items in the replication folder in Enterprise Manager but it seems replication details are stored somewhere, perhaps in the backup itself?

Matt
Reply With Quote
  #2 (permalink)  
Old
Grand Poobah
 
Join Date: Aug 2002
Location: Scotland
Posts: 1,578
Still you require to use sp_removedbreplication which removes the replication settings on this database.

There is another way to ignore the setting but I prefer you to use system supplied SPs, if this ain't working then take back route.
__________________
--Satya SKJ
Microsoft SQL Server MVP
[IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Location: Newcastle, Australia
Posts: 51
That didnt seem to do anything. Bummer.

Anything else I should try?
Reply With Quote
  #4 (permalink)  
Old
Grand Poobah
 
Join Date: Aug 2002
Location: Scotland
Posts: 1,578
Then adopt the following with prior testing:
use Userdatabase

updates sysobjects set replinfo = 0 where name ='table_name'

sp_configure 'allow updates', 1
go
reconfigure with overide
go

BOOM!!!!
__________________
--Satya SKJ
Microsoft SQL Server MVP
[IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Location: Newcastle, Australia
Posts: 51
I checked sysobjects (before running) and replinfo was already 0

I ran the whole script and still no change. This is one stubborn db.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Location: KY
Posts: 146
Hi

How about setting up repl for that Db (create Publication) and then disabling it again ?
Reply With Quote
  #7 (permalink)  
Old
Grand Poobah
 
Join Date: Aug 2002
Location: Scotland
Posts: 1,578
What kind of replication was set on production database?
__________________
--Satya SKJ
Microsoft SQL Server MVP
[IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jun 2003
Posts: 31
Even if the GUI tool runs perfectly to remove the Replication components, it will still leave elements behind.
The most common items that are left behind are:

1) database options
2) replication objects
3) sysobject settings
4) rowguid column
5) conflict tables
6) distribution database

To remove the database options, you simply execute sp_dboption for published, merge publish, or subscribed, and set it to false.

exec sp_dboption 'pubs','published', FALSE


Removing the sysobject settings is more involved in that you have to turn on allow updates, update the replinfo column and set it to 0 where it is 128, and then turn off allow updates. At this point, you should have a fully functional database that allows you to alter/drop objects and drop databases. You could stop here without having any side effects. But, we might as well take everything out. You get rid of the rowguid column, if it exists, by altering the table and dropping the column. Conflict tables are simply user tables and can be removed with a simple drop command. The same goes for any triggers, views, or procedures that replication created.


Good Luck.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Location: Newcastle, Australia
Posts: 51
Thanks for the response, I learnt some more about replication

I still am unable to drop any columns on my tables though. Its worth noting that the replinfo column value is 0 for all rows in the sysobject database before I run any "remove replication" scripts.

There dont seem to be any rowguid columns or conflict objects in the db.

I've passed all these details onto our DBA in case it helps resolve the problem

Thanks
Matt
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Location: Newcastle, Australia
Posts: 51
Just in case someone is vaguely interested, our DBA has produced a script that seems to have nicely nuked all replication info

sp_removedbreplication 'mydb'
go

exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
go

update sysobjects set replinfo = 0 where replinfo > 0
UPDATE syscolumns SET colstat = colstat & ~4096 WHERE colstat & 4096 <>0
go

exec sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
Reply With Quote
  #11 (permalink)  
Old
Grand Poobah
 
Join Date: Aug 2002
Location: Scotland
Posts: 1,578
Thanks for the enhancement to replace the issue, it helps.
__________________
--Satya SKJ
Microsoft SQL Server MVP
[IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On