How to: Verify and Repair a SQL Server Compact Edition Database (Programmatically)

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

SQL Server Compact Edition database files are divided into logical 4 KB units named pages. As each page is written to the database file, SQL Server Compact Edition 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.

Note

The Repair method is useful only if SQL Server Compact Edition 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.

To verify a SQL Server Compact Edition 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 SQL Server Compact Edition 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);
  

Example

This example shows how to verify a SQL Server Compact Edition 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

See Also

Other Resources

Maintaining Databases (SQL Server Compact Edition)

Help and Information

Getting SQL Server Compact Edition Assistance