This article describes how to manually synchronize
replication push subscriptions by using backup and
Sometimes, you cannot fully synchronize replication subscriptions by using the
default method because of the following potential reasons:
- You have large tables that you have to transfer to the
- Network bandwidth can only handle incremental changes;
therefore, large BCPs may time out.
- The publisher is a production server; therefore, business
needs require the down time to be minimized.
In these cases, you can use SQL Server backups to create copies
of the published database, and then you can restore the data on the subscriber;
by doing so, you can set up replication, and test the use of the replication, without delivering the schema or user
data over the network. The following sections list the steps and considerations
that you have to use to make sure that manual synchronization is
Transactional replication stores and forwards serial transactions
to the subscriber. It is crucial that changes to the published tables are
delivered to the subscriber in the order in which they were submitted.
With a new subscription, transactional replication marks each change
to the published table (or tables) in the transactional log. The default
subscription delivery method locks the tables, exports the data by using the bcp
utility, unlocks the published tables, and then begins tracking
changes to the published database. In SQL Server 2000, the Concurrent Snapshot
feature improves Snapshot locking overhead. SQL Server 2000 and
SQL Server 7.0 can transfer the snapshot by using File Transfer Protocol
(FTP). However, you can use the backup method for situations where these
options are not acceptable.
By backing up the published database and
restoring it to the subscriber, you can reduce the snapshot creation process
time to the time that it takes to backup the published database. The database
backup includes all the objects that are not transferred to the subscriber by
replication; you do not have to perform a bcp transfer of the tables over the
There are two methods to back up the published database. The first method uses a full backup of the published database. The
full backup method works best if the database is small or if the database
is not configured for Full Recovery mode. The second method uses a transaction log backup and assumes that you have already captured a full backup of the database. The transaction log backup method decreases the time that the database must be in single-user mode. Transaction log backups take less time than full backups. If
you plan to use the transaction log backup method, follow these steps:
- If the published database is not running in Full Recovery
mode, change it to Full Recovery mode.
- Back up the published database.
- Back up the log file to minimize
the time that it takes to walk through the subscription steps, and then follow the steps in the next procedure.
To set up the subscription, follow these steps:
- Place the published database in Single User mode to prevent
changes from being made in the database by running the following stored
procedure: sp_dboption 'DBNAME', 'single user', 'true'. This prevents changes from being made in the database. This is a
critical step; you are making sure that the publisher stays synchronized with
the subscriber. You must stop all replication agents that are connected to the
database before you run the sp_dboption stored procedure.
- If you are using the full backup method, back up the published database. If you are using the transaction log method, back up the transaction log for the published database.
- Create a new subscription to your publication. Select not to deliver the data and schema.
- While you are setting up the subscription, look for the
Distribution Agent Schedule screen. Change the task to run only once. (This
prevents the Distribution Agent from running until after you restore the
the transaction log backup] to the subscriber.)
- Remove the database from single-user mode by using the
following stored procedure call: sp_dboption 'DBNAME', 'single user', 'false'. Because the subscription is set up, all changes are forwarded to
the distribution database.
- Restore the database at the subscriber. If you are using the transaction log method, restore the full backup and the transaction log
backup. The Distribution Agent should not be running
at this point. If it is, it will prevent the database from being restored. The
Agent schedule was changed in Step 4.
- Generate the Insert, Update, and Delete procedures that are used during replication. You can generate the
CREATE PROCEDURE statements for these procedures by running one of the
following procedures: (The procedures vary depending on the type of replication
and the version of SQL Server)
- For SQL Server 2000: sp_scriptpublicationcustomprocs
Run sp_scriptpublicationcustomprocs on the publisher. This procedure generates text for the stored
procedures that are required at the subscriber. Run the generated script on the
- For immediate updating and queued subscribers: sp_script_synctran_commands
Note Immediate updating and queued subscribers are an exception to step 4. You must run the
Distribution Agent before you apply the output for
the sp_script_synctran_commands to the subscriber database because the
Distribution Agent generates a supporting table that is named MSsubscription_agents.
After you run the Distribution Agent, apply the script that is generated by
sp_script_synctran_commands to the subscriber database. You must also run the
sp_scriptpublicationcustomprocs stored procedure for immediate updating subscribers on the
publisher and the generated script on the subscribing database.
You must apply the output for sp_script_synctran_commands to the subscriber database; however, you must first run the
Distribution Agent to generate a supporting table named MSsubscription_agents, and then you can apply the generated output when you run sp_script_synctran_commands. You must also run sp_scriptpublicationcustomprocs for Immediate Updating subscribers on the publisher. Run the
generated script on the subscribing database.
- For SQL Server 7.0: sp_scriptinsproc, sp_scriptdelproc, sp_scriptupdproc, sp_scriptmappedupdproc
These procedures generate scripts for the procedures
that are required at the subscriber. Run these scripts against the subscribing
- Start the Distribution Agent. You may want to set up the
Distribution Agent to run continuously. To do so, add -Continuous to the Distribution Agent command line.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
sp_scriptpublicationcustomprocs generates replication stored procedures
No-synch subscriptions are not supported for merge pull subscriptions.
you use backup or restore to set up a subscription to a merge publication with
the no-sync option, follow these steps:
- Publish the database, and then run the Snapshot Agent. If
the database has been published, you only have to run the Snapshot Agent.
All changes that are made on the publisher are now recorded in the
merge replication system tables.
- Back up the published database, and then restore it on the
- Create a new subscription, and then select No, the Subscriber already has the schema and data.
- Run the Merge Agent.
When the Merge Agent runs,
it first uses the snapshot to create the merge replication tables. All the
changes that were made since the snapshot was generated are applied to the
- If you added any rows between step 1 and step 2 in this
procedure, you will see the new rows as updates on the subscriber. The rows
already exist because of the restore. Therefore, you will see the new rows on
- If you deleted any rows between step 1 and step 2 in
this procedure, the Merge Agent reports that no changes must be made because
the rows do not exist on the subscriber. The backup or restore was performed
after the rows were deleted on the publisher.
- If any rows were updated between step 1 and step 2 in
this procedure, you will see these as updates on the subscriber.
For more information about how to initialize a transactional subscription from a backup in SQL Server 2005, visit the following Microsoft Developer Network (MSDN) Web site:
For more information about how to initialize a merge subscription from a backup in SQL Server 2005, visit the following MSDN Web site:
Article ID: 320499 - Last Review: November 12, 2007 - Revision: 7.1
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2000 Developer Edition