archive data

[ersatz]

Hello,

I need your help.

I will tray to explain you what is my problem

I must create a sql server 2005 database that will be used by a .net application.
This database will contain scanned bills too.
The client told me that we will have around 40 000 files every year and each file is 300-400 k.
They told me that they want to keep data for 10 years.
They want to keep only 3 years data available in the database, archive the rest and if needed they must be able to access old data (from the last 10 years).

I don’t know if it’s a good idea to create a database file for every year of data or to archive the old information in another database.
If I choose the last solution, later a can have problems if my database will change ( add/remove columns in tables, for example).

Any suggestion will be really appreciated?

 

[bborissov]

You can switch to SQL Server 2008.
Then you could use a FILSTREAM.
That way you could save the files over the file system and keep them outside of the DB.
Then you do not have to think how to split the DB

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[ersatz]

Hi bborissov.

Thank you very much for your answer. Unfortunately I can’t use sql2k8 for the moment. We’ll begin migration in 2011.

I have a kind of application that store files on disk and it’s a big problem because every time when I take a db bkp I must be sure that a folder bkp is tacked too and many times I realized that data are not synchronized.