SQL Server 2008 R2 sqlcmd Examples
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 G O command has been entered. Figure
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.
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 T-SQL 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: \ Test sqlcmd Input. sql:
BACKUP DATABASE Master
TO DISK = 'c:\master.bak'
BACKUP DATABASE Model
TO DISK = 'c:\model.bak1
BACKUP DATABASE MSDB
TO DISK = 1
c:\msdb.bak1
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.