In this topic, you will learn how to verify and repair a corrupted Microsoft SQL Server Compact 3.5 database by using the Engine object. For more information about using the SqlServerCe namespace, see the SqlServerCe namespace reference documentation.

SQL Server Compact 3.5 database files are divided into logical 4 KB units named pages. As each page is written to the database file, SQL Server Compact 3.5 calculates and saves a checksum for that page. If the page is modified or corrupted after being written to the file, it will no longer match its expected checksum.

Calling the Verify method of the System.Data.SqlServerCe.SqlCeEngine class recalculates the checksums of every page in the database file and verifies that the checksums match their expected values. If this method returns true, there has been no database file corruption. If this method returns false, the database file has been corrupted and the application should call the Repair method.

If a database file becomes corrupted, you can try to recover the database file by using the Repair method of SqlCeEngine object. The Repair method scans the database and calculates the page checksums. If a checksum does not match the checksum that was calculated previously when that page was written to the database, that page is considered corrupted.

There are two options when calling the Repair method:

  • RepairOption.DeleteCorruptedRows

    If the repair method is invoked with the DeleteCorruptedRows value, all corrupted pages are discarded. This might cause a significant data loss if the corrupted page contains database schema. However, data recovered by using this option should be free from corruption.

  • RepairOption.RecoverCorruptedRows

    If the repair method is invoked with the RecoverCorruptedRows value, the database will try to read data from corrupted pages. This can cause more data being recovered, but does not guarantee that the data recovered will be free of logical corruption.

The Repair method is useful only if SQL Server Compact 3.5 returns an error with a native error number of 25017 (SSCE_M_DATABASECORRUPTED), or if a call to the Verify method of the SqlCeEngine object returns false.

Procedures for SQL Server Compact 3.5

To verify a database

  1. Create an Engine object.

    SqlCeEngine engine = new SqlCeEngine("Data Source = AdWks.sdf");
    
  2. Call the Verify method to examine the database for corrupted rows.

     if (false == engine.Verify()) {...}
    

To Repair a database

  • If the database has corrupted rows, call the Repair method to fix the database. You can choose to delete all corrupted rows by passing in the DeleteCorruptedRows Repair Option, or try to recover corrupted rows by passing in the RecoverCorruptedRows Repair Option.

    engine.Repair(null, RepairOption.RecoverCorruptedRows);
    
  • In order to create a case sensitive repaired database, set the case sensitive property in the connection string property of the Repair method. For more information about case-sensitive databases, see Working with Collations (SQL Server Compact). Example:

    engine.Repair("Data Source= Test.sdf; LCID= 1033; Case Sensitive=true;", RepairOption.RecoverCorruptedRows);
    

Example

This example shows how to verify a SQL Server Compact 3.5 database, and if corrupted rows are found, how to repair the database while recovering data from the corrupted rows.

SqlCeEngine engine = new SqlCeEngine("Data Source = AdventureWorks.sdf");

if (false == engine.Verify())
{
   MessageBox.Show("Database is corrupted.");
   engine.Repair(null, RepairOption.RecoverCorruptedRows);
}
Dim engine As New SqlCeEngine("Data Source = AdventureWorks.sdf")

If False = engine.Verify() Then
   MessageBox.Show("Database is corrupted.")
   engine.Repair(Nothing, RepairOption.RecoverCorruptedRows)
End If

Concepts

Maintaining Databases (SQL Server Compact)