In this article, I have described about how to repair a corrupt “MS SQL server 2005 (compact edition) database” with the help of engine object repair methods.
MS SQL server 2005 compact edition database has several files and all these files are divided into four kilobytes unit each, these files are known as pages. SQL server compact edition database stores a checksum for all pages. If a page is corrupted or damaged then checksum of the page does not match with stored checksum. There are so many reasons for the corruption in the database file like Meta data structure corruption, virus attack, sudden system download, hardware failure and many more. If you have verified that there is a corruption in the database file then you can repair sql server database file by the engine object repair method.
You can verify corruption is the database file by calling system.data.sqlserverce.sqlceengine command. If this command returns a true value then there is no corruption in the database file and if this command returns a false value then there may be some corruption in the database file. The syntax for verifying the corruption is given below:
Syntax for verify
if (false == engine.Verify()) {...}
If a database file has been corrupted then you can recover database file by engine object repair methods. This method scans and fixes the corrupt database file. Engine object repair method provides two repair methods:
1) repairoption.deletecorruptedrows
2) repairoption.recovercorruptedrows
Repairoption.Deletecorruptedrows: This method discards all the corrupted pages and recovers the database files but you may lose some data if corrupt database file contains the database schema.
Syntax for this method
engine.Repair(null, RepairOption.DeleteCorruptedRows);
Pros: Guaranteed that recovered database is free from logical corruption.
Cons: You may lose some data, if you will use this method.
Repairoption.recovercorruptedrows: This method tries to read more data from corrupted database files and recover more data as can possible.
Syntax for this method
engine.Repair(null, RepairOption.RecoverCorruptedRows);
Pros: Recover more data in comparison with repairoption.deletecorruptedrows.
Cons: Does not guarantee that recovered database is free from logical corruption.
MS SQL server 2005 compact edition database has several files and all these files are divided into four kilobytes unit each, these files are known as pages. SQL server compact edition database stores a checksum for all pages. If a page is corrupted or damaged then checksum of the page does not match with stored checksum. There are so many reasons for the corruption in the database file like Meta data structure corruption, virus attack, sudden system download, hardware failure and many more. If you have verified that there is a corruption in the database file then you can repair sql server database file by the engine object repair method.
You can verify corruption is the database file by calling system.data.sqlserverce.sqlceengine command. If this command returns a true value then there is no corruption in the database file and if this command returns a false value then there may be some corruption in the database file. The syntax for verifying the corruption is given below:
Syntax for verify
if (false == engine.Verify()) {...}
If a database file has been corrupted then you can recover database file by engine object repair methods. This method scans and fixes the corrupt database file. Engine object repair method provides two repair methods:
1) repairoption.deletecorruptedrows
2) repairoption.recovercorruptedrows
Repairoption.Deletecorruptedrows: This method discards all the corrupted pages and recovers the database files but you may lose some data if corrupt database file contains the database schema.
Syntax for this method
engine.Repair(null, RepairOption.DeleteCorruptedRows);
Pros: Guaranteed that recovered database is free from logical corruption.
Cons: You may lose some data, if you will use this method.
Repairoption.recovercorruptedrows: This method tries to read more data from corrupted database files and recover more data as can possible.
Syntax for this method
engine.Repair(null, RepairOption.RecoverCorruptedRows);
Pros: Recover more data in comparison with repairoption.deletecorruptedrows.
Cons: Does not guarantee that recovered database is free from logical corruption.
