Archive & Restore design question

Hi,

I have a design question.

So, I am designing an web application that will store lots of data.

Because of that we need to allow administrator users to archive some of the data.

However, the user needs the ability to view the archive data (just to know that it is there)

The administrator user can restore the data by using the web application.

My design includes 2 databases, one for the actual data, and the second for the archive data, and I manipulate it by moving data from one database to the other. What do you think about that?

Another thing is the fact that every month administrator will want to empty the archive database and save it on a disk. What is the best way of doing so? and lets say i want to take one of these backup file and restore several records from it into the archive database, what method should I be using?

Finally is there any best practices articles for this kind of issues?

Thanks,

Are you using SQL Server 2005? If you are, you can take advantage of table partitioning. But if not, then you will have to create an application that uses distributed transactions on the two databases you mentioned. Do you have to do all of this in your web application?|||

10x for the response,

All of that should be achived via the web application!

I am using sql 2005.

I am trying to figure out what are the alternatives and what is the best practice for it.

My idea, if using the current design of 2 databases, is to add 3rd database that will handle the restore from file.

Another option is to create a design that will actually to Import/Export to file.

|||

I too have a similar requirement as you have. I wonder if you have found a good design.. Please let me know how are going about diong this task.