CODEWITHSUNDEEP
sql-serverreplication
masoud ramezani
masoud ramezani

Reputation: 22930

Is there a way to force replication that replicate only inserted and updated data?

I want to have a version of my db that contains all of data from first to now. If I use sql server replication, can I force that to ignore delete operation for replication?

Upvotes: 1

Views: 2703

Answers (3)

Jason
Jason

Reputation: 405

Yes, you can. If you use Transactional Replication, you define what is done for updates, deletes and inserts on table articles that you publish.

EDIT to add more detail:
So, when creating a new Transactional Publication, under the Articles step (Figure 1), you can select all tables and "Set Properties of All Table Articles". The section under "Statement Delivery" (Figure 2) offers options on "delivery formats" for INSERT, UPDATE, and DELETE statements. You can set the format to "Do not replicate DELETE statements" or, for example "Call <stored procedure>", which can call a stored proc that sets a flag on each row that has been deleted on the Publisher.

Figure 1 Articles Wizard Step

Figure 2 Article Properties Window

With reference to Hilary Cotter's answer on MSDN.

Upvotes: 0

van
van

Reputation: 77002

I am not aware of such feature.

And I would be surprised if one exists out of the box. What if instead of UPDATE one does DELETE and INSERT? Your target database will not be able to handle such change because it will not delete a row with some unique identifier (column value) XXX, and then will try to insert a new one with this identifier, resulting in a unique key violation.

EDIT-1: If what you want is the only-recent database in production and the whole database for audit, then maybe you should try different solution:

  1. Logical Delete and a View: you change your DELETE logic (either by changing the DELETE command, or by using INSERT OF trigger) to only mark rows as deleted. Then you create a view that basically wraps your table but filters only those that are not deleted. Run all your SELECT/INSERT/UPDATE queries against this view instead. At the end also in your current solution you do mark "deleted" records as deleted somehow, right?
  2. Audit tables: keep your history also in your main database, but in AUDIT (shadow) tables. Again you can do this with AFTER triggers almost non-intrusively.

Upvotes: 2

p.marino
p.marino

Reputation: 6252

Not a common scenario, see if this can help: http://www.eggheadcafe.com/software/aspnet/35648651/disable-delete-on-transac.aspx

Upvotes: 1

Related Questions