Monday, April 23, 2012

How to repair damaged .sdf file of SQL CE?

SQL server compact is a compact relational database developed by Microsoft Corp. for mobile & desktop applications. Initially, it was developed for the mobile and known as SQL server mobile application. It is freeware software & supports ACID property but do not support durability by default. All the information of SQL server compact edition (SQL CE) is stored in the .sdf file and the size of this file can be up to 4 GB. You can encrypt .sdf file by 128 bit encryption for the data security purpose. 

SQL CE database files are divided into 4 KB unit logical named pages. Each page is written to the database file. SQL CE calculates & maintains a checksum for all the pages. SQL CE pages can be corrupt or damaged due to several reasons like virus attack, power failure and many more. To verify this, scroll down.

How to Verify Corruption in .SDF file? To verify corruption in the sdf file, call System.Data.SQLserverce.SQLceengine class. It will recalculate the checksum values for all the pages in the database and match with the expected checksum value. If it returns true then your file is not corrupt. If it returns False then your file is corrupt and need to repair corrupt file.

SqlCeEngine engine = new SqlCeEngine("Data Source = Your-database-name.sdf");
if (false == engine.Verify()) {...}


How to Repair? To repair & recover SQL server compact database file, you can try repair method of SQLCeEngine. It will scan the database files and try to calculate checksum for all corrupt pages. Repair method of SQLCeEngine has two options:
  1. RepairOption.DeleteCorruptedRows
  2. RepairOption.RecoverCorruptedRows  
RepairOption.DeleteCorruptedRows: If you use this option then all the corrupted rows are discarded. You might loss some amount of data if corrupted page contains database schema.

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


RepairOption.RecoverCorruptedRows: If you use option then repair method will try to read all data from the corrupted pages. Chances of data loss in this option is very minimum but not guaranteed that your database is free from logical corruption.

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


Applied for: SQL server Compact 3.5

1 comment: