Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Back up Expand / Collapse
Author
Message
Posted Monday, June 10, 2013 7:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
What is the use of WITH CHECKSUM in backup statement?
Post #1461534
Posted Monday, June 10, 2013 7:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
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 !!!!!
Post #1461542
Posted Monday, June 10, 2013 7:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1461543
Posted Monday, June 10, 2013 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
Thank you
Post #1461566
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse