C# Large Data backup strategy to free database

Question

The application we have takes large amount of data most of which is called transactions which is mostly clock in and clock out information of employees,most of which is processed and stored in an other table.Over years this data may grow.I was thinking of backup data to text files and export them if ever from those text files,export will clear the data from the database.well now the data is in textfiles that can be archived etc...This is a idea from an other software I saw...what about you suggestions....

Few point:

• I would leave the database alone,since it could or could not have a dba and there could be many solutions with the database involved.When we deploy the application at a customer I would least want to talk to the dba and get involved in the nasty stuff...So I could just tell them after a year that "hey the db size is too much" so now just do the dump to the text or xml file and clear the table is once solution.The reports run off another master table..I am thinking in the long run like in 4 years I may have a huge database with table having shit amount of transactions since employees grow X per shift X per clock in blaaahhh... so if they could follow the dump to text file per month or per what ever.... i.e..

Answer 1

SQL Server 2008 Supports Partitioning of Tables.

http://msdn.microsoft.com/en-us/library/dd578580.aspx

This may be a viable strategy for you. This was probably my favorite new feature of SQL2008.

Answer 2

I think it depends how you need to access the old data.

If you just need to run periodic reports on the old data, you can move the data into a separate archive database. Basically, just create a copy of your live production database. In the copy, just delete the recent entires that you want to keep in your live system. In the live, production database, delete the data that will stay in the archive database. You essentially prune your database periodically and move the old data into your archive database. I've used this technique with a vendor's time writing app that would slow to a crawl every few years because it wasn't designed to hold the amount of data being stored in it. Fortunatley, we could point the app to whichever database we needed to run reports.

Answer 3

I'll try to break my answer into two variations depending on the particulars of your environment.

1. Embedded database in your application with NO DBA -- MS Access, Berkely DB, etc.
2. Networked RDBMS (with or without DBA) -- SQL Server, Oracle, MySQL, etc.

My gut reaction based on how you phrased things is that you don't have a DBA.

In the first case (embedded database with no DBA), then exporting to text files is fine. You might consider XML since it pretty much contains its metadata inline and is plain text. That could future-proof you to some extent, since a new person could figure out what is in the archive data pretty easily. You would want to establish guidelines for how long you want to retain data and when it should be destroyed. If you are a publicly traded company, there may be laws involved you need to respect.

In the second case (networked RDBMS), then you should defer to the DBA for data retention. A DBA should have a plan for that. If you do NOT have a DBA, then you have several options.

You could go with your idea of exporting text files. You could partition the table and roll off partition segments (this depends on the RDBMS how you do it -- Oracle would be tablespaces, SQL Server would be file groups, I have no idea about MySQL). You could have a second database as either an archive database or an actual data warehouse.

The other option would be to bring in a contract DBA to help sort it out.

Answer 4

Many database can do this work. I can mention Firebird SQL who have good C# provider