November 24, 2010 at 7:51 am
I am in need of reinitializing the transactional replication of a database after various upgrade scripts are ran against it. I need a new snapshot created and the whole thing to start without my intervention.
I though that sp_reinitsubscription would be the way to go but I am having issues making it work. I have never had to do the replication this way so it is a new prospect. Let me give you some info. I scripted out the publication and subscription to make it easier.
I am grateful for any insight I can get.
exec sp_addpublication @publication = N'MCSTST7', @description = N'Transactional publication of database ''MCSTST7'' from Publisher ''ODDBMST05\ODDBMST05''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
exec sp_addsubscription @publication = N'MCSTST7', @subscriber = N'ODDBMSREPT02\ODDBMSREPT02', @destination_db = N'RPTMCSTST7', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
November 24, 2010 at 9:32 am
Remember the replication is already running. I use the sp in the following manner:
EXEC sp_reinitsubscription
@publication = N'MCSTST7',
@subscriber = N'ODDBMSREPT02\ODDBMSREPT02', --$(SubServer),
@destination_db = N'RPTMCSTST7',
@invalidate_snapshot = 1;
I get the error:
Msg 14013, Level 16, State 1, Procedure sp_MSrepl_reinitsubscription, Line 65
This database is not enabled for publication.
?????
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
December 1, 2010 at 6:50 am
try this:
EXEC sp_replicationdboption @dbname = N'MCSTST7', @optname = N'publish', @value = N'True'
If not, i would wipe the whole replication by right clicking the publication in SSMS and deleting it. This will remove any attached subscriptions cleanly.
Use your script to recreate and reinitialize.
December 1, 2010 at 12:04 pm
I have already tried the delete and recreations. This did not work. :: sigh ::
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
December 1, 2010 at 12:35 pm
David
The following is what i do usually. Using the "Allow Initialization from Backup" Option. The following methodology is useful if the database size is big. The usual snapshot regenerates copy of the tables and instead you can copy the entire database across and use the image to 'initialize' the subscription.
STEP I **
AT THE PUBLISHER
Using GUI create a publication and right click to see its properties. Under “Subscription Options” set ‘Allow initialization from backup files’ to TRUE. [Clicking OK might take upto 20-30 minutes to return control depending on how many (hundreds) of objects are being published 🙂 . Once the control is returned , double verification can be done by looking at the Properties for Subscription Options.]
STEP II **
AT THE SUBSCRIBER
Take a full backup of the Publishing database and restore it at the Subscriber. You can use the ‘Overwrite’ option while restoring if required; But there is no need to check the WITH KEEP_REPLICATION checkbox while restoring. Choose the Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored (RESTORE WITH RECOVERY option).
--AT THE PUBLISHER
USE my_publishing_db
GO
exec sp_addsubscription
@publication = N'my_publication'
,@subscriber = N'my_subscriber_server'
,@destination_db = N'my_subscribing_db'
,@sync_type = N'initialize with backup'
,@subscription_type = N'pull'
,@update_mode = N'read only'
,@backupdevicetype = 'Disk'
,@backupdevicename = 'C:\DBA\my_publisher_full.bak' <== Point to your backup file directory on the Publisher
GO
--AT THE SUBSCRIBER
USE my_subscribing_db
GO
exec sp_addpullsubscription
@publisher = N'my_publishing_server',
@publication = N'my_publication',
@publisher_db = N'my_publishing_db',
@independent_agent = N'True',
@subscription_type = N'pull',
@description = N'',
@update_mode = N'read only',
@immediate_sync = 1
GO
USE my_subscribing_db
go
exec sp_addpullsubscription_agent
@publisher = N'my_publishing_server',
@publisher_db = N'my_publishing_db',
@publication = N'my_publication',
@distributor = N'my_distributor_server',
@distributor_security_mode = 1,
@distributor_login = N'',
@distributor_password = N'distributor_password', <==optional if applicable
@enabled_for_syncmgr = N'False',
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20101108, <== put today's date
@active_end_date = 99991231,
@alt_snapshot_folder = N'',
@working_directory = N'',
@use_ftp = N'False',
@job_login = null,
@job_password = null,
@publication_type = 0
GO
AT THE PUBLISHER:
Right click on the publication and check "View snapshot agent status".
If it says "The agent has never been run" start it. Once the Start time is displayed click on the close button.
When you right click on the Publication again and check “View snapshot agent status”.
It might say “[0%] A snapshot was not generated because no subscription needed initialization.”.
This is because we are using a backup for initialization and no need to generate any snapshot. It is safe to ignore this message.
When you right click on the Publication again and check “View Log Reader agent status”.
The log reader agent will say "No replicated transactions are available". Click on the Close button to exit.
Check the replication monitor. It will say no replicated transactions are available.
Carry out few updates on the publishing database and you will see them replicated to the subscribing database. If bulk update then obviously the replication monitor will show the pending rows to be applied at the subscriber.
Hope This Helps
GK
July 11, 2016 at 4:20 am
That sp MUST be run against the publisher database
use PublisherDBName;
GO
EXEC EXEC sp_reinitsubscription
@publication = N'MCSTST7',
@subscriber = N'ODDBMSREPT02\ODDBMSREPT02',
@destination_db = N'RPTMCSTST7',
@invalidate_snapshot = 1;
or
EXEC PublisherDBName.dbo.EXEC sp_reinitsubscription
@publication = N'MCSTST7',
@subscriber = N'ODDBMSREPT02\ODDBMSREPT02',
@destination_db = N'RPTMCSTST7',
@invalidate_snapshot = 1;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply