How to manually synchronize replication subscriptions by using backup or restore
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
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.
- Backup “to be published” database
- Restore to Subscriber. Published DB can continue to be queried for RO transactions.
- Create Publication
- Create Push/Pull Subscription but remember to CLEAR the INITIALIZE checkbox.
- 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