Problem solve Get help with specific problems with your technologies, process and projects.

Using bcp utility for data moving, sqldiag for troubleshooting

Learn about three more command-line utilities. The bcp utility for data moving directs data into and out of SQL Server; sqldiag gathers diagnostic information and can be used in troubleshooting a problem; and sqlservr is an executable that starts SQL Server from a command prompt.

Microsoft SQL Server 2008 R2 Unleashed

Chapter 5: SQL Server Command-Line Utilities

This section covers three more command-line utilities. The bcp utility, for data moving, directs data into and out of SQL Server; sqldiag gathers diagnostic information and can be used in troubleshooting a problem; and sqlservr is an executable that starts SQL Server from the command prompt.

Table of Contents

Editor's Note: Please see the chapter in PDF form for formatted syntax conventions.

The bcp Command-Line Utility

You use the bcp (bulk copy program) tool to address the bulk movement of data. This utility is bidirectional, allowing for the movement of data into and out of a SQL Server database.

bcp uses the following syntax:

bcp {[[database_name.][owner].]{table_name | view_name} | “query”}
{in | out | queryout | format} data_file
[-mmax_errors] [-fformat_file] [-x] [-eerr_file]
[-Ffirst_row] [-Llast_row] [-bbatch_size]
[-n] [-c] [-N] [-w] [-V (60 | 65 | 70 | 80)] [-6]
[-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
[-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
[-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
[-T] [-v] [-R] [-k] [-E] [-h”hint [,...n]”]

Some of the commonly used options—other than the ones used to specify the database, such as user ID, password, and so on—are the –F and –L options. These options allow you to specify the first and last row of data to be loaded from a file, which is especially helpful in large batches. The –t option allows you to specify the field terminator that separates data elements in an ASCII file. The –E option allows you to import data into SQL Server fields that are defined with identity properties.

This section barely scratches the surface when it comes to the capabilities of bcp. For a more detailed look at bcp, refer to the section, “Using bcp” in Chapter 52, “SQL Server Integration Services.”

The sqldiag Command-Line Utility

sqldiag is a diagnostic tool that you can use to gather diagnostic information about various SQL Server services. It is intended for use by Microsoft support engineers, but you might also find the information it gathers useful in troubleshooting a problem. Sqldiag collects the information into files that are written, by default, to a folder named SQLDIAG, which is created where the file sqldiag.exe is located (for example, C:\Program Files\Microsoft SQL Server\100\Tools\binn\SQLDIAG\). The folder holds files that contain information about the machine on which SQL Server is running in addition to the following types of diagnostic information:

  • SQL Server configuration information
  • SQL Server blocking output
  • SQL Server Profiler traces
  • Windows performance logs
  • Windows event logs

The syntax for sqldiag changed quite a bit in SQL Server 2005, but very little has changed in SQL Server 2008. Some of the options that were used in versions prior to SQL Server 2005 are not compatible with the current version. The full syntax for sqldiag is as follows:

sqldiag
{ [/?] }
|
{ [/I configuration_file]
[/O output_folder_path]
[/P support_folder_path]
[/N output_folder_management_option]
[/C file_compression_type]
[/B [+]start_time]
[/E [+]stop_time]
[/A SQLdiag_application_name]
[/T { tcp [ ,port ] | np | lpc | via } ]
[/Q] [/G] [/R] [/U] [/L] [/X] }
|
{ [START | STOP | STOP_ABORT] }
|
{ [START | STOP | STOP_ABORT] /A SQLdiag_application_name }

By default, the sqldiag utility must be run by a member of the Windows Administrators group, and this user must also be a member of the sysadmin fixed SQL Server role. To get a flavor for the type of information that sqldiag outputs, open a command prompt window, change the directory to the location of the sqldiag.exe file, and type the following command:

Sqldiag

No parameters are needed to generate the output. The command prompt window scrolls status information across the screen as it collects the diagnostic information. You see the message “SQLDIAG Initialization starting...” followed by messages that indicate what information is being collected. The data collection includes a myriad of system information from MSINFO32, default traces, and SQLDumper log files. When you are ready to stop the collection, you can press Ctrl+C.

If you navigate to the sqldiag output folder, you find the files created during the collection process. In this output folder, you should find a file with MSINFO32 in its name. This file contains the same type of information that you see when you launch the System Information application from Accessories or when you run MSINFO32.EXE. This is key information about the machine on which SQL Server is running. This information includes the number of processors, the amount of memory, the amount of disk space, anda slew of other hardware and software data.

You also find a file named xxx_sp_sqldiag_Shutdown.out, where xxx is the name of the SQL Server machine. This file contains SQL Server–specific information, including the SQL Server error logs, output from several key system stored procedures, including sp_helpdb and sp_configure, and much more information related to the current state of SQL Server.

You find other files in the sqldiag output directory as well. Default trace files, log files related to the latest sqldiag execution, and a copy of the XML file containing configuration information are among them. Microsoft documentation on these files is limited, and you may find that the best way to determine what they contain is simply to open the files and review the wealth of information therein.

The sqlservr Command-Line Utility

The sqlservr executable is the program that runs when SQL Server is started. You can use the sqlservr executable to start SQL Server from a command prompt. When you do that, all the startup messages are displayed at the command prompt, and the command prompt session becomes dedicated to the execution of SQL Server.

The syntax for the sqlserver utility is as follows:

sqlservr [-sinstance_name] [-c] [-dmaster_path] [-f]
[-eerror_log_path] [-lmaster_log_path] [-m]
[-n] [-Ttrace#] [-v] [-x] [-gnumber] [-h]

Most commonly, you start SQL Server from the command prompt if you need to troubleshoot a configuration problem. The –f option starts SQL Server in minimal configuration mode. This allows you to recover from a change to a configuration setting that prevents SQL Server from starting. You can also use the –m option when you need to start SQL Server in single-user mode, such as when you need to rebuild one of the system databases.

SQL Server functions when started from the command prompt in much the same way as it does when it is started as a service. Users can connect to the server, and you can connect to the server by using SSMS. What is different is that the SQL Server instance running in the command prompt appears as if it is not running in some of the tools.

SSMS and SQL Server Service Manager show SQL Server as being stopped because they are polling the SQL Server service, which is stopped when running in the command prompt mode.

Summary

SQL Server provides a set of command-line utilities that allow you to execute some of the available SQL Server programs from the command prompt. Much of the functionality housed in these utilities is also available in graphical tools, such as SSMS. However, the capability to initiate these programs from the command prompt is invaluable in certain scenarios. Chapter 6, “SQL Server Profiler,” covers a tool that is critical for performance tuning in SQL Server 2008. SQL Server Profiler provides insight by monitoring and capturing the activity occurring on a SQL Server instance. It is a “go-to” tool for many DBAs and developers because of the wide variety of information that it can capture.

Download the chapter "SQL Server Command-Line Utilities" in PDF form.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close