title: Design a process to archive data (SQL Server 2005)
body: <p>We're designing a process to archive a set of records based on different criteria like date, status, etc...</p>
  
  <p><strong>Simplified set of tables:</strong> <code>Claim, ClaimDetails, StatusHistory</code> (tracks changes in Claim status), <code>Comments</code> &amp; <code>Files</code>    </p>
  
  <p><strong>Environment:</strong> SQL Server 2005, ASP.Net MVC (.NET Framework v3.5 SP1)</p>
  
  <p><code>Claim</code> is the main entity and it has child row(s) in the sub tables mentioned. Some have details and others are used to track changes. Eventually based on some criteria a <code>Claim</code> becomes "ready to archive" as explained above. In simple words archived <code>Claims</code> will be identified from the database and treated differently in the web-app.</p>
  
  <p>Here's a simplest version: (top view)</p>
  
  <ul>
  <li>Create a script which marks a <code>Claim</code> "archived" in db.</li>
  <li>Archived row and its child row(s) can either be kept in the same table (set a flag) or moved to a different table which will be a replica of the original one.</li>
  <li>In the web app we'll let the user filter <code>Claims</code> based on the archive status.</li>
  <li>There might be a need to "unarchive" a <code>Claim</code> in the future.</li>
  </ul>
  
  <p><strong>What I need to know?</strong></p>
  
  <ol>
  <li><p>We need to keep this as simple and easy as possible and also flexible to adapt future changes - pls suggest an approach.</p></li>
  <li><p>Is a timely scheduled SQL script the best option for this scenario?</p></li>
  <li><p>Should I consider using separate tables or just add an "Archived" flag to each table?</p></li>
  <li><p>For performance consideration of the selected approach - what difference would it make if we plan to have about 10,000 <code>Claims</code> and what if its 1 million. In short pls mention a light &amp; heavy load approach.</p></li>
  <li><p>We store files uploaded physically on our server - I believe this can stay as it is.</p></li>
  </ol>
  
  <p><strong>Note:</strong> A claim can have any number of child record(s) in all the mentioned table so it gets n-fold.</p>
  
  <p>Is there a standard framework or pattern that I should refer to learn about archiving process. Any sample ref article or tool would help me learn more.</p>