Command Line Utilities in SQL Server 2005

Microsoft has always been the best among the database vendors when it comes to providing useful GUI utilities to work on and manage the RDBMS. In addition, there are a lot of very handy command line utilities as well that are available in SQL Server.

In SQL Server 2000, these are some of the important command line utilities: bcp, osql, isql, dtsrun, dtsrunui, sqlmaint, sqlftwiz, sqldiag, replication utilities, odbcping, readpipe, itwiz, dtswiz, sqldumper etc.. There are a lot of others as well like the ones used for replication, rebuildm, regxmlss, sqladhlp, svrnetcn, TextCopy and vswitch. In SQL Server 2005, additional ones were introduced: like SQLCMD.exe, SQLWB.exe, Profiler90.EXE, DTA.exe (replacement for itwiz.exe), DTExec.exe, DTExecUI.exe, DTUtil.exe (NOTE: This list does not include the Report Server command line utilities like the RS.exe utility that can be used to deploy reports).

So, out of the names mentioned above, how many have you used? 🙂 Chances are that you might not have used many of them since some are actually called by the GUI interface as well. Is it really useful to know any of these? IMO, one should know some of the important ones since that way you can use them to schedule your tasks and be more productive. SQLCMD.exe, RS.exe, DTA.exe, bcp, sqldiag, profiler90.exe are some that we have used in our projects on an as needed basis. Let’s pick one and see what we can do with it.

DTA.exe: This is the database tuning advisor. You can either operate it through the GUI or you can right up your own script to use the EXE and then schedule the tasks to analyze performance and provide you recommendations regarding the potential improvements like adding/removing/modifying indexes, adding partitions, indexed views etc.. Connection setting information remains the same as with other command line utilities in SQL Server and you can refer BOL for the complete list or when using these utilities, you can get more information by typing:

H:\>dta.exe /?
Microsoft (R) SQL Server Microsoft SQL Server Database Engine Tuning Advisor command line utility
Version 9.00.3042.00
Copyright (c) Microsoft Corporation. All rights reserved.

This will be followed by the different command line parameters and their meaning. We would encourage you to look at these command line utilities so that you can use them to automate some of your tasks as well as use them to help you troubleshoot issues in a more productive fashion (like performance issues that arise at 2:00 A.M. in the morning that might need you to scheduled some automated task to collect samples for you to analyze later).