TornPage Detection:
Up to sql server 2000, for data corruption process is called torn page detection, this option is default on. The data page is of size 8k and combination of 8 pages is called extent. where as hardware storage is stored in tracks and sectors,sector size is 512 bytes. and page header contains information of 512 bytes. so whenever data is updated /changes it save in a chuck of 512 bytes. if this option is enabled, it validates for 512 bytes on page header. and if any data corruption occurs it will check in 512 bytes chucks. so it is not accurate and generally this occurs 823, 824 error. most of the time the only option left would be to restore the last good backup.
Page Checksum: in sql server 2005. development team has introduce a new improved option over torn page detection called page checksum. whenever page is read from physical storage to memory or write to physical storage from Memory. on every page there will be a bitmap which is sum the bits on that page and validate it. this process is very efficient. So compare to torn page of 512 bytes it checks for every bit on page. also in for sql server 2005 onwords page level restore page is also possible.
Checksum is also included for backup and restore.
http://technet.microsoft.com/en-us/library/cc966500.aspx
http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/6d45e4a9-2c9b-451d-b7c2-1fa96c6cd03a
http://www.sqlservercentral.com/Forums/Topic663009-266-1.aspx#bm663026
Please correct me if I am missing something.
Thanx
Vinay
Vinay Thakur 
Just wanted to add something to para1, what ever the error (i.e 823,824), i suggest that checkdb should be executed and is interpreted before attempting a restore. It can just be a integrity issue with the index page that has caused the error which does not require a DB restore.
Appriciate Jude, for you kind feedback.