We've got a list from app owners to restore say 20 databases from a total of 100 available production server databases onto a test server on weekly basis.

Moreover, they require it in a way that backup that would be occurring should contain data of last 60-90 days for all the above 20 databases.

I am not sure if that can be achieved. If yes, kindly give suggestions how can I go around this to automate the backups as said above.

For automating the restore process, should I go with Log Shipping or Transactional replication? We have space issues on test and the client does not want to invest much!

  • Send them a link to the documentation what backup does. Backup/Restore do NOT filter data like this. And you should not do that on the backup side - backups are for backup. I.e. the backups should happen regularly, restores are another thing. But as a full backup breaks incremental backup chains.... you do not want to do them outside a workflow.
    – TomTom
    Mar 4, 2015 at 15:46

1 Answer 1


As already suggested, you cannot achieve this using backup/restore. You can also not achieve it using log shipping.

I would look at using transactional replication perhaps from views, which would define the 60 to 90 day window.

Be aware (or make your stakeholders aware) that replication is not free and comes with its own (usually small) performance overhead.

Your Answer

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

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