There are many subtle details -- whole books have been written on the topic. But at a broad level:
1) Make an empty database with the same schema as your production db.
2) Decide which tables need to be archived -- "all tables" is unusual. If you think you need to archive all of your tables you likely have not thought about it enough.
3) Decide on the criteria -- perhaps it is something like "orders that were shipped at least 12 months ago".
4) Determine how to apply that criteria to the tables selected in step #2. It may not be as simple as "WHERE datefield <= X". You might, for instance, have parent-child relationships to consider like orders to order-lines where only one table has the key field available.
5) Write some code to accomplish the archive & purge. Personally I prefer to connect the two databases and use BUFFER-COPY rather than dumping to .d files and then loading them. Something like:
Code:
/* totally untested psuedo code...
*/
for each src.order exclusive-lock where src.order.order-date < ( today - 365 ):
for each src.order-line exclusive-lock where src.order-line.orderNum = src.order.orderNum:
create dst.order-line.
buffer-copy src.order-line dst.order-line. /* archive */
delete src.order-line /* purge */
end.
create dst.order.
buffer-copy scr.order dst.order. /* archive */
delete src.order. /* purge */
end.
Notice that the code above is
restartable -- it can be aborted at any time and then re-run to completion later. A huge mistake that people make is to try to turn the archive & purge operation into a db transaction. Doing that is a very bad mistake. You end up with a huge bi file and gigantic lock table. If you take the "big transaction" approach you will eventually be in a position where you cannot successfully run your archive and purge.