Five sqlcmd features to automate SQL Server database tasks

Make your life easier with the sqlcmd tool in SQL Server 2005 that automates administration and maintenance via scripting. In this tip, you'll get five useful features and examples to execute sqlcmd commands, such as for utilizing and populating variables, improved error handling and restoring a database.

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 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')
    RAISERROR(N'This script can only
    Execute on RREHAK', 16, 127)

    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

    TO DISK = 'C:\Temp\Northwind.bak'

    -- connect to the destination server
    :CONNECT rrehak

    -- if the database exists, disconnect any
    possible connections

    master.dbo.sysdatabases WHERE name =
    ALTER DATABASE Northwind

    RESTORE DATABASE [Northwind]
    FROM DISK = N'C:\Temp\Northwind.bak'
    WITH FILE = 1,
    MOVE N'Northwind' TO N'C:\SQL Server
    MOVE N'Northwind_log' TO N'C:\SQL Server

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.

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.

Next Steps

Learn how to executie a create database script

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning