SQL Server replication without deletes?

Question

Is there a way to replicate a sql server database but not push out deletes to the subscribers?

Answer 1

It's tempting to 'intervene' in a normal replication and 'disarm' the subscriber's side delete stored procedures, but this leaves no option to recover from replication failure. If the replication tries to recover, a reinitialize may be needed and this will drop any 'stale' data that the replication agent considers deleted.

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.

Answer 2

Do this....Drop the article. Create a new storedprocedure in the corresponding database that mimicks the system store procedure (sp_del...) and contains the same parameter but does nothing. Add the article again...and set the delete store procedure under the article's properties to the new delete stored procedure that you created....

Or you can select Do not replicate Delete Statements....I think that works but i haven't tried it.

Answer 3

You don't mention which version of SQL Server you're running, but Andy Warren wrote an article on configuring INSERT, UPDATE, and DELETE behaviour in SQL Server 2005. You can configure this through the GUI, using his instructions:

http://www.sqlservercentral.com/articles/Replication/3202/