sp_copysubscription
Copies a subscription database that has pull subscriptions, but no push subscriptions. Only single file databases can be copied. This stored procedure is executed at the Subscriber on the subscription database.
Syntax
sp_copysubscription [ @filename = ] 'file name'
[ , [ @temp_dir = ] 'temp_dir' ]
[ , [ @overwrite_existing_file = ] overwrite_existing_file]
Arguments
[@filename = ] 'file name'
Is the string that specifies the complete path, including file name, to which a copy of the data file (.mdf) is saved. file name is nvarchar(260), with no default.
[@temp_dir = ] 'temp_dir'
Is the name of the directory that contains the temp files. temp_dir is nvarchar(260), with a default of NULL. If NULL, the SQL Server default data directory will be used. The directory should have enough space to hold a file the size of all the subscriber database files combined.
[@overwrite_existing_file = ] 'overwrite_existing_file'
Is an optional Boolean flag that specifies whether or not to overwrite an existing file of the same name specified in @filename. overwrite_existing_file is bit, with a default of 0. If 1, it overwrites the file specified by @filename, if it exists. If 0,the stored procedure fails if the file exists, and the file is not overwritten.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_copysubscription is used in all types of replication to copy a subscription database to a file as an alternative to applying a snapshot at the Subscriber. The database must be configured to support only pull subscriptions. Users having appropriate permissions can make copies of the subscription database and then e-mail, copy, or transport the subscription file (.msf) to another Subscriber, where it can then be attached as a subscription.
This technique is useful for copying highly customized databases that contain user-defined objects, such as triggers, stored procedures, views, UDFs, and objects such as defaults and rules, which are not otherwise delivered through replication.
Permissions
Members of the public role can execute sp_copysubscription.