How would you save a portion of your database if you had to?

[VBAHole22]

I have a database with 6 tables that are all joined together in one way or another. At certain points in time I want to take a subset of the records in one table(based on a criteria in that table) and 'catalog' them somehow along with the related records in the other tables.
It's somewhat hard to explain. I want to preserve a point in time in the db so that at a later point in time I can reinstate the records as they were in the past.
Is this even possible?
I thought one way to do it would be to create a view(with all 6 tables) that accepts a parameter and then exports all of the records that are returned into another table. Not sure how I would rehydrate this back into the original schema.

Is there even a name for this kind of operation? I'm having trouble googling it because I don't even know what a name is for what I am trying to do.

Another way I thought I could get this done would be to add a flag field to each table and then modify the flag somehow.

Yet another way could be to roll transactions back and forth but I only want to do this for a subset. I do keep my own audit table that lists changes so that might work.

Yet even another way could be to keep a lookup table telling me which version some record belongs to and then roll back based on that value. (I figure any schema I come up with would have to have a way to keep track of versions).

Any suggestions are very much appreciated

 

[jbenson001]

You may want to look into Replication, check books on line. But not sure if that is exactly what you need.

Jim

 

[carlen]

if you know which Point in time u will want to be looking at an easy way to do it would be to take a full backup everytime you need the information and then simply restore it to a second database whenever required.

alternativley create a table called records or something
have a recordid and time collumn
then add a recorid collumn to everytable
then u can limit your querys on the time stored in the record table