COPY SUBSCRIPTION ON SQL SERVER 2000 (STEP BY STEP PROCEDURE)

Due to the fact that “Copy Subscription Database” option doesn’t work for a DB greater than 2GB (sp_copysubscription and sp_attachsubscription call xp_makecab and xp_unpackcab that cannot manage files greater than 2GB), I put in place a step-by-step procedure to this without using system SP’s
I spent some time figuring out how to do it, and I thought that could be helpful for you guys (this part is not documented in BOL).

Prerequisites:
- the publication is set to accept the new subscription (at the publisher-replication-Configuring Publishing, Subscribers and Distribution- “Subscribers” tab , the new subscriber is checked)
- the publication property: “Allow new subscription to be created by attaching a copy of a subscription database” (Subscription option tab) is checked
- the subscription that will serve as source is a pull subscription

1. Stop MSSQLServer service on the source subscriber (using either EM or sp_detach_db);
2. copy .mdf file onto the new subscriber repository;
3. attach DB on the new subscriber (execute sp_attach_single_file @dbname=’your_db_name’, @physname=’mdf_filename’);

Note: If you don’t want to stop the MSSQL service on the source server, you can achieve the same goal by making a backup of the source DB, copying and restoring the backup on the new subscriber; but this would take time considering that the DB could be very large.

4. (optional) on the new subscriber:
use your_db_name
go
execute sp_changedbowner ‘sa’
go
5. on the new subscriber:
use your_db_name
go
UPDATE MSreplication_subscriptions
SET agent_id = NULL, subid = NULL, distribution_agent = NULL, time = GETDATE()
Go
UPDATE MSsubscription_agents
SET attach_state=1
Go
Execute sp_addpullsubscription_agent
@publisher = ‘your_publisher_name’,
@publisher_db = ‘publication_db_name’,
@publication = ‘publication_name’,
@distributor = ‘distributor_server_name’,
@distribution_db = ‘distribution_db_name_on_the_distributor’,
@frequency_type = 2, /*2=onDemand; 64=continuous*/
@reserved = ‘no_change_to_properties’

@alt_snapshot_folder = ‘your snapshot folder if different from default’


Note: at that point the subscription agent is added and started, but the reported run status is failed because the subscription is not added yet. A message like “Publication has expired and does not exist” might also be displayed.

6. on the publisher in the publication DB add the new subscription:

execute sp_addsubscription
@publication = ‘publication_name’,
@subscriber = ‘new_subscriber_name’,
@destination_db = ‘new_subscriber_db_name’,
@sync_type = ‘none’,
@update_mode = ‘read_only’,
@subscription_type = ‘pull’

7. restart the distribution agent for the new subscription

Note: the step-by-step procedure is tested for a transactional replication. If you want to implement it for a merge replication additional steps are required:
- after step 3:
use new_subscription_db
go
CREATE TABLE dbo.MSreplication_restore_stage (stage_id int)
GO
INSERT INTO dbo.MSreplication_restore_stage (stage_id) values (2)
GO
- step 5 must be replaced by:
execute sp_MSrestore_sub_merge
- after step 6:
DROP TABLE dbo.MSreplication_restore_stage
GO

------

Well, thats it, hope it helps !

Steve