Applies To: SQL Server
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.
sp_copysubscription [ @filename = ] 'file_name' [ , [ @temp_dir = ] 'temp_dir' ] [ , [ @overwrite_existing_file = ] overwrite_existing_file]
[ @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 Microsoft 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_fileis 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.
0 (success) or 1 (failure)
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.
The size of the subscription database being copied must be less than 2 gigabytes (GB).
sp_copysubscription is only supported for databases with client subscriptions and cannot be executed when the database has server subscriptions.
Only members of the sysadmin fixed server role can execute sp_copysubscription.