Archiving old data on SQL Server Express 2008

[SQL Server 2008 Express] [C# 4.0]

I've developed a winforms application, I want to give the user the opportunity to archive old 开发者_StackOverflow社区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.

---

• Do you have a maintenance window to accomplish this task. This is to understand there is no transaction happening while archival process is in progress
• If you have a maintenance window below is my suggestion
• Step 1 - Get FUll DB backup during maintenance window
• Step 2 - Have a Archival Script which would delete the data for previous years
• Step 3 - Validate the Delete data, This can be tested and validated on a Test Data Set
• Step 4 - Delete the Data in batches. Sometimes you may end up with log full issues, You would be delete records in huge records sets, Make it batched delete
• Step 5 - In case of any issues, Use the Full backup taken in Step 1 and continue until you have a proper archival script