A gem in the arsenal of command-line utilities that comes with SQL Server is the SQLMAINT application. With it, you can run a variety of maintenance activities, including DBCC checks, backup and restore, statistics updates, and more.
But here’s the best part. By passing an -HtmlRpt switch to the utility, it can automatically generate a user-friendly, Web-based report of its activities. You can use the Windows Task Scheduler to schedule a regular maintenance process, generating this report file, and you’ll never have to wonder exactly what the utility did or didn’t do. For instance, the following command-line would perform DBCC checks on the local Northwind database, reporting its results as Nwind_chk.html. Adjust the location of this file to suit your needs:
sqlmaint -S "(local)" -U "sa" -P "saPwd" -D Northwind -CkDB-CkAl -CkCat -HtmlRpt C:Nwind_chk.htmlNote: Remember to ensure that the command outputs an HTML file *in a secure location.” Otherwise, malicious users could use the report to gain knowledge about the structure and data in your database.
