How to properly check for data corruption via a SQL job
I recently found out that we are not properly checking for data corruption in our "Integrity Checks" SQL job. I thought that a SQL job would fail if the job step that runs DBCC CHECKDB returned errors, but apparently that's not the case. Check out Paul Randal's blog for more information.
To properly check for data corruption via a SQL job, you should raise an error if @@ERROR does not equal zero.
In the next few days, I'll rewrite my isp_DBCC_CHECKDB stored procedure to include this. I will be testing my code change against a database that contains corruption. See this thread for more information.
UPDATE: We were unable to find a case where the job would show success, yet data corruption exists. Check out Paul's blog and the thread that I mentioned for more details.