After reading your recent 'Importance of Validating Backups' and now this article on DBCC CHECKSUM, I'm very curious to know how much database corruption is really encountered in the real world. I deal with a few client production databases and in the course of 10 years or so I have never run into this corruption issue - thank heaven.

So to all the SQL Server Central community out there, to whom has this ever really happend!?

And again, after reading your recent 'Importance of Validating Backups' I decided to get my hands a little dirty and automate this DBCC CHECKSUM on a production database. It's not so simpe. But here is my solution. It consistes of check1.sql which generates check.log which is read by check2.sql and sends an email when something went wrong. Has anyone done anything to automate DBCC CHECKSUM that's a bit more clever?

-- does a dbcc checkdb on a database

-- this should be run via sqlcmd with -o parameter sending ouput to file check.log;

-- this file then gets read by check2.sql

declare @errnum int

declare @errdesc varchar(255)

set @errnum=0

set @errdesc=''

begin try

dbcc checkdb('MYDATABASE')

set @errnum=@@error

set @errdesc=error_message()

end try

begin catch

set @errnum=@@error

set @errdesc=error_message()

end catch

if @errnum <> 0 begin

print 'CHECKDB ERROR: ' + convert(varchar,@errnum) + ' ' + @errdesc

end

-- loads check.log generated by check1.sql into a table and

-- checks if there are errors

set nocount on

-- U = table V = view P = stored procedure FN = scalar function TF = table-valued function

if object_id('tempdb..#sqlcmdlog','U') is not null drop table #sqlcmdlog

create table #sqlcmdlog(line varchar(8000))

-- load log file into a one-column table

bulk insert #sqlcmdlog

from 'c:\temp\check.log'

with (rowterminator = '')

go

-- check if there were errors

declare @count1 int

declare @count2 int

select @count1=count(*)

from #sqlcmdlog

where line like 'CHECKDB found 0 allocation errors and 0 consistency errors%'

select @count2=count(*)

from #sqlcmdlog

where line like 'CHECKDB ERRORS%'

if @count1<>1 or @count2>0 begin

-- you should send an email here with file check.log as an attachment

select line

from #sqlcmdlog

where line like 'CHECKDB found % allocation errors and % consistency errors%'

or line like 'CHECKDB ERROR%'

end

The rownterminator value doesn't seem to appear. It should be a backslash followed by the letter 'n'.