June 17, 2010 at 3:28 am
We are in the process of migrate our SQL Server 2K to a new box with SQL Server 2008.
We intend to move our database to the new box/sql instance 2008 with restore.
We know that the option "Page Verify" remain the same when we restore our db to the new SQL instance.
But we also know that there is a new database option, which is "checksum".
First question:
Do we need to modify this option which is set to "none" or "Torn_Page_Detection" to "checksum"?
Second question:
If we modify our databases with the "checksum" option, do we also need to execute the backup with the error management option "with checksum"?
Can you please explain what is the best or recommended approach?
Thank you very much for your time.
Kind regards.
June 17, 2010 at 3:35 am
First question:
Do we need to modify this option which is set to "none" or "Torn_Page_Detection" to "checksum"?
Answer: Yes
Second question:
If we modify our databases with the "checksum" option, do we also need to execute the backup with the error management option "with checksum"?
Answer: Not necessarily. Database checksum and backup checksum are different things.
June 17, 2010 at 3:41 am
beppe_30 (6/17/2010)
Do we need to modify this option which is set to "none" or "Torn_Page_Detection" to "checksum"?
You should. Checksum is recommended, it detects more IO corruptions than torn page did.
If we modify our databases with the "checksum" option, do we also need to execute the backup with the error management option "with checksum"?
You don't have to, but running the backup with checksum gives you a few things.
The backup checks all the page checksums as it backs the database up and will fail if it encounters a page with an invalid checksum, hence it won't backup a corrupt database
The backup process writes a checksum over the backup file, so if anything damages any portion of the backup file it will be detected if you do a restore verify only (without the backup checksum the restore verifyonly just checks headers)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2010 at 1:18 am
Suresh and GilaMonster, thank you very much for your reply.:cool:
April 4, 2011 at 9:19 am
Do you have to do anything special after changing the page verify setting to checksum in order for it to be functional. Thanks.
April 4, 2011 at 9:21 am
Yes. Modify every single page in the database. The checksum only gets added on the next modification.
Easiest way to change most pages - rebuild all indexes on all tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2011 at 10:01 am
GilaMonster (6/17/2010)
The backup process writes a checksum over the backup file, so if anything damages any portion of the backup file it will be detected if you do a restore verify only
Not 'any portion'. Damage to parts of the the MTF blocks can go undetected with the restore verifyonly option, resulting in a backup file that gets verified successfully, but cannot be restored.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
April 4, 2011 at 12:43 pm
Does anyone know how to force a 2005 or 2008 maintenance plan backup to use the WITH CHECKSUM option of the backup command?
I have not been able to find the option, but maybe it is well hidden somewhere.
Since the BACKUP WITH CHECKSUM option has been available since 2005, I would at least expect to see it available in SSMS 2008.
November 18, 2011 at 12:22 pm
For Michael Valentine Jones - Did you ever find an answer to this? How can I add the CHECKSUM option to the existing Maintenance Plan backup job?
Thanks
November 18, 2011 at 1:19 pm
You can't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply