Results 1 to 11 of 11
  1. #1
    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

  2. #2
    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]

  3. #3
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    That didnt seem to do anything. Bummer.

    Anything else I should try?

  4. #4
    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]

  5. #5
    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.

  6. #6
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Hi

    How about setting up repl for that Db (create Publication) and then disabling it again ?

  7. #7
    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]

  8. #8
    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.

  9. #9
    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

  10. #10
    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

  11. #11
    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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •