Back up

  • What is the use of WITH CHECKSUM in backup statement?

  • Result found from Goggle - http://sqlmag.com/blog/does-using-checksum-ensure-successful-backup

    When the WITH CHECKSUM option is used for a backup, the backup process will test the page checksums that exist on the data file pages being backed up. If a bad page checksum is found, the backup process will stop (by default – you can override this using WITH CONTINUE_AFTER_ERROR but I’d only do that in an emergency). So if the backup completes successfully, you know that there are no broken page checksums.

    But does that mean there are no corruptions? Absolutely not.

    Firstly, if the database was upgraded from a version where page checksums weren’t available or if page checksums were not enabled for some reason, then it’s possible that not all pages in the database actually have a page checksum (as a page checksum is only put on a data file page after page checksums are enabled and then the page is read into memory, altered, and written back out to disk). This means that using WITH CHECKSUM is not testing the I/O subsystem integrity of the entire data file – only those pages with page checksums.

    Secondly, a page checksum being valid doesn’t guarantee the page is not corrupt – it just guarantees that the I/O subsystem hasn’t corrupted the page since it was written to disk. A page could have been damaged in memory, and then written to disk with a valid page checksum.

    To summarize: using the WITH CHECKSUM option and having the backup complete successfully tells you that those pages with page checksums had not been corrupted by the I/O subsystem at the time the backup was taken.

    The only way to know that the database does not have corruptions in it is to run DBCC CHECKDB. I like to advise people to restore the backup on another server and then run DBCC CHECKDB on the restored copy of the database. If that DBCC CHECKDB comes back clean, you know that the backup contains a corruption-free database.

    One thing to bear in mind though is that just because the backup completed successfully and you ascertain that it doesn’t have corruption in, that doesn’t mean that the backup will remain corruption free. An I/O subsystem is just as likely to corrupt a backup file as anything else – which is why regular testing of restore sequence is necessary – especially if backups are moved on and off of tape media

    ************************************
    Every Dog has a Tail !!!!! :-D

  • techmarimuthu (6/10/2013)


    What is the use of WITH CHECKSUM in backup statement?

    Please check this... http://msdn.microsoft.com/en-us/library/ms189055.aspx#BckChecksums

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply