Centralize SQL Server maintenance with native tool in my goal. Scripts are saved in a database table, exported to a .bat in the file system and executed from a SQL Server Agent job.
The basic statement in the command batch file looks like this:
sqlcmd -E -S ServerName -Q “SET NOCOUNT ON; DBCC CHECKDB( 7) WITH NO_INFOMSGS ”I build the statement using the server database ID rather than database name. There are no naming convention rules like “database names have no spaces” or “do not begin with a number” for systems not created by me. Just easier to reference databases from numbers than names in the Dynamic SQL code within the stored procedures than handling complex database names.
I want to have a .log file error message result output. I can then add a step to my job that looks for error message files and forward the file in email to me as notification of errors.
sqlcmd -E -S ServerName -Q “SET NOCOUNT ON; DBCC CHECKDB( 7) WITH ALL_ERRORMSGS, NO_INFOMSGS ” -o \\Lgc004\SQLBackups\Process\DbccCheckDb\Error\LG0113DBCCCheckDB.logPaul Randal is a guide:
http://www.sqlskills.com/blogs/paul/how-to-get-all-the-corruption-messages-from-checkdb/