SQL Server Performance

A newly restored db on an existing replication

Discussion in 'SQL Server 2005 Replication' started by WingSzeto, May 3, 2007.

  1. WingSzeto Member

    We are using SQL 2K5 with sp2. Say I have set up a transaction push replication on the publisher database (server A) to a subscriber database on a different server (server B). The distribution db is on the publisher server. The replication is working. Now I am ready to go live, can I just restore the most current db on top of the publishing database and then initialize the snapshot to replicate the new data to the subscriber database? Or do I need to redo all the publishing and subscription setup?

    wingman
  2. ndinakar Member

    to restore the db, you will have to stop the replication. which means you will have to set up the replication again. but no, you dont have to reinitialize the snapshot. just skip that part of the wizard and choose 'data already exists' option.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. MohammedU New Member

    If you restore the same the replicated database backup... your replication will work but when you restore with different database backup copy then it will not work...

    Best approach will be ...
    script your replication...
    Restore the database.
    Modify the scripts if the server name is different
    Run the scripts...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  4. WingSzeto Member

    What if the mentioned most current database backup copy which I will use to do the restore is two days newer than the existing publishing db? If I do what I suggest by choosing 'data already exist;, the subscriber database will not be in-sych with the restored database because the subscriber db is still two days old.





    quote:Originally posted by ndinakar

    to restore the db, you will have to stop the replication. which means you will have to set up the replication again. but no, you dont have to reinitialize the snapshot. just skip that part of the wizard and choose 'data already exists' option.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  5. satya Moderator

    If you have relevant referential intregrity then the duplicate data will be ignored or atleast they are alerted in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page