SQL Server ships with an array of powerful graphical management tools, but it also features many lesser-known command-line utilities that let you use Windows NT shell scripts or VBScript to manage SQL Server. Here are my picks for the seven most overlooked SQL Server command-line utilities, all of which are available in both SQL Server 2000 and 7.0.
7. Net Start and Net Stop MSSQLServer
The net start and net stop commands let you start and stop the SQL Server service from a command line. You can add these commands to your shell script-based backup utilities to automatically shut down and restart SQL Server.
6. Bulk Copy Program
Bulk copy program (bcp) is SQL Server's original data import and export tool. Although I haven't used bcp since Microsoft introduced Data Transformation Services (DTS) with SQL Server 7.0, bcp is still useful for continuing to run existing data transfers.
5. ODBCCMPT
The odbccmpt command sets the ODBC compatibility level for a named application. Odbccmpt is particularly useful when you're using a 3.x version of the SQL Server ODBC driver and connecting to an instance of SQL Server 2000 or 7.0 from an application written to the 2.x version of the ODBC driver. For SQL Server 6.x compatibility, you can use odbccmpt with the /v:6 switch. The command's syntax is
odbccmpt file_name /v:version_number
4. ITWIZ
The itwiz command-line utility lets you run SQL Server's Index Tuning Wizard from the command line.
3. ISQL and OSQL
The isql and osql utilities let you execute single SQL statements as well as SQL batches stored in script files. However, isql uses the limited DB-Library to connect to SQL Server, whereas osql uses ODBC. Here's the osql syntax you would use to run a single query and output the results to a file:
osql /S server_name /U user /P password /Q query /o output_file
2. DTSWIZ
The dtswiz utility starts the DTS Import/Export Wizard from an NT shell script or a VBScript file. Command-line switches let you bypass several wizard dialog boxes by passing the needed information from the command line.
1. DTSRUN
The dtsrun command-line utility executes existing DTS packages. Using dtsrun with SQL Server's scheduled jobs feature is a great way to regularly execute DTS packages. You can store the DTS packages in SQL Server's msdb database, a structured COM file, or the Microsoft repository. To execute a DTS package saved in msdb, use the following syntax:
dtsrun /S server_name /U user
/P password /N package_name