locked
Restoring Source Database on Destination RRS feed

  • Question

  • Guys, I have a question about replication.

    I have transactional replication going from Server A to Server B.  Let's say for example I take a backup copy of the source database of Server A, and restore it on Server B destination database; would it affect replication in a negative way after restoring the database that is being replicated?

    Wednesday, April 20, 2016 7:37 PM

Answers

  • Yes, there will be a lot of problems. You will need to recreate the subscription and then mark all identity columns, trigges, and constraints as not for replication.

    You will not know if you are in consistent or not. For example you create a backup a noon. The backup completes at 12:10.

    You restore on the publication databases on subscriber. There is a good chance that the replication command pooling on the publisher between 12:00 and 12:10 will not be distributed to the subscriber. There is also a chance that replicated commands between the time you create the subscription and do the backup will not make it to the subscriber.

    If you do use the initialize from a backup option, you will likely experience no data loss.


    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 Diango Friday, April 22, 2016 1:23 PM
    Wednesday, April 20, 2016 8:22 PM
    Answerer

All replies

  • Yes, there will be a lot of problems. You will need to recreate the subscription and then mark all identity columns, trigges, and constraints as not for replication.

    You will not know if you are in consistent or not. For example you create a backup a noon. The backup completes at 12:10.

    You restore on the publication databases on subscriber. There is a good chance that the replication command pooling on the publisher between 12:00 and 12:10 will not be distributed to the subscriber. There is also a chance that replicated commands between the time you create the subscription and do the backup will not make it to the subscriber.

    If you do use the initialize from a backup option, you will likely experience no data loss.


    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 Diango Friday, April 22, 2016 1:23 PM
    Wednesday, April 20, 2016 8:22 PM
    Answerer
  • Awesome, thanks for the info!
    Friday, April 22, 2016 1:23 PM