Verifying Full database backups "WITH CHECKSUM"

Question

We have a daily maintenance plan in SQL Server 2005 that performs a full backup to disk using the "Back Up Database Task".

"Verify backup integrity" is enabled but from what I understand data integrity is only validated if the backup media contains a checksum, which is enabled by the "WITH CHECKSUM" option during the backup. I can see from generated T-SQL that the maintenance plan task does not create this so, apart from manually hacking the command's code, how do we specify this in the task (if it is indeed possible)?

Thanks

Answer 1

"Verify backup integrity" probably means run "RESTORE VERIFYONLY". I can't find a reference though.

If a checksum is present, RESTORE VERIFYONLY will use it. If you specify WITH CHECKSUM, it's expected. So, the BACKUP does not need WITH CHECKSUM for RESTORE VERIFYONLY to work, because you can't specify WITH CHECKSUM for RESTORE VERIFYONLY either in the plan.

Of course, this does not help in forcing "WITH CHECKSUM" on either backup or restore, but hopefully it sheds some light...

Answer 2

After further investigation it appears it is not possible to configure a task to validate the data integrity "WITH CHECKSUM" through the drag-drop features of SQL Server Management Studio alone. This command will need to be added to the T-SQL of the task