How to manually synchronize replication subscriptions by using backup or restore

Chris Skorlinski
Microsoft SQL Server Escalation Services

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