I have a production sql db of 600gb and a historical server where i move the full db every month. Now we want to have only the last 3 months information on the production server, and move every week data from the production DB to historical server, to then delete each register which have more than 90 days old.

I couldnt find the best way to do that. Should i save the date of each register i put on the database to know when it was stored? Does sql server have any function to know when a register was stored?

Both database are stored on AWS, so it´s easy to move data between them. Thanks a lot!

closed as too broad by Erik Darling, Md Haidar Ali Khan, hot2use, Colin 't Hart, Max Vernon Sep 11 '18 at 19:47

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Most people doing stuff like this use SSIS. It's a pretty natural fit for moving chunks of data. – Erik Darling Sep 11 '18 at 0:50

You can look into replication as well (not sure if it works on AWS though)

[Prod SQL] --> [Archive SQL] constantly

periodically delete on Prod: anything > 90 days
periodically delete on Archive: anything > 365 days maybe?

If not replication, does "linked server" work on AWS?

Not sure what you mean by "register"

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