Five sqlcmd features to automate SQL Server database tasks
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
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
Dig Deeper
-
People who read this also read...
-
This was first published in February 2008
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:
- 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 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"
- 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"
- 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
- 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.
- 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:
- -- 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: |
|
|
|
|
|
|
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.
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