Option To Not Replicate Deletes – SQL Server Replication

Recently I was asked whether you could have transactional replication set up and replicate everything, except deletes.

I didn’t think that was possible out of the box, but when I did some research I found out that actually this is an option.

You can edit the properties of the article and make sure you don’t replicate deletes (under statement delivery).

The obvious things to watch out for here are

1./ If you have a valid delete statement that you do want deleted from the subscriber – its not going to happen.

2./ When you reinitialize the replication and apply a snapshot, the table at the subscriber end will get dropped and recreated, so you could lose all your undeleted data.

When you are asked questions and think you know the answers – always make sure you confirm it by doing some research afterwards, to make doubly sure.

If you find out something new or different, keep everyone in the loop so that decisions are made with all the relevant information to hand.

Here is an article you can refer to for more information.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.