Comments (22)

  1. Thanks to Paul Randal again for pointing out that the 2-bit pattern used for torn page detection uses an alternating pattern rather than repeating the same pattern every 512 bytes. Also, thanks to Paul for the recommendation of using RESTORE VERIFYONLY … WITH CHECKSUM;.

    See edits in post.

  2. For the torn page detection, How does it determine what pattern to write?

    1. As far as I know, the details of the bit pattern are not documented. Paul Randal may know, but I’ve never seen that info made public.

      1. Starts with 10, 01, 10, 01 alternating down the blocks, then flips them next time it writes the page.

        1. Thanks again Paul!

  3. I just scripted out the restore code from the GUI and it does not use CheckSum. That would have been nice if they coded for it.

    1. Agreed. Sadly, the GUI doesn’t support a lot of good features.

      1. It seems amazing that SSMS uses the checksum option differently when scripting backups if you choose compression verses np compression.
        Using compression I get checksums scripted using SSMS 2008 / 2012 and when compression is left off the backup then checksums are not scripted – I checked BOL and it stats this also. Yet another reason to go the TSQL route.
        Great series Robert.

        1. Thanks Martin! I wish they would make checksum the default option for backups and restores, but that would probably break backwards compatibility rules.

  4. Hi, i have a vendor DB that is set to torn page detection. We are running on SQL 2008 R2, I am guessing that if the default is with checksum the vendor must have changed the setting to torn page detection when they setup the DB. Are there any possible reason they would do this? Are there any risks to changing it to checksum?

    1. Hi David. The vendor’s DB was probably developed initially on SQL 2000 or earlier version when torn page detection was the default and then they generated scripts that hard-coded the torn page option.

      There is no risk to changing it to checksum, but I would first verify that this would not break your support agreement with the vendor.

    2. Sorry David. I saw you posted a reply, but it accidentally got deleted. Can you repost it?

  5. No worries, I will follow up with the Vendor regarding checksum. My question was around the need to do a complete index rebuild after turning on checksum. Our vendor provide all the DB maint jobs that they say need to be run against the DB. Can i just turn on checksum and leave SQL to just create the checksums as the data is accessed or? I understand that the pages will not have a checksum created until the are changed but this would be better then just staying on torn_page_detection.

    1. I generally recommend doing a full rebuild of all indexes during your next index maintenance period. Otherwise, There could be areas of your database not properly protected. I guess you need to weigh the risks and benefits and decide if you think it is okay to wait.

  6. Is there any way to detect the number of pages or percentage of pages which do not have a checksum for a database which has been changed from torn page detection (if the maintenance window didn;t allow for a full index rebuild for example)?

    1. Unfortunately, that isn’t tracked anywhere as an aggregate. You would have to dump every page using DBCC PAGE() to see if it had a checksum value. The closest you could come in estimating this without dumping every page would be to dump just the differential change map (DCM) pages and try to estimate how much of the database had changed since the last full backup. This would still be inaccurate because the DCMs track changed extents, not pages. So an extent could be changed but have anywhere between 1 and 8 pages in the extent that have been changed.

      It may be better to figure out a way to do the index rebuild rather than trying to calculate how much of it is protected.

  7. […] their backup medium support backing up with the CHECKSUM option? This option is very important for making sure that your backups do not contain corrupted data. It […]

Leave a Reply to SQLSoldierCancel reply