COPY SUBSCRIPTION ON SQL SERVER 2000 (STEP BY STEP

Due to the fact that “Copy Subscription Database” option doesn#%92t 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#%92s<br />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). <br /><br />Prerequisites:<br />- 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)<br />- the publication property: “Allow new subscription to be created by attaching a copy of a subscription database” (Subscription option tab) is checked<br />- the subscription that will serve as source is a pull subscription<br /><br />1. Stop MSSQLServer service on the source subscriber (using either EM or sp_detach_db);<br />2. copy .mdf file onto the new subscriber repository;<br />3. attach DB on the new subscriber (execute sp_attach_single_file @dbname=#%92your_db_name#%92, @physname=#%92mdf_filename#%92);<br /><br />Note: If you don#%92t 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. <br /><br />4. (optional) on the new subscriber:<br />use your_db_name<br />go<br />execute sp_changedbowner ‘sa#%92 <br />go<br />5. on the new subscriber:<br />use your_db_name<br />go<br />UPDATE MSreplication_subscriptions<br />SET agent_id = NULL, subid = NULL, distribution_agent = NULL, time = GETDATE()<br />Go<br />UPDATE MSsubscription_agents<br />SET attach_state=1<br />Go<br />Execute sp_addpullsubscription_agent<br />@publisher = ‘your_publisher_name#%92,<br />@publisher_db = ‘publication_db_name#%92,<br />@publication = ‘publication_name#%92,<br />@distributor = ‘distributor_server_name#%92,<br />@distribution_db = ‘distribution_db_name_on_the_distributor#%92,<br />@frequency_type = 2, /*2=onDemand; 64=continuous*/<br />@reserved = ‘no_change_to_properties#%92<br /><br />@alt_snapshot_folder = ‘your snapshot folder if different from default#%92<br /><br /><br />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.<br /><br />6. on the publisher in the publication DB add the new subscription:<br /><br />execute sp_addsubscription <br />@publication = ‘publication_name#%92,<br />@subscriber = ‘new_subscriber_name#%92,<br />@destination_db = ‘new_subscriber_db_name#%92,<br />@sync_type = ‘none#%92, <br />@update_mode = ‘read_only#%92,<br />@subscription_type = ‘pull#%92<br /><br />7. restart the distribution agent for the new subscription<br /><br />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:<br />- after step 3:<br />use new_subscription_db<br />go<br />CREATE TABLE dbo.MSreplication_restore_stage (stage_id int)<br />GO<br />INSERT INTO dbo.MSreplication_restore_stage (stage_id) values (2)<br />GO<br />- step 5 must be replaced by:<br />execute sp_MSrestore_sub_merge <br />- after step 6:<br />DROP TABLE dbo.MSreplication_restore_stage<br />GO<br /><br />------<br /><br />Well, thats it, hope it helps ![<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />