SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Back up


Back up

Author
Message
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 36
What is the use of WITH CHECKSUM in backup statement?
OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)

Group: General Forum Members
Points: 742 Visits: 692
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13985 Visits: 4639
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.
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 36
Thank you
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search