How to: Verify and Repair a Database (Programmatically)

  • Article

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

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

Following are the options when calling the Repair method:

  • RepairOption.RecoverAllOrFail

    If the Repair method is invoked by using the RecoverAllorFail value, the recovery will be successful only if there is no data loss. If data loss is detected, the recovery is stopped, and an exception is thrown. This is the most effective option to help protect against data loss and corruption of the repaired database.

  • RepairOption.RecoverAllPossibleRows

    If the Repair method is invoked by using the RecoverAllPossibleRows value, the database will attempt to read all data, including rows from the corrupted pages. This potentially results in more data being recovered. However, this option does not guarantee that the recovered data will be free of any kind of corruption.

  • 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 data rows or database schema. However, data recovered by using this option should be free from corruption.

  • RepairOption.RecoverCorruptedRows

    The option is deprecated in SQL Server Compact 4.0. You should use RecoverAllPossibleRows option instead. 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.

Important

The administrator should remember the following points while and after using the Repair method.

  • The Repair method does not guarantee complete data recovery for every database. Some forms of data corruptions cannot be repaired completely, regardless of the Repair option that is selected by the application.

  • The administrator should run the Verify method on the destination database, after repairing the source database.

  • Regardless of the repair option that is selected, the Repair method will write details about any database inconsistency into a log file. This file is written to the same directory that contains the source database .sdf file. The application or a database administrator should examine this log file to determine whether the resulting database is valid.

  • When the application does not use the RecoverAllOrFail option, it is the responsibility of the application or of database administrator to validate the resulting database. This is accomplished by examining the log file to determine whether the database is reliable.

  • If you or the application cannot validate the resulting database, restore the database from a backup, or manually re-construct the database.

Procedures for SQL Server Compact 4.0

To verify a database

  1. Create an Engine object.

    SqlCeEngine engine = new SqlCeEngine("Data Source = Northwind.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 RecoverAllorFail Repair Option.

    engine.Repair (null, RepairOption.RecoverAllorFail );

  • 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.RecoverAllorFail );

Example

This example shows how to verify a SQL Server Compact 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.RecoverAllorFail);
}

Dim engine As New SqlCeEngine("Data Source = AdventureWorks.sdf")

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

See Also

Concepts

Maintaining Databases (SQL Server Compact)