Archival logic/algoritm

We are in need of finalizing an archival approach for one of our Web and Client server application. The major requirements are



a) User can click on Web front end to start archival process.

b) The system should move the related data to archived space in a backup location.

c) Could be a batch process.

d) The relation between tables is extensive i.e. > 30 tables need to be managed for archival one component.

e) Database size is not very huge < 10 MB.





We were planning to have a table to store archival flag, which will be set when user click on Archival. Then a batch program will copy the database in to a backup location and delete the entries from archived database where archive flag is not set and delete entry from master database where archive flag is set. The problem is how to synchronize the changes when archival process runs next time i.e. the master database would have changed so how to put that data in archival database with out removing existing data.



Any other approach/practical solutions will be very helpful.





Regards,

Mridul MishraWant to reread that and tell us if you think that makes sense?

Archive what? How do you know what to mark?

Maybe a simple explination:

"I build widgets...and when a widget is shipped I need to archive the order..."

What are you doing?|||Hmmm...Sorry for being not clear enough. Some how i thought every one is bugged with the problem as much as i do...;)

Well archival is more an event based task...Let me try to give more relevant information here..it's normal microsoft project plan which are stored and displayed for users. In most of the cases users might decide on their own whims(as most of the users are ;) ) when they feel data need to be moved to archive.

Thanks and regards
Mridul

Archival and purging

How to implement a optimal archival and purging in MSSQL SERVER
databases?? That depends on what you mean by archival and purging. You need to find out what the business requirements are for all of your data first. After you have clearly defined that, you can map out the data that will fit that business requirement. You can only then create the optimal archive and purging processes, which will vary greatly by what those requirements are.|||Thanks for that, My requirement is quite simple.
I am working for a popular bank and I have to retain data only of the past 3
years and other data must be archived.

any clue/url links to assist me|||Create views for all tables that need to be archived/purged. Don't forget to take into account all dependencies. The views should have a WHERE clause that would filter out anything that is less than 3 years old:

...WHERE <date_field> <= convert(char(10), getdate(), 101)

Use those views to archive the data. You can use BCP if you want to keep that as text files and then zip them up, or you can have a separate database that you can populate with archived data, then back it up, and then empty it (truncate for example on all tables). Then you use the same set of views to perform your deletes from your production database.