Executing Scripts Before and After the Snapshot Is Applied
You can specify scripts to execute at the Subscriber before or after the snapshot is applied. Scripts can be used for a variety of reasons, such as creating logins and schemas (object owners) at each Subscriber.
You specify a file location for each script, and the Snapshot Agent copies the script files to the current snapshot folder each time snapshot processing occurs. The Distribution Agent or the Merge Agent runs the pre-snapshot script before any of the replicated object scripts when applying a snapshot. The Distribution Agent or the Merge Agent runs the post-snapshot script after all the other replicated object scripts and data have been applied. After the snapshot application is complete and script files run successfully, the script files are removed from the working directory on the Subscriber.
The script is run by launching the sqlcmd utility. Before deploying a script, run it with sqlcmd to ensure it executes as expected. The contents of scripts that are executed before and after the snapshot is applied must be repeatable. For example, if you create a table in the script, you should first check for its existence and take appropriate action if it exists. The script must be repeatable because 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 Microsoft 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 script indicated as a pre-snapshot script is executed. Likewise, any post-snapshot script is decompressed and executed at the Subscriber as the last step in applying the snapshot.
You can execute scripts when applying the snapshot to Subscribers running Microsoft SQL Server 7.0 if you use push subscriptions and the Distributor is running SQL Server 2000 or later. You cannot execute scripts when applying the snapshot to Subscribers running SQL Server 7.0 if you use pull subscriptions.
To execute scripts before and after the snapshot is applied
- SQL Server Management Studio: How to: Execute Scripts Before and After a Snapshot is Applied (SQL Server Management Studio)
- Replication Transact-SQL programming: How to: Configure Snapshot Properties (Replication Transact-SQL Programming)