0

I want to create replication in SQL Server and move the records that have changed in the last year to the test environment. Since I added a filter for last 1 year, when replication runs, only records from the last 1 year are added to the dev environment and records older than 1 year in the dev environment are deleted.

How can I add newly added records to the dev environment without deleting records older than 1 year in the dev environment?

1 Answer 1

1

Replication isn't necessarily designed (though it's possible, with some limitations) to create data in the Subscriber tables that doesn't originate from the Publisher.

Though I'm guessing when you reinitialized the Publication, you have the articles' properties for Action if name is in use set to either Drop..., Delete..., or Truncate..., all of which would clear out the table on the Subscriber side before synchronizing the data from the Publisher.

You can change that property to Do Nothing and it'll retain the original rows that are on the Subscriber side. But then you'll need to manually handle preventing duplicates rows from being synchronized, perhaps with a Snapshot Pre-Initialization script.

Example article properties set to Truncate...:

Replication Article Properties

4
  • I select "Keep existing object unchanged" and save it, but when I check again, it seems to have changed to "Drop existing object and create a new one". How do I make permanent changes to Action if name is in use?
    – Merve
    Commented May 27 at 19:48
  • @Merve sounds like a permissions issue, though I've never seen that before and I'd expect you to get an error then. I'd honestly have to see it for myself. Note you have to do this for every Article individually unless you select "...for all articles" when you make changes.
    – J.D.
    Commented May 27 at 20:10
  • 1
    Thank you very much for your help. When I selected it this way, it happened. Old records were not deleted. I'm just wondering this. Why did we choose the "copy nonclustered indexes" section in the image? What is the importance of this part? @J.D.
    – Merve
    Commented May 28 at 7:10
  • @Merve Disregard the "copy nonclustered indexes" section, that's not relevant here. This is only an example image I found on the internet.
    – J.D.
    Commented May 28 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.