How to manually synchronize replication subscriptions by using backup or restore

Share this Post

The Microsoft Knowledge Base contains the following article for setting up a SQL 2000 Subscriber using Backup/Restore.  Once the subscriber is restored sp_scriptpublicationcustomprocs is executed to create the subscriber Replication SPs.

320499 How to manually synchronize replication subscriptions by using backup or restore
http://support.microsoft.com/default.aspx?scid=kb;EN-US;320499

These steps should be modified for SQL 2005/2008 which automatically creates the required Replication Metadata tables and stored procedures.  The replication stored procedures sp_scriptpublicationcustomprocs does not need to be executed.  In addition, as long as no data is being updated on the Publisher it is not necessary to place the database in Single User mode.  Steps 2 and 3 can be occurring simultaneously.

  1. Backup “to be published” database
  2. Restore to Subscriber.  Published DB can continue to be queried for RO transactions.
  3. Create Publication
  4. Create Push/Pull Subscription but remember to CLEAR the INITIALIZE checkbox.
  5. MSrepl_Subscription table and restored SPs are created on the subscriber.

See also: SQL 2005/2008 Books Online top: How to: Initialize a Transactional Subscriber from a Backup

See also: Deep Dive on Initialize from Backup for Transactional Replication


Share this Post

One thought on “How to manually synchronize replication subscriptions by using backup or restore”

  1. Thank you Chris, Is there any way to synchronize only one table or one article from all of the published articles ?

Leave a Reply to patil Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.