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:
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.
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 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.|
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.
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.