1

I implemented Transaction Replication (pull subscription with distributor at subscriber) with initialize from backup. It was working good and today when i checked replication monitor,it shows error on subscription.I can see error message "Invalid object name 'demoview1" in Distributor to Subscriber history.

So i checked the Subscriber database and it don;t have that view and even published database don't have such a view.I checked the published article list and there is no such view. I remember i renamed demoview(published article) to demoview1 as part of testing in publisher.

  1. How can i get rid of this issue.I ran all agents again,but no help?
  2. My understanding is ,since i initialized subscriber with backup, i don't have any snapshot in the snapshot folder.So if it was normal replication (with initialize) ,a new snapshot would have get rid of this issue?Please correct me if i am wrong.

3.Do i have to take a backup of publication and restore it at subscription to initialize the subscriber again?

  • How big is the publication database ? – Kin Shah Jun 29 '18 at 13:57
  • It is around 1 TB. – user9516827 Jun 29 '18 at 14:05
0

I don't think you need to initialize from backup. Try to recreate the view from publisher db and make sure to add it on the articles properties.

It is very important to change below config so it will not recreate the entire snapshot:

EXEC sp_changepublication
@publication = 'pubtest1',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'pubtest1',
@property = N'immediate_sync',
@value = 'false'
GO

After you've added the article, run your snapshot agent. This should fix the error.

You should see your snapshot agent with a message of [100%] A snapshot of 1 article(s) was generated. And on your distribution agent a message of A DDL change has been replicated.

You must test this first before applying in PROD. I've tested this on my machine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.