SQL Server Replication - lost publication. Can data be re-synced?

Robin 1 Reputation point
2022-02-04T03:07:48.55+00:00

Hi guys
We have recently had a serious server SQL Server failure and I am attempting to rebuild.

This server was a publisher for a single target subscription database.

The subscription and the database still reside on the target server.

The target database has all the data - up until the the time publisher SQL Server instance died.

My question is can I create another publication that either:
a) checks the subscription that still resides on target server and carry on from where it left off?
or b) have the publication only sync data to where the previous instance died?

The target DB is purely from the publication - so has no custom or orphaned data in it.

My point to the question is that I'm hoping I avoid having to either drop/recreate objects on the target or truncate the data.
If I use the "Leave object unchanged" option, this will attempt to re-insert all the data from the snapshot - which will give primary key violations, since the some of the data still resides there.

Thanks in advance :-)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,911 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. YufeiShao-msft 7,061 Reputation points
    2022-02-07T06:51:13.653+00:00

    Hi @Robin

    Yes,you can. If you have the backup of The publication database at the Publisher, this is recommended for regular backup, if you have, some things will be much easier
    Moving publisher/distributor to new server/ SQL instance

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments