title: resolving corruption in SQL Server Compact Edition database files
body: <p>This is not a query. Its a summary of our solution to get around the problem of corruption in SQL Compact Database files with (almost) definite success. SQLCE Corruption is a very common problem. We've received tremendous help from earlier posts in StackOverflow, and hence this post.</p>
  
  <p>Our product is a 3-tier architecture with the server running as a Windows Service connected to Rich Clients through .Net Remoting. Our product uses SQLCE since 2006. We have moved from v3.1, to v3.5 and now v4.0. We've a custom OR-Mapping tool for some very specific requirements. We've faced limited problems with v3.1, we've faced more with v3.5 and v4.0.</p>
  
  <p>Initially with v3.5, we implemented <code>SqlCeEngine.Repair</code>. But it only drops the corrupted data, and tries to recreate a stable db. We found that Foreign Keys of affected tables went missing. We had to do away with this immediately. We started notifying users about db corruption, and restore the last backup. This only provided a temporary relief; the problem of corruptions still stood.</p>
  
  <p>This year, we adopted v4.0. However, our application also introduced several new features which tremendously increased the number of database calls. v4.0 began well, but started giving problems when the software usage increased. Corruptions happening while the application was running caused neither by Windows crashing, abnormal shutdowns or disk issues. The database just corrupted. </p>
  
  <p>The next post covers the solution that we devised for this problem:</p>