Options to not replicate SQL Server DELETE commands

By:   |   Comments (22)   |   Related: > Replication


I have transactional replication configured in my production environment. The business team has requested that I do not replicate delete operations on certain articles.  In this tip we look at a couple of options to not replicate DELETE commands.


In some cases older data may be required on the subscriber, but deleted at the publisher. In order to meet this requirement we could use either of the options outlined below. Note, both options assume transactional replication is already configured between two databases.

Option 1: Modifying Replication Settings

In this option we will use SSMS to make the changes, but this could be done using T-SQL commands as well.

In SSMS go to Replication -> Local Publications and right click on your publication and select Properties.  In the Publication Properties window click on 'Articles' and select the relevant article.  Go to "Article Properties" and select "Set Properties of Highlighted Table Article" as shown below.

sql publication properties

In the article properties window, change the "DELETE delivery format" to "Do not replicate DELETE statements".

sql server article properties

After the change click OK and you will see the below prompt.  As the article property has been changed the subscriptions need to be reinitialized. Click "Mark for Reinitialization" which causes the snapshot to be applied to the subscriber.

reinitialize subscriptions

In SSSM, navigate to Replication and right click and select "Launch Replication Monitor" as shown below.  Go to your publication and click View Details as shown below to see the snapshot progress.

sql server replication monitor

After clicking "View Details", you can see the details of the snapshot generation for the subscriber.

sql distributor to subscriber history

As you can see the entire snapshot is getting applied to the subscriber.  This option must be used with caution as there may be cases where your subscriber database is used as an archive and may have more data than your publisher database.  However, this depends on your environment and if your application is fine with getting the entire snapshot you could use this method.  Also, for very large databases you may not want to reinitialize the entire snapshot either, so again use caution.

Once this change has been made and if there is a need to revert to replicating deletes, just perform similar steps as above to allow delete operations by calling the replication delete stored procedure at the subscriber. Call <stored procedure> is the default syntax for DELETE operations for SQL Server transactional replication. Once you select Call <stored procedure>, you will also need to provide the DELETE stored procedure name for this specific article which would be available in the subscriber database.

Option 2 : Modifying the replication stored procedure in the subscriber database

For transactional replication, by default, stored procedures get created in the subscriber database for insert, update and delete operations. These could be viewed as shown in the screenshot below.

sql replication stored procedures

REP_S is the subscriber database and we could see the insert, update and delete stored procedures that were created for each subscribed article.  In option (1), we need to be concerned about the snapshot getting applied when we modify the article property. However, in order to overcome this shortcoming, there were some discussions I came across on the internet to make the replication delete procedure in the subscriber database to just RETURN and not actually do the DELETE. This would ensure that the replication delete procedure in the subscriber database exits unconditionally even though it is called. As per books online, RETURN in T-SQL enables you to "Exit unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed." (Source - http://msdn.microsoft.com/en-us/library/ms174998.aspx).

Using this concept, the delete procedure in the subscriber database could be modifed as shown. I have just added the RETURN statement right after the BEGIN, so the stored procedure exits immediately without doing the actual DELETE.  With this approach, we need not worry about the snapshot getting applied again to the subscriber.

--Include RETURN statement in stored procedure and alter the stored procedure in the subscriber database
/****** Object: StoredProcedure [dbo].[sp_MSdel_dboArticle_1] Script Date: 10/14/2011 22:00:43 ******/
ALTER procedure [dbo].[sp_MSdel_dboArticle_1] @pkc1 int
delete [dbo].[Article_1]
where [Col1] = @pkc1 if @@rowcount = 0
   if @@microsoftversion>0x07320000
       exec sp_MSreplraiserror 20598

In order to revert to the older configuration, to allow SQL Server to replicate deletes, you would need to just comment or remove the RETURN statement in the stored procedure.


Both options could be tested easily with a simple publication of a single article and performing the sequence of steps as shown above.  Also you should use Replication Monitor to see if there are any errors.

Note: the above steps were performed using SQL Server 2008 R2.

Next Steps
  • Consider testing this scenario through a simple transactional replication setup
  • Refer to other related tips on replication to get familiar with the concepts

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, March 16, 2021 - 7:09:27 PM - Mohammed Back To Top (88421)

Thank you for your comments. Yes, we need to be cautious as mentioned in the tip.

Your alternatives sounds good but haven't tried it out.


Monday, March 15, 2021 - 9:32:39 PM - DBALuke Back To Top (88404)
This solution has risk, once you need to reinitialize the replication, the history data will be gone. Do you think the solution of Stijn Sanders is better?

An alternative is to use a normal replication, and use a script that generates insert and update triggers on all tables in the subscriber database, that insert/update that data into yet a third database. This way the third DB will collect all the data that ever existed, the second DB can re-initialize it's subscription if it needs to (when you do, just remember that bulk inserts don't call the insert trigger and check for new data and add it to the third DB), and the first DB doesn't have to perform the extra work that the triggers are.

Wednesday, July 25, 2018 - 4:34:26 AM - Gilles Back To Top (76822)

A third very useful option (which would also solve the problems of Rama and pk) is to replicate deletes, but put an "INSTEADOF trigger for DELETES" on the subscriber. In this trigger you can put the logic if the record should be actually deleted or ignore the delete if the record was deleted for archiving purposes.



