locked
Transactional replication from backup RRS feed

  • Question

  • Hy everybody. I Would like to ask about the transactional replication om MS SQL 2005 SP3 for full backup.

    Does backup restore once and after that it  uses transactions by the distributor and logreader agents or it can be restored recurring?

    Friday, July 23, 2010 9:48 AM

Answers

  • No, if you restore the backup into a publication database from a previous version of that publication database and use the keep replication switch, the log reader will fail. The distribution agent isn't sensitive to publication database metadata it will work fine, just have nothing to replicate.

     

    You will need to use the sp_replrestart proc, and then use the continue on data consistency error profile to get replication working again.

     

    Then run a validation to determine how out of sync you are.


    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
    • Proposed as answer by Tom Li - MSFT Tuesday, July 27, 2010 8:32 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 9:14 AM
    Friday, July 23, 2010 2:26 PM
    Answerer

All replies

  • Can you please be more clear? I think all you are saying is: If a restore happens, then, can I restore the replication?

    Friday, July 23, 2010 2:03 PM
  • No, if you restore the backup into a publication database from a previous version of that publication database and use the keep replication switch, the log reader will fail. The distribution agent isn't sensitive to publication database metadata it will work fine, just have nothing to replicate.

     

    You will need to use the sp_replrestart proc, and then use the continue on data consistency error profile to get replication working again.

     

    Then run a validation to determine how out of sync you are.


    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
    • Proposed as answer by Tom Li - MSFT Tuesday, July 27, 2010 8:32 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 9:14 AM
    Friday, July 23, 2010 2:26 PM
    Answerer