Scenario

You have a transactional replication configured in your production environment with multiple subscribers.  The business team has requested that one of the subscriptions be reinitialized, because they think there is some missing data. In this tip we look at the different options that you can use to reinitialize a subscription for transactional replication.

Solution

If we come across this requirement there are different ways by which we could achieve this. In this tip, we will accomplish this by using SQL Server Management Studio (SSMS) and Replication Monitor. Note, both options assume transactional replication is already configured in your server.

Option 1 : Using SQL Server Management Studio (SSMS).

In SSMS go to Replication -> Local Publications -> Locate your publication and expand it.

Suppose, you wish to reinitialize only the subscription [SUBSCRIP1].[REP_S1].  To do this, right click that subscription and select the ‘reinitialize’ option and you will get a dialog window.

Here, we have the option to select either ‘Use the current snapshot’ or ‘Use a new snapshot’. The use current snapshot will use the existing snapshot and the use a new snapshot will use a new current snapshot.

Based on your requirement, select the desired option and then click on ‘Mark for Reinitialization’ which will enable you to reinitialize the subscription.

If you wish to reinitialize all subscriptions, you would need to right click on the publication and select ‘Reinitialize All Subscriptions’, which would enable you to reinitialize all your subscriptions.

Option 2: Using Replication Monitor

In SSMS go to Replication -> right click on replication and select ‘Launch Replication Monitor’.

The ‘replication monitor’ screen should open. On the left pane, under ‘My publishers’, click on the publisher node and expand to get a list of the subscriptions.

In the ‘All Subscriptions’ tab, you need to select the appropriate subscription and click on ‘Reinitialize Subscription’ which would enable you to reinitialize only that subscription in the list.

Once done, you would encounter the same window as shown in option 1, when you reinitialize a subscription and you would need to either select the existing snapshot or opt for a new one.

If you wish to reinitialize all your subscriptions using replication monitor, you could just right click on the publication node and select ‘Reinitialize All Subscriptions’.

 

Both options could be tested easily by configuring a simple replication setup and performing the sequence of steps as detailed above.

Thanks for reading this article,

Next steps :

1. Share this with your colleagues because Sharingis Learning
2. Comment below if you need any assistance

Powered by CodeReview – Let’s make it Better!