I have a need for a test server that hosts a small subset of data from our production systems. Is it possible to setup a SQL Server Replication Job that only keeps a week's worth of data so developers can develop reports?

Keep running 7 days of data, keeping the storage need small is the goal.

  • Do all tables have a datetime column? Is there any problem (for your app) if there's any out of sync data? What do you do with the data after the first 7 days have passed? Do you clean the tables by hand?
    – Marian
    Commented Jul 17, 2017 at 20:48

2 Answers 2


It has been a couple years since you asked this. If you found a solution you should answer your own question.

There is some good info about a similar (but different) issue at Moving Transaction Data To Another Database For Reporting Purpose

For your question "SQL Server Replication - Only weeks worth of data" the solution is going to depend on your design. Does every row of every table have a date field that indicates it is fresh this week?

I suspect the answer is no...

If you can't identify what is new in the last week, it is not possible to meet your requirement.

You don't say in your question, but I suspect lack of space on the test server is what drives your question. This means you don't have room to copy everything over and then delete what you don't want.

I would look to creating a view that has the data you need, then I would use a variant of this solution to copy the data to the test server. Do the copy off hours, once a day.


For Merge replication:

Static row filters, which are available with all types of replication. I have not done any time based filtering :)

I don't know that either of these is really going to meet your "running 7 days" criteria, which sounds to me like "last 7 days".

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.