CHECKSUM and TORN_PAGE_DETECTION

CHECKSUM and TORN_PAGE_DETECTION

Its basically making sure our data not get corrupted.
It will do verify the data against the backup while restoring the database.

You have total 3 options in the SQL server to take care of this task.

--You can find two scenarios to do the stuff in sql server
Note : We need to choose one of these, But you will get the benfit-over while
retoring the database.
1) CheckSum and
2) Torn_Page_Detection and
3) None

CHECKSUM do basically check each and every bit writing in to the page while restoring it
from the backup(You choosen the option and got the backup), If it finds anything
mismatched. Then it will throw the (824) error. It is much safer, though little burden
(as it is checking each bit).

Torn_Page_Detection is the one which will write a bit for every 512 bytes of data as it is
not corrupted. It is lightweight-er but its not that safe compared to CHECKSUM.

None will make your database recover/restore very fast compaing the other options.
But, there is no guaranty you have good data.

You can find the work Description below :

So, when ever your taking the backup of the database....
We usually think have to get the good backup (which is not corrupted).. Basically SQL will
take care of that....

Otherhand while restoring the same backup located on the disk for some reasons you may get (corrupted
your backup) wrong data as pages(DB) doesnt load properly during Power loss,hardware failure..

So in that case these two options will help us..
If its checksum It will read every bit from the back up and write it into disk. If find some
thing wrong while writing the disk... then it will try 4 times to read properly from the
backup.. If it doesnt read the page successfully It will write on the SQl log and application
log as displaying 824 error while recovering the database.

But it will restore the database successfully.
It will write the record in the suspect_pages system table in msdb database


-- To find out the suspect pages in the database while restoring.
select * from msdb.dbo.suspect_pages