SQLCMD Utility – a Useful Tool in Your Toolbelt

One of the coolest things about DC Comics’ Batman was that he had this utility belt (http://en.wikipedia.org/wiki/Batman’s_utility_belt) that enabled him to pretty much do anything.  There was no problem or situation that he wasn’t prepared for.

As IT professionals, and especially DBA’s, we want to be like Batman and be prepared for any situation that arises.  In our figurative toolbelt, we may have self-created scripts, consultants code (i.e. sp_whoisactive, diagnostic DMV’s, sp_blitz, etc.), and even vendor tools (i.e. monitoring software, toolkits, etc.).  But, one thing we don’t want to forget is some of the utilities that are packaged with SQL Server, such as the SQLCMD utility (http://technet.microsoft.com/en-us/library/ms162773.aspx), which can help us in certain situations.

The SQLCMD utility is the replacement for osql.exe and it uses ODBC when it is used from the command line.  In this post, I am going to discuss some of the situations where using SQLCMD as a command line utility can be helpful.  Granted, many of the things I am going to mention can be done a different way (i.e. using PowerShell), but I wanted to display the usage of SQLCMD utility.

Configuring servers after a new installation
After a new SQL Server installation, there are probably post-installation procedures that need to be configured (i.e. sp_configure options, database mail, alerts and operators, adjusting system databases such as tempdb and model, adding maintenance scripts, etc.).

Imagine having all these scripts sitting in a directory, and running the SQLCMD utility to apply them against your newly built server and receiving the output.  The following code shows a very basic way that this could be done:

sqlcmd -S [ComputerName]\[InstanceName] -I [SQLConfigXX.sql] -o [SQLConfigXX_log.txt]

Deploying upgrades to databases
It is probably easy to see how the SQLCMD utility could be used to deploy T-SQL changes to individual databases.  Depending on how you may receive deployment scripts from your development team, the SQLCMD utility could run these scripts and generate an output file.

In my multi-tenant database environment, we use the SQLCMD utility to deploy code releases to thousands of production databases.  We have a process that builds the list of databases that need a particular script or set of scripts applied, and then we use the SQLCMD utility to deploy to all of them.  Afterwards, we have another process that checks the output files for errors.

sqlcmd -S [ComputerName]\[InstanceName] -d [DatabaseName] -I [DBUpgradeXX.sql] -o [DBUpgradeXX_log.txt]

Scan network for SQL Servers
Recently, I also found out that the SQLCMD utility had the ability to scan the network for SQL Servers, which is really handy.  In the past, I have used Microsoft’s Assessment and Planning (MAP) Toolkit (http://technet.microsoft.com/en-us/solutionaccelerators/dd537572) or Idera’s SQL Admin Toolset (http://www.idera.com/productssolutions/sqlserver/sqladmintoolset), which has a SQL Discovery tool.  But, this just continues to show the versatility of the SQLCMD utility.  Here is the basic syntax of how to scan the network:

sqlcmd -L

Now, this isn’t intended to be an exhaustive list of the ways you can use the SQLCMD utility, but rather just a few examples to show how it can be a useful tool in your toolbelt.

This entry was posted in DBA and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s