From MSDN
CHECKSUM Specifies that the backup operation will verify each page for
checksum and torn page, if enabled and available, and generate a
checksum for the entire backup. This is the default behavior for a
compressed backup.
All the verify backup option does is confirm that the header of your backup is readable, nothing else.
Neither of these options will tell you that you have a good backup! The only way to confirm that your backup is good is to restore it on another system
Am I wrong? Is it minimal risk to turn this off, if I am backing up to disk and not to streaming tape or something?
No, you are right :-)
RESTORE VERIFYONLY only will not ensure that you will be able to recover your database in the event of corruption. By nature, it will not perform any integrity checks.
A better way will be to periodically take your backups and do a valid restore on a different server and perform DBCC CHECKDB on it.
This is one reason, why I am not a big fan of maintenance plans since the GUI does not expose a lot of options like backup .. with CHECKSUM that can be achieved by T-SQL.
From Paul Randal's Myth blog
24p) using RESTORE … WITH VERIFYONLY validates the entire backup
No. Using VERIFYONLY only validates the backup header looks like a backup header. It's only when you take the backup using WITH CHECKSUM and do RESTORE … WITH VERIFYONLY and using WITH CHECKSUM that the restore does more extensive checks, including the checksum over the entire backup.
Do you run "verify" on every backup in your environment? Do you spot-check them?
I dont run the VERIFYONLY. Instead I take backup with CHECKSUM and then have them restored + CHECKDB'ed on a different server. You can follow
Statistical Sampling for Verifying Database Backups approach if you want to be creative.
This is not the same as enabling the "checksum" option on backup (which can't be done in the wizard, as far as I know).
You can enable Trace Flag 3023 so that the CHECKSUM option is automatically enabled for the BACKUP command. As always, test the behavior of any trace flags in your environment !
The bottom line is - ditch maintenance plans and use a more sensible backup solution (hint: Ola's backup solution) that will allow you to customize it based on your needs.
(We back up over the network to an EMC DD-800 backup appliance, if that is relevant.)
Backup up locally to the disk and then have a PowerShell transfer job that will copy the backup locally from the server to a network share (backup server). That will be more faster than to directly copy to a network share.
Also, enable instant file initialization, that will help with auto-growths on data files as well as will help cutdown the restore time (incase if you have to restore your databases). Its always good to have options handy.
A good read will be : Backups: Planning a Recovery Strategy
Best Answer
This gives you a little bit of extra protection for your database backups, since the backup operation verifies each page for checksum and torn page, and generates a checksum for the entire backup. This is not a substitute for actually restoring you database backups on a regular basis, but it is a useful extra step to help ensure that your backups are actually good.
CHECKSUMs does not add too much CPU load but doing a VERIFYONLY does. There is no impact on vss snapshot creation.