Thursday, May 4, 2017 - 6:47:51 PM - pk Back To Top (55449)

 Rama, I have same problem like you mentioned and it will be great, if you an answer or anyother solution you come across


Thursday, February 20, 2014 - 9:51:32 PM - Rama Back To Top (29521)


I have a production database which replicates to replication database. I would like to perform archving on production database where I would like keep three months old data and delete rest of the data but dont want to replicate such actions to replication database. Whatever you mentioned in the article should work but I have a scenario to consider where as part of my application, there are operations that delete records on database. So if I stop replicating deletes completely,  how could I replicate inentional deletes. In short I want to replicate deletes but dont wanna replication purge action from production database.


Thanks in advance!

Thursday, June 27, 2013 - 6:51:18 PM - Mohammed Back To Top (25603)


Thanks for the question. It appears you may be correct. Didn't get to test it though.



Thursday, June 27, 2013 - 11:39:25 AM - Edward Hixon Back To Top (25601)

On question that I have regarding option #1; As I am turning off DELETES temporarily and want to turn then back on after the deletes on the publisher are correct, and if I follow the "Mark for Reinitialization" step, won't that replace the subscription data and thus result in the deletes being effectivly replicatated? 

Tuesday, March 5, 2013 - 11:34:03 PM - MM Back To Top (22582)

Do we have a similar option for snapshot replication as well?

Tuesday, February 5, 2013 - 12:26:17 PM - Sapen Back To Top (21931)

Nice Article. Thanks for the post.  Is there a way that I can restrict the updates specific only to a column? Lets say if updates occur on source table on column a, column b and column c then I dont want any thing to get updated in the destination. But if update occurs on column d on source table then I want it to get updated in destination table too. Is this possible?


Friday, January 25, 2013 - 9:11:52 AM - Bill M Back To Top (21703)

Exactly what we were looking for ...thanks!

Thursday, August 2, 2012 - 8:22:28 AM - Mohammed Moinudheen Back To Top (18883)

Thank you Hy, appreciate your feedback.

Thursday, July 19, 2012 - 1:00:53 AM - Hy Chan Han Back To Top (18642)

It really help mes.

My problem has been occured for a long time it is now resolved by this useful article.

Thank for posting and keep your good works.



Wednesday, November 9, 2011 - 7:58:07 AM - Mohammed Moinudheen Back To Top (15052)

Ron, Thanks for your inputs. Very useful.

Tuesday, November 8, 2011 - 5:51:22 PM - Ron Back To Top (15049)

We take another route in that all deletes are done via stored procedure calls on the publisher.  We take advantage of that to replicate execution of these delete stored procedures to a target procedure on the subscribers that does nothing.  This has advantages of not requiring a new snapshot, not touching any of the generated code, and continuing to work even if a new snapshot is performed (which would typically wipe out your modifications to the generated procedures).  It does not protect the data from direct deletions, so it only works if you do not allow your users direct access to deleting table data.

Friday, November 4, 2011 - 8:33:42 AM - Mohammed Moinudheen Back To Top (15018)

Jason, Thanks for your comments.

Friday, November 4, 2011 - 7:37:51 AM - Jason Bunn Back To Top (15016)

With option #2, you have to be careful, since any schema change, if replicated, causes a regeneration of the stored procedures, and if you are manually editing the delete proc, you run the risk of losing that change.  A safer option may be to use "register custom scripting" to generate custom stored procedures for the delete statements.  That way, you are using a procedure that you develop, rather than manually modifying a system generated procedure, and this will ensure that your functionality will survive a schema change.

Friday, November 4, 2011 - 7:28:56 AM - Mohammed Moinudheen Back To Top (15015)

Steve, Thanks for your comments. I thought option 2 is used if you don't want to apply the snapshot.

Friday, November 4, 2011 - 5:51:45 AM - Steve Johnson Back To Top (15014)

Option 2 is completely misleading and will cause data loss on the subscriber when the snapshot is applied - the delete stored proc, is used when replicating transactions post-snapshot application.

The publication option 'Destination Object-->Action if name is in use' - which has a default value of 'Drop existing object and create a new one' will remove the existing data at the subscriber, before the snapshot data is bcp'd, regardless of your suggested stored proc. hack.

Tuesday, November 1, 2011 - 2:44:20 PM - BuntyBoy Back To Top (14983)

Very good article Moinu!!!!!

Wednesday, October 26, 2011 - 11:36:21 PM - Mohammed Moinudheen Back To Top (14935)


Thanks for your comments. For not replicating insert or update, you could try the same options as described and select the correct format under the 'statement delivery' section of article properties. Or you could try including the 'return' statement in the relevant stored procedure in the subsriber database.


Mohammed Moinudheen



Wednesday, October 26, 2011 - 8:34:44 AM - Tony Back To Top (14923)

BTW - Great article!!!

Wednesday, October 26, 2011 - 8:33:44 AM - Tony Back To Top (14922)

I am new to replication.  Are there any options to not replicate insert or update commands?  I assume select statements are not replicated.

get free sql tips
agree to terms