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.log