Using SQLMAINT.EXE for Database Maintenance

Using SQLMAINT.EXE for Database Maintenance

The SQLMAINT utility is used to perform database maintenance activities from the command line. Database backups and certain DBCC statements can be specified to run. The Database Maintenance Plan Wizard actually uses a subset of SQLMAINT options, calling them through the xp_sqlmaint extended-stored procedure.

Why would you want to run T-SQL statements from the command line when SQL Server has its own built-in job and scheduling functions? One advantage of SQLMAINT is its ability to generate a report of its activities and save it to a text or HTML file, or e-mail it to an operator. SQLMAINT can also group several operations into one statement; if this statement is saved as a script, it could then be copied and easily modified to run on other servers. The following shows some examples of the SQLMAINT command.

This example runs DBCC CHECKDB on the Pubs database and creates a report called Pubs.rpt.

 sqlmaint -S Server1 -U "sa" -P "password"  -D Pubs -CkDB  -Rpt J:\reports\Pubs.rpt 

Backups can also be run, as in the following example, which backs up the Pubs database to the default backup directory.

 sqlmaint -S Server1 -U "sa" -P "password"  -D Pubs -BkUpDB -BkUpMedia DISK -UseDefDir