Executing Scripts Before and After the Snapshot is Applied

SQL Replication

Replication

Executing Scripts Before and After the Snapshot is Applied

You can specify scripts to execute necessary procedures at the Subscriber before or after snapshot synchronization. Possible uses of executing scripts before or after synchronization could be to create logins at each Subscriber, to create user-defined data types at the Subscriber so that data with those data types can be replicated, or to update statistics after snapshot synchronization.

When a file location and script name entry is specified, the Snapshot Agent copies the script files to the current snapshot folder each time snapshot processing occurs. The Distribution Agent or Merge Agent will run the pre-snapshot script before any of the replicated object scripts when applying an initial synchronization. The Distribution Agent or Merge Agent will run the post-snapshot script after all the other replicated object scripts and data have been applied during an initial synchronization. The script is run by launching the osql utility. Test your script by running it with osql to be sure it executes as expected. It is recommended that you make sure that the contents of scripts that are executed before and after the snapshot is applied are repeatable and can be executed more than once. If you need to reinitialize a subscription for which the script has already been applied, the script will be applied again when the new snapshot is applied during reinitialization.

If you are compressing the snapshot file (by putting it in CAB file format), the scripts are also compressed and placed in the CAB file. After the compressed snapshot file is transferred to the Subscriber and decompressed to a working directory on the Subscriber, any scripts indicated as a pre-snapshot script will be executed. Likewise, any post-snapshot script will be decompressed and executed at the Subscriber as the last step in applying the snapshot. After initial synchronization is complete and script files run successfully, the script files are removed from the working directory on the Subscriber.

Important  You can execute scripts when applying the snapshot to Subscribers running SQL Server 7.0 if you use push subscriptions and the Distributor is running SQL Server 2000. You cannot execute scripts when applying the snapshot to Subscribers running SQL Server 7.0 if you use pull subscriptions or anonymous subscriptions. With pull subscriptions, the agent is created and run on the Subscriber. Agents in SQL Server 7.0 do not have the capability of running scripts while applying the snapshot. However, if you use push subscriptions, the agent is run at the Distributor by default. If the Distributor is running SQL Server 2000, the agent running there will be able to execute the scripts before and after applying the snapshot.

To execute scripts before and after the snapshot is applied