I am restoring databases to each side of my transactional and merge replications. Basically the publication and the subscriber are the same database initially.
I still need to run the snapshot agent to get things started.
How can I run the snapshot without runnin the bcp commands, I mean, without copying the data accross?
I am ok with the data structure to be copied over though.
In the job activity monitor check those who are the snapshot jobs:
double click the job and go to the second step - Run Agent
add /NoBCPData at the end:
-Publisher [myserver] -PublisherDB [PP_Master] -Distributor [myserver] -Publication [PP_Master] -ReplicationType 2 -DistributorSecurityMode 1 /NoBCPData It just happened I had an example of that today. However, the database was also involved in an availability group.
So first I had to restore a new backup of this database to both this UAT server and the DR server. restore to add to availability group generally is one full, one diff and one log, remember to stop all log and diff backups while you do it, so that the databases are in sync.
then create the merge publication and the merge subscription:
-- Adding the merge subscriptions use [PP_Milano_Italia] exec sp_addmergesubscription @publication = N'PP_Milano_Italia', @subscriber = N'My_Server_in_Italy', @subscriber_db = N'PP_Milano', @subscription_type = N'Push', @sync_type = N'Automatic', @subscriber_type = N'Global', @subscription_priority = 75, @description = N'', @use_interactive_resolver = N'False' exec sp_addmergepushsubscription_agent @publication = N'PP_Milano_Italia', @subscriber = N'My_Server_in_Italy', @subscriber_db = N'PP_Milano', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'Replication_SQL_Account', @subscriber_password = N'MY_p4$$w0rd', @publisher_security_mode = 1, @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0 GO then change the snapshot job:
USE [msdb] GO EXEC msdb.dbo.sp_update_jobstep @job_id=N'254a4463-8df7-4d55-bb56-b4e8fd5006cf', @step_id=2 , @command=N'-Publisher [MYSERVER] -PublisherDB [PP_MILANO_Master] -Distributor [MYSERVER] -Publication [PP_MILANO_Master] -ReplicationType 2 -DistributorSecurityMode 1 /NoBCPData' GO Then start the snapshot.
And after the snapshot is applied:
all is good.
