SQL Server command-line utilities: sqlcmd
By Ray Rankins, Paul T. Bertucci, Chris Gallelli and Alex T. Silverstein
Microsoft SQL Server 2008 R2 Unleashed
Chapter 5: SQL Server Command-Line Utilities
SQL Server command-line utilities give database administrators a new way to access the
database engine and its components. In this three-part series from the book “Microsoft SQL Server
2008 R2 Unleashed,” we'll review each utility and its command syntax. The first is the sqlcmd
utility, which connects to SQL Server from the command prompt and executes T-SQL commands.
Table of Contents
- SQL Server command-line utilities: sqlcmd
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in December 2010
Editor's Note: Please see the chapter in PDF form for formatted
syntax conventions.
This chapter explores various command-line utilities that ship with SQL Server. These utilities
give administrators a different way to access the database engine and its related components. In
some cases, they provide functionality that is also available with SQL Server’s graphical user
interface (GUI). Other command-line utilities provide functionality that is available only from the
command prompt.
For each utility, this chapter provides the command syntax along with the most commonly used
options. For the full syntax and options available for the utility, see SQL Server Books
Online.Table 5.1 lists the command-line utilities discussed in this chapter. This table lists the
physical location of each utility’s executable. The location is needed to execute the utility in
most cases, unless the associated path has been added to the Path environmental variable.
TABLE 5.1 Command-Line Utility Installation Locations
| Utility |
Install Location |
| sqlcmd |
x:\Program Files\Microsoft SQL Server\100\Tools\Binn |
| dta |
x:\Program Files\Microsoft SQL Server\100\Tools\Binn |
| tablediff |
x:\Program Files\Microsoft SQL Server\100\COM |
| bcp |
x:\Program Files\Microsoft SQL Server\100\Tools\Binn |
| sqldiag |
x:\Program Files\Microsoft SQL Server\100\Tools\Binn |
| sqlserver |
x:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\Binn |
When you are testing many of these utilities, it is often easiest to set up a batch file (.BAT)
that contains a command to change the directory to the location shown in Table 5.1. After you make
this directory change, you can enter the command-line utility with the relevant parameters.
Finally, you should enter a PAUSE command so that you can view the output of the utility in the
command prompt window. Following is an example you can use to test the sqlcmd utility (which is
discussed in more detail later in this chapter):
CD “C:\Program Files\Microsoft SQL Server\100\Tools\Binn”
SQLCMD -S(local) -E -Q “select @@servername”
pause
After you save the commands in a file with a .BAT extension, you can simply double-click the
file to execute it. This approach is much easier than retyping the commands many times during the
testing process.
What’s New in SQL Server Command-Line Utilities
The SQL Server command-line utilities available in SQL Server 2008 are basically the same as
those offered with SQL Server 2005. This has some key benefits for those who are familiar with the
2005 utilities. Very little has changed in the syntax, and batch files or scripts you have used
with these utilities in the past should continue to work unchanged.
A few command-line utilities have been added in SQL Server 2008, however, and some have been
removed. The sqlps utility is new to SQL Server 2008. This utility can be used to run PowerShell
commands and scripts. The sqlps utility and the PowerShell Windows–based command-line management
tool are discussed in detail in Chapter 17,“Administering SQL Server 2008 with PowerShell.”
Utilities removed from SQL Server 2008 include sac. The sac utility can be used in SQL Server
2005 to import or export settings available in the graphical Surface Area Configuration (SAC) tool.
Both the sac command-line utility and SAC graphical tool have been removed. Similar functionality
is now available via policy-based management and the Configuration Manager tool.
The sqlcmd Command-Line Utility
The sqlcmd command-line utility is the next generation of the isql and osql utilities that you
may have used in prior versions of SQL Server. It provides the same type of functionality as isql
and osql, including the capability to connect to SQL Server from the command prompt and execute
T-SQL commands. The T-SQL commands can be stored in a script file, entered interactively, or
specified as command-line arguments to sqlcmd.
The syntax for sqlcmd follows:
sqlcmd
[{ { -U login_id [ -P password ] } | –E trusted connection }]
[ -z new password ] [ -Z new password and exit]
[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d
db_name ]
[ -l login time_out ] [ -A dedicated admin connection ]
[ -i input_file ] [ -o output_file ]
[ -f < codepage > | i: < codepage > [ < , o: <
codepage > ] ]
[ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ]
[ -R use client regional settings ]
[ -q “cmdline query” ] [ -Q “cmdline query” and exit ]
[ -e echo input ] [ -t query time_out ]
[ -I enable Quoted Identifiers ]
[ -v var = “value”...] [ -x disable variable substitution ]
[ -h headers ][ -s col_separator ] [ -w column_width ]
[ -W remove trailing spaces ]
[ -k [ 1 | 2 ] remove[replace] control characters ]
[ -y display_width ] [-Y display_width ]
[ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ]
[ -a packet_size ][ -c cmd_end ]
[ -L [ c ] list servers[clean output] ]
[ -p [ 1 ] print statistics[colon format]]
[ -X [ 1 ] ] disable commands, startup script, environment variables [and exit]
[ -? show syntax summary ]
The number of options available for sqlcmd is extensive, but many of the options are not
necessary for basic operations. To demonstrate the usefulness of this tool, we look at several
different examples of the sqlcmd utility, from fairly simple (using few options) to more
extensive.
Executing the sqlcmd Utility
Before we get into the examples, it is important to remember that sqlcmd can be run in several
different ways. It can be run interactively from the command prompt, from a batch file, or from a
Query Editor window in SSMS. When run interactively, the sqlcmd program name is entered at the
command prompt with the required options to connect to the database server. When the connection is
established, a numbered row is made available to enter the T-SQL commands. Multiple rows of T-SQL
can be entered in a batch; they are executed only after the GO command has been entered. Figure 5.1
shows an example with two simple SELECT statements that were executed interactively with sqlcmd.
The connection in this example was established by typing sqlcmd at the command prompt to establish
a trusted connection to the default instance of SQL Server running on the machine on which the
command prompt window is opened.
Figure 5.1 Executing sqlcmd interactively.
The capability to edit and execute sqlcmd scripts was added to SSMS with SQL Server 2005. A
sqlcmd script can be opened or created in a Query Editor window within SSMS. To edit these scripts,
you must place the editor in SQLCMD Mode. You do so by selecting Query, SQLCMD Mode or by clicking
the related toolbar button. When the editor is put in SQLCMD Mode, it provides color coding and the
capability to parse and execute the commands within the script. Figure 5.2 shows a sample sqlcmd
script opened in SSMS in a Query Editor window set to SQLCMD Mode. The shaded lines are sqlcmd
commands.
Figure 5.2 Executing and editing sqlcmd scripts in SSMS.
The most common means for executing sqlcmd utility is via a batch file. This method can provide
a great deal of automation because it allows you to execute a script or many scripts by launching a
single file. The examples shown in this section are geared toward the execution of sqlcmd in this
manner. The following simple example illustrates the execution of sqlcmd, using a trusted
connection to connect to the local database, and the execution of a simple query that is set using
the –Q option:
sqlcmd -S (local) -E -Q”select getdate()”
You can expand this example by adding an output file to store the results of the query and add
the –e option, which echoes the query that was run in the output results:
sqlcmd -S (local) -E -Q”select getdate()” -o c:\TestOutput.txt –e
The contents of the c:\TestOutput.txt file should look similar to this:
select getdate()
– – – – – – – – – – – -
2008-09-10 20:29:05.645
(1 rows affected)
Using a trusted connection is not the only way to use sqlcmd to connect to a SQL Server
instance. You can use the –U and –P command-line options to specify the SQL Server user and
password. sqlcmd also provides an option to specify the password in an environmental variable named
sqlcmdPASSWORD, which can be assigned prior to the sqlcmd execution and eliminates the need to
hard-code the password in a batch file.
sqlcmd also provides a means for establishing a dedicated administrator connection (DAC) to the
server. The DAC is typically used for troubleshooting on a server that is having problems. It
allows an administrator to get onto the server when others may not be able to. If the DAC is
enabled on the server, a connection can be established with the –A option and a query can be run,
as shown in the following example:
sqlcmd -S (local) -A -Q”select getdate()”
If you need to manage more complex T-SQL execution, it is typically easier to store the TSQL in
a separate input file. The input file can then be referenced as a sqlcmd parameter. For example,
say that you have the following T-SQL stored in a file named
C:\TestsqlcmdInput.sql:
BACKUP DATABASE Master
TO DISK = ‘c:\master.bak’
BACKUP DATABASE Model
TO DISK = ‘c:\model.bak’
BACKUP DATABASE MSDB
TO DISK = ‘c:\msdb.bak’
The sqlcmd execution, which accepts the C:\TestsqlcmdInput.sql file as input and executes the
commands within the file, looks like this:
sqlcmd -S (local) -E -i”C:\TestsqlcmdInput.sql” -o c:\TestOutput.txt –e
The execution of the preceding example backs up three of the system databases and writes the
results to the output file specified.
Using Scripting Variables with sqlcmd
sqlcmd provides a means for utilizing variables within sqlcmd input files or scripts. These
scripting variables can be assigned as sqlcmd parameters or set within the sqlcmd script. To
illustrate the use of scripting variables, let’s change our previous backup example so that the
database that will be backed up is a variable. A new input file named c:\BackupDatabase.sql should
be created, and it should contain the following command:
BACKUP DATABASE $(DatabaseToBackup)
TO DISK = ‘c:\$(DatabaseToBackup).bak’
The variable in the preceding example is named DatabaseToBackup. Scripting variables are
referenced using the $( ) designators. These variables are resolved at the time of execution, and a
simple replacement is performed. This allows variables to be specified within quotation marks, if
necessary. The –v option is used to assign a value to a variable at the command prompt, as shown in
the following example, which backs up the model database:
sqlcmd -S (local) -E -i”C:\BackupDatabase.sql” -v DatabaseToBackup = model
If multiple variables exist in the script, they can all be assigned after the –v parameter.These
variables should not be separated by a delimiter, such as a comma or semicolon. Scripting variables
can also be assigned within the script, using the :SETVAR command. The input file from the previous
backup would be modified as follows to assign the DatabaseToBackup variable within the script:
:SETVAR DatabaseToBackup Model
BACKUP DATABASE $(DatabaseToBackup)
TO DISK = ‘c:\$(DatabaseToBackup).bak’
Scripts that utilize variables, sqlcmd commands, and the many available options can be very
sophisticated and can make your administrative life easier. The examples in this section illustrate
some of the basic features of sqlcmd, including some of the features that go beyond what is
available with osql.
Download the chapter "SQL Server Command-Line Utilities" in PDF form.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation