locked
Restoring replicated database RRS feed

  • Question

  • Hello all,

    I backed up a database (SQL 2005) that was being replicated and resotored the backup copy in SQL 2008 R2 enterprise in a new server. Now will there be any problem as the original database was being replicated?

    Thanks.

     

    Wednesday, September 21, 2011 6:12 PM

Answers

  • By default the replication settings should be removed after a restore.  If you want to be sure that the replication settings are removed you can execute sp_removedbreplication on the database after the restore.  This will guarantee all replication objects are removed from the database.

    Hope this helps.
    www.sqlrepl.com

    • Proposed as answer by Marco Carozzi Wednesday, September 21, 2011 9:14 PM
    • Marked as answer by FitzC Monday, September 26, 2011 4:14 PM
    Wednesday, September 21, 2011 8:30 PM
  • As Brandon points out all replication metadata will be removed from the published database's backup when you restore it - unless you restore the database using the keep replication switch.

    There should be no need to use sp_removedbreplication - but it will definitely remove any lingering replication metadata should it exist.

    There will be no problem with creating a new publication in this database however.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by FitzC Monday, September 26, 2011 4:14 PM
    Thursday, September 22, 2011 12:06 PM
    Answerer

All replies

  • By default the replication settings will be removed.  To preserve the replication settings specify the RESTORE argument KEEP_REPLICATION.

    Hope this helps.
    www.sqlrepl.com

    • Proposed as answer by Marco Carozzi Wednesday, September 21, 2011 9:14 PM
    Wednesday, September 21, 2011 6:23 PM
  • Thanks for your reply.

    Actually I do not want to keep previous replication settings. I restored the db in a new machine and I might start a new replication later and I hope that the restored db won't create any problem in this machine.

    Wednesday, September 21, 2011 6:30 PM
  • By default the replication settings should be removed after a restore.  If you want to be sure that the replication settings are removed you can execute sp_removedbreplication on the database after the restore.  This will guarantee all replication objects are removed from the database.

    Hope this helps.
    www.sqlrepl.com

    • Proposed as answer by Marco Carozzi Wednesday, September 21, 2011 9:14 PM
    • Marked as answer by FitzC Monday, September 26, 2011 4:14 PM
    Wednesday, September 21, 2011 8:30 PM
  • As Brandon points out all replication metadata will be removed from the published database's backup when you restore it - unless you restore the database using the keep replication switch.

    There should be no need to use sp_removedbreplication - but it will definitely remove any lingering replication metadata should it exist.

    There will be no problem with creating a new publication in this database however.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by FitzC Monday, September 26, 2011 4:14 PM
    Thursday, September 22, 2011 12:06 PM
    Answerer