SQL Server Performance

COPY SUBSCRIPTION ON SQL SERVER 2000 (STEP BY STEP

Discussion in 'General DBA Questions' started by dbadelphes, Nov 8, 2006.

  1. dbadelphes New Member

    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 />

Share This Page