• Login
• Become a member
• RSS
• Part of the TechTarget network

Sqlcmd is one of the command line utilities included with SQL Server 2005. It was designed for executing SQL scripts and replaces the osql utility that came with previous versions of SQL Server. In addition to executing SQL code, it also executes so-called "sqlcmd extensions." These extensions provide a lot of added functionality that go beyond plain SQL, making sqlcmd a useful tool for automating SQL Server database administration and maintenance via scripting. This article shows you the basics of using sqlcmd and suggests some practical ways to use it for SQL Server administration and maintenance.

Start sqlcmd from the command prompt window in the interactive mode. While you could execute SQL queries that you type in the window and see the results, you can pretty much achieve the same thing much more easily by running queries in SQL Server Management Studio in sqlcmd mode. The real power of sqlcmd lies in its ability to easily automate the execution of saved SQL scripts with options for controlling where and how the script is executed -- you control that via passed-in parameters or sqlcmd variables.

Here is a list of the most useful features in sqlcmd:

1. Executing script files. You can execute script files in sqlcmd in two ways. If you are in DOS, you can invoke sqlcmd and use the –i option to pass in the name of the file you want to execute:

   sqlcmd -i"c:\sql\MySql.sql"

   Note: Double quotes

1. are needed only if the path to the file contains any spaces, but I tend to use them all the time for the sake of consistency.

   The second option is to invoke another SQL file from a file that is being executed. This allows you to create two or more layers of script files, where at the top you control the flow – which files to execute and which parameters to pass in when they are executed. The following script will execute MySql.sql from inside of a SQL file running in sqlcmd mode:

   :r "c:\sql\MySql.sql"

2. Using and populating variables. This is, in my opinion, the most useful feature addition if you compare sqlcmd in SQL Server 2005 with osql in previous SQL Server versions. You can declare variables inside of sqlcmd scripts using the :setvar directive and then use them later in the code by enclosing the variable name with the $() placeholder. Sqlcmd will replace the placeholder with the value of the referenced variable:

   :setvar hello "Hello World"
PRINT '$(hello)'

   One very useful behavior is, if you declare and populate a variable, the execution context includes any script files invoked by the file where the variable is declared. This allows you to pass variables from the parent script file to child script files. In the following example, the hello variable will be available for use inside of the invoked MySql2.sql file:

   :setvar hello "Hello World"
:r "c:\sql\MySql2.sql"

   If calling sqlcmd from DOS, you can pass in variables and populate them using the –v command line option:

   sqlcmd -i"c:\sql\MySql2.sql" -
vhello="Hello World"

3. Connecting to multiple servers. When starting sqlcmd, you have to connect to a defined server to execute your code. But then in SQL, you can change the connection to a different server and any subsequent scripts will be executed on that server. You connect to another server by using the :connect directive:

   :connect MyServer2
SELECT * FROM sys.databases – returns
databases from MyServer2

4. Improved error handling. Sqlcmd gives you many options for handling situations where something doesn't go the way you expected it to. When you combine this feature with the ability to use sqlcmd variables, you get a lot of flexibility for protecting yourself from the unexpected. For example, let's say you want to make sure that a certain script file only executes on a particular database server and you want to enforce it in code. This example uses the ":on error exit" directive to specify that the script execution should stop if the SQL Server instance name is not RREHAK:

   :on error exit
IF (@@servername != 'RREHAK')
BEGIN
RAISERROR(N'This script can only
Execute on RREHAK', 16, 127)
END

   Any scripts that follow the code above would not get executed.

5. Backing up and restoring a database. Now I'll show you one example of a useful sqlcmd script. I frequently create scripts that back up a database on one server and restore it on another server. With sqlcmd you can achieve the whole thing in a single script file because you can connect to both source and destination server. The following script backs up the Northwind database on one server and restores it on another server:
6. -- connect to the source server
   :CONNECT rrehak\sql2000

   BACKUP DATABASE Northwind
   TO DISK = 'C:\Temp\Northwind.bak'
   WITH INIT
   GO

   -- connect to the destination server
   :CONNECT rrehak

   -- if the database exists, disconnect any
   possible connections
   IF EXISTS (SELECT * FROM
   master.dbo.sysdatabases WHERE name =
   'Northwind')
   ALTER DATABASE Northwind
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   GO

   RESTORE DATABASE [Northwind]
   FROM DISK = N'C:\Temp\Northwind.bak'
   WITH FILE = 1,
   MOVE N'Northwind' TO N'C:\SQL Server
   Databases\Northwind.mdf',
   MOVE N'Northwind_log' TO N'C:\SQL Server
   Databases\Northwind_1.ldf',
   NOUNLOAD, STATS = 10
   GO

More on increasing SQL Server performance: FAQ: SQL Server query errors and explanations Restore basics: How to restore using T-SQL commands T-SQL commands vs. XML AUTO in SQL Server

 Here's one more improvement to the script above, which demonstrates how to execute operating system commands from sqlcmd. The demo script restored the database from the location where the source database was backed up. Let's say you want to copy a backup file to a folder on the destination server because you'll need to restore the file repeatedly and want to avoid restoring over the network. Sqlcmd can execute DOS commands using the :!! directive. In this case, place the following script after the backup section and before the restore section to execute the DOS copy command for creating a copy of the backup file:

:setvar CopyCommand "copy
\ \sqlserver1\c$\Temp\Northwind.bak
\ \sqlserver2\c$\sql"
:!! $(CopyCommand)

All of these features make sqlcmd the tool of choice for automating SQL Server administration and maintenance tasks. For example, you can create a "worker" script file that contains a common set of SQL scripts that need to execute in multiple databases, possibly on multiple servers as well. Inside of that script you would use sqlcmd variables.

This article provided an overview and a few examples of using the sqlcmd utility. You can read more about sqlcmd in Microsoft Books Online and start building your own collection of sqlcmd script files that automate your repetitive tasks and make you a more productive DBA.

ABOUT THE AUTHOR
Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.

Join the conversationComment

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.