The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
With the growing popularity of windows server in CORE mode the command line is officially back in vogue. PowerShell is getting all the press but there is a lot more to the command line than just PoSh. Long before server CORE and PoSh were grabbing headlines and blog titles – savvy DBAs were interacting with the SQL Engine at the command line with sqlcmd.
There are a variety of reasons you might do this even in a normal GUI environment. For a quick query it’s much faster and way less overhead than opening up the full blown SQL Management Studio. Further, if your system is near un-responsive and you need to use the dedicated administrative connection to access the server, sqlcmd might be one of the only ways you can get a response back. John Miner pointed out to me on twitter that sqlcmd with the windows task scheduler is a great way to get around the absence of SQL Agent in SQL Express. Finally – it’s just cool. And the ABCs of database administration requires us to A-lways B-e C-ool.
The actual sqlcmd utility is found in: c:\program files\Microsoft SQL Server\<version no>\tools\binn
In a typical installation however you can just run it from the command prompt without specifying the actual path. Once you’re connected to the server working with sqlcmd is as easy as issuing t-sql statements followed by the GO keyword to execute.
Here are a few tips for using sqlcmd effectively.
Know the basic options and behaviors for connecting
I prefer windows authentication mode on my servers so more often than not I simply type sqlcmd at the prompt and hop right in. No additional switches or options are needed. Issuing sqlcmd without additional options will connect to the default instance on the current box with integrated security (i.e. current credentials).
Often however you’ll need to specify a specific server\instance, or use a sql account so you should also know these three switches off hand.
-S specify server\instance
-U specify a user name
-P specify a password
If you specify -U but not -P it will prompt you for the password – this avoids people shoulder surfing your sensitive prod passwords.
A fourth that is also good to know
-d allows you to specify the database to use – but you can also just issue a USE statement to get what you want once connected.
Another switch that every DBA should know by heart is -A. This is the switch that instructs sqlcmd to use the dedicated admin connection. Usually if you need this switch, you really need it. You don’t want to have to be googling around for it while production is in the throws of chaos.
There are a lot of additional command switches and options but I will refer you to books online – no need to rehash them here.
Keep it simple and concise
There isn’t a lot of room in the cmd window for large outputs. Running an sp_who, select * from sys.dm_exec_requests, or Machanic’s sp_whoisactive without reducing options will overwhelm the command window with data that isn’t aligned or easy to read. For example a select from sys.dm_exec_requests looks like this in the command window of sqlcmd. Not much you can do with these query results.
As an efficient DBA you should be running very specific queries anyway, but with sqlcmd it’s even more important.
Notice in the following example I break each clause onto it’s own line. sqlcmd won’t run the query until you issue a go command so you can use as many lines as you need to make it clear. Doing so makes it easy for me to review the script before I issue the GO. Second, I’m selecting a single value from a single column. So it’s very easy to read the results when they do come back.
If you can’t be concise use an output file
Ok fine, there is a fifth command line option you should know. When you start up sqlcmd you can specify -o followed by a path to an output file. So, if you really do want to run sp_whoisactive or exec sp_who and capture all the results. Plan ahead and output everything to a file.
Notice that I issued two command above. I switched my database to adventureworks2012 with a use statement and then I ran the sp_who stored procedure. Neither command produced any output visible to the screen. It instead wrote out the results to the output file I designated in my sqlcmd session ‘queryresult.txt’.
View output files with notepad (yes, even in core)
Something you may not know… windows server core still supports notepad. It’s a watered down version but gets the job done. So from the command line (even in core) you can now view the results opening the output file with notepad.
Notepad gives you scroll options and uses a fixed width font – so everything will line up like you are used to in the SSMS window. You’ll notice that the output file captured the output’s of both batches. You’re not limited to a single query with an output file.
Running a script in a single command
Oh, wait – there is a sixth you should really know. But that is the last one – promise. You should know that -i followed by a path to a sql script file will run the content of that script. The i stands for input file. Be careful, there are no “are you sure?” prompts or anything. Once you hit enter it will run. But if you have a script prepared in a file ready to go… this is a whole lot easier than typing the whole thing out. This is also how you would use sqlcmd in conjunction with the windows task scheduler.
Interesting side note on sqlcmd script files. If you want to get fancy with scripts know that sqlcmd isn’t just limited to TSql. It has a complete set of additional syntax elements unique to the utility. These allow you to declare variables, run external command line operations, re-direct output to files, and even switch between database servers in a single script file. The same msdn documentation I keep referring you to has all the details. Also check sample usage and enabling support for this additional syntax in SSMS via sqlcmd mode.
There you have it. sqlcmd and the six command line options that you should know by heart. Uhh, seven if you count -A.
I challenge you to use sqlcmd the next time you need a quick answer from the server. I would also challenge you to practice using sqlcmd with the -A option to get used to working with the dedicated admin account and look into reasons why you might need that account. Like I said above, when you need that one – you need it now.