How to manually synchronize replication subscriptions by using backup or restore (320499)

SUMMARY

This article describes how to manually synchronize replication push subscriptions by using backup and restore.

Sometimes, you cannot fully synchronize replication subscriptions by using the default method because of the following potential reasons:

1. You have large tables that you have to transfer to the subscriber.
2. Network bandwidth can only handle incremental changes; therefore, large BCPs may time out.
3. 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 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 successful.

back to the top

Transactional replication

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 network.

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:

1. If the published database is not running in Full Recovery mode, change it to Full Recovery mode.
2. Back up the published database.
3. 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:

1. 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.
2. 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.
3. Create a new subscription to your publication. Select not to deliver the data and schema.
4. 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 database [and the transaction log backup] to the subscriber.)
5. 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.
6. 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.
7. 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)
   1. 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 subscribing database.
   2. 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.
   3. 
      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.
   4. 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 database.
8. 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: back to the top

Merge replication

Note No-synch subscriptions are not supported for merge pull subscriptions.

When you use backup or restore to set up a subscription to a merge publication with the no-sync option, follow these steps:

1. 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.
2. Back up the published database, and then restore it on the subscriber.
3. Create a new subscription, and then select No, the Subscriber already has the schema and data.
4. 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 subscriber:
   • 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 the subscriber.
   • 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.

back to the top