0

I have the databases A and B, A publish some tables to B, and the subscription to B is with error:

... has been marked as inactive and must be reinitiated... error 21704

There's very sensitive data on those databases, and I'm trying to figure out a way to prevent data loss, or at least minimize it.

Can I compare the databases with this error, or do I need to cancel the subscription first?

I've never done this before, and can't find any other solution than 'you're screwed, delete and do it again'.

How much may this affect the data? This error is going on for a couple weeks already.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Felipe Bandeira
  • 3
  • 4

1 Answers1

0

In case someone else have these doubts in the future, I'm posting my experience.

  • Yes, you can compare databases, and there's no need to cancel the subscription first.
  • The "You're screwed" part is not THAT screwed. Since DB A still holds the original info, B is lacking the changes. When you compare and synchronize them, you transfer the outdated info.
  • This error didn't cause any data trouble at all, the trouble can happen when you compare and update the DBs, because if you update A with B, and B is still outdated, you will probably lose data. Just be careful.

The comparison can be made on VisualStudio with SQL Server Data Tools, who automates the task, or manually through a simple SQL Query.

I made manually to learn and have a better understanding of the process. Here's the code I used:

    --RUN THIS QUERY FROM THE DESTINATION DB

    INSERT INTO TABLE_B 
    SELECT * FROM OPENQUERY([ORIGINAL-SERVER],'SELECT * FROM DB-A.DBO.TABLE_A') 
    WHERE FOREIGN_KEY NOT IN (SELECT FOREIGN_KEY FROM DB-B.DBO.TABLE_B)

Hope this can help someone in the future.

Felipe Bandeira
  • 3
  • 4