Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dohamsg
Starting Member
22 Posts |
 Posted - 2010-12-11 : 06:38:34
|
| Hi, [SQL Server 2008 Express] [C# 4.0]I've developed a winforms application, I want to give the user the opportunity to archive old data at the beginning of each year.The user can for example connect to to consult his archive.The real scenario happens as follows: The user is working on the database, till the beginning of the next year(2011), he clicks on , I want to copy then delete all 2010 transactional data (keeping customers, suppliers, employees, balances...) data to a new database called , I keep track of all archive filenames and location in an XML file.I'd like to know the steps to follow on both sides, SQL and C# in order to achieve this task.I welcome any suggestion, tip, samples, links, books...Thanks. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-12-11 : 06:44:06
|
| Have a look at table partitioning.You can partition your table based on the year.PBUH |
 |
|
|
dohamsg
Starting Member
22 Posts |
Posted - 2010-12-11 : 08:20:15
|
| Hi,01-I have too many tables.02-The archived data is rarely consulted.So, I think of removing old data to another database.Thanks. |
|
|
|
Sachin.Nand
2937 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-11 : 08:56:49
|
| "The archived data is rarely consulted."Well ... either it is consulted, in which case it needs to be online, or it is offline - and then has to be mounted in order to be consulted.You can copy the archive data to an "archive database", delete the data from the main database, and detach the archive databaseOr you could BCP the data out (using native format) but then it would have to be re-imported for reuse. We use this for log data which we do not expect ever to have to re-import (but we might ... e.g. for a fraud investigation)But unless disk space / database size is an issue personally I wouldn't bother - keeping the data online gives the user the maximum flexibility.If its a performance issue then partition the data tables as Sachin.Nand said. |
|
|
|
|
|
|
|
|
SQL Server Archiving old data steps