Powershell vs Sqlcmd

Question

Powershell vs Sqlcmd

jts2013

Hall of Fame

Points: 3226
More actions
March 12, 2010 at 6:51 am

#217814

Hi there,
I'm wondering if anyone can explain whether I should be using Powershell instead of Sqlcmd, or as well as, which is best for certain situations and what the main differences are between them.
Currently, I'm looking to widen my SQL knowledge and Sqlcmd looked a logical next step (I've already managed to get it to perform some usual admin tasks) - but then Powershell came along!
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

sqlcmd is a command line utility for firing sql commands off within SQL, and so can be incorporated into a .bat file.

powershell can be used to run SQL commands but can do a whole lot more, it was actually designed more for windows admins as a shell scripting language.

If you just want to run simple SQL commands against a single server, sqlcmd should be fine, if its getting more complex and say involves multiple servers, powershell is the way to go.

SQLcmd is easy to use. Powershell needs learning, but its definitely worth knowing.

---------------------------------------------------------------------

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

simply sead :

Powershell is the new vbscript.

SQLCMD is only intended for usage related to sqlserver.

So .... two different worlds with a bit of overlap.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gary Varga SSC Guru Points: 82166 More actions · Answer 3

PowerShell all the way. It will give you so much more. MS is betting the house on it like it did with .NET. I reckon that all new admin tools will be based on it.

SQLCMD will live forever for backwards compatibility though.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

I may as well pile on here since we clearly don't have a consensus.

Either will work.

Either will work with multiple servers, multiple databases, etc.

However, PowerShell offers much more flexibility and power (ha) than sqlcmd. It can do everything that sqlcmd can do and then more. BUT, powershell is only available in SQL Server 2008 and the newer OS's. To have it available for 2005 & 2000 (which it works with, just fine) you either have to have those installed on new servers or you'll need to install PowerShell. It's something to keep in mind before you dedicate yourself to using this tool as your primary command-line operating method.

My recommendation, go for PowerShell. It offers more and with Microsoft's huge push to use PowerShell in all it's various server applications, you'll be well served by learning it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

steveray SSC Journeyman Points: 83 More actions · Answer 5

george sibbald (3/12/2010)
sqlcmd is a command line utility for firing sql commands off within SQL, and so can be incorporated into a .bat file.
powershell can be used to run SQL commands but can do a whole lot more, it was actually designed more for windows admins as a shell scripting language.
If you just want to run simple SQL commands against a single server, sqlcmd should be fine, if its getting more complex and say involves multiple servers, powershell is the way to go.
SQLcmd is easy to use. Powershell needs learning, but its definitely worth knowing.

Gazareth One Orange Chip Points: 27737 More actions · Answer 6

I frequently call sqlcmd (Invoke-SqlCmd) from within powershell scripts, so... both?

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 7

I'm in the both camp. I use SQLCMD to do lots of simple stuff, it's easy to work with and I'm comfortable.

However, when I want to do more scripting that's programming, that requires me to process results and do more than run a query, PoSh is what I turn to. I do struggle with it a bit still, but the new SqlServer module the dbatools are winning me over.

It does take time to get used to the syntax and structure. The $_.property threw me for ahwile, and -eq instead of = still gets me. I'm getting better and part of that is mucking around on some project every week.

Gazareth One Orange Chip Points: 27737 More actions · Answer 8

Steve Jones - SSC Editor (1/10/2017)
-eq instead of = still gets me

Yep, catch myself out with that one all the time (and -gt instead of > etc. etc...)

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 9

Gazareth - Monday, January 9, 2017 11:55 AM
I frequently call sqlcmd (Invoke-SqlCmd) from within powershell scripts, so... both?

That is NOT the same. Calling "sqlcmd.exe" from within PowerShell will give you different output than calling the same with "Invoke-SqlCmd". Just look at the type you get with each.

If I run this in PowerShell I will get just a basic System.String type:

$results = sqlcmd -S myserver -Q "SELECT name FROM sys.databases"
$results.GetType()

If you run this in PowerShell you will get a more robust object, System.Array, that you can manipulate row-by-row utilizing the power in PowerShell:

$results = Invoke-Sqlcmd -ServerInstance myserver -Query "SELECT name FROM sys.databases"
$results.GetType()

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Gazareth One Orange Chip Points: 27737 More actions · Answer 10

Shawn Melton - Sunday, January 15, 2017 11:28 PM
Gazareth - Monday, January 9, 2017 11:55 AM
I frequently call sqlcmd (Invoke-SqlCmd) from within powershell scripts, so... both?
That is NOT the same. Calling "sqlcmd.exe" from within PowerShell will give you different output than calling the same with "Invoke-SqlCmd". Just look at the type you get with each.
If I run this in PowerShell I will get just a basic System.String type:
$results = sqlcmd -S myserver -Q "SELECT name FROM sys.databases"
$results.GetType()

If you run this in PowerShell you will get a more robust object, System.Array, that you can manipulate row-by-row utilizing the power in PowerShell:
$results = Invoke-Sqlcmd -ServerInstance myserver -Query "SELECT name FROM sys.databases"
$results.GetType()

Oh, I'm aware they're different (which is why included the cmdlet name); I was more trying to get at that they're complimentary rather than competing tools.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

I'm in the "usually avoid both" camp, although there's always the rare exception. In many cases that I've seen, people are simply using PowerShell to do things that should actually be done in SQL Server. In fact and as some have already pointed out, many have taken to writing PowerShell to run SQLCmd instead of building a stored procedure.

Sure, there's some huge utility in both PowerShell and SQLCMD but they shouldn't be used as a replacement for stored procedures nor should they be used to build things like centralized backups, as seems to be all the rage. Just imagine what will happen to the log files on all of the remote systems if that centralized back system goes offline or fails. If that happens, I hope you have an excellent script to properly shrink and resize all of your transaction log files on those other systems. 😉

Just as people say about SQL, so it is true with PowerShell. "Just because you can do something in PowerShell, doesn't mean you should."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 12

I hate centralized systems. That creates a single point of failure that depends on lots of stuff (Security, networking, etc.) working correctly.

I do think PoSh works well for backups, since I can more easily move files to places I want them, rename them, find the latest one, etc. easily. However, I would script this is the same process on every instance, letting each one manage itself. I don't know that this is easier in PoSh than T-SQL, but it's not really harder with new cmdlets, and the file work is easier.<

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

Steve Jones - SSC Editor - Tuesday, January 17, 2017 9:40 AM
I hate centralized systems. That creates a single point of failure that depends on lots of stuff (Security, networking, etc.) working correctly.
I do think PoSh works well for backups, since I can more easily move files to places I want them, rename them, find the latest one, etc. easily. However, I would script this is the same process on every instance, letting each one manage itself. I don't know that this is easier in PoSh than T-SQL, but it's not really harder with new cmdlets, and the file work is easier.<

So, again... how do you schedule it to run and from where? Can this type of thing easily be done using some Posh task in SQL Agent?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 14

Jeff Moden - Tuesday, January 17, 2017 9:57 AM
So, again... how do you schedule it to run and from where? Can this type of thing easily be done using some Posh task in SQL Agent?

I agree with what you are saying and I don't do backups using Posh but I do manage the backup files with it (i.e. deleting the ones xx days old) since it is a really simple, easy to understand kind of script. .
Job steps have Powershell for the type now. I think it was added in 2008, not sure on that.
There are certainly issues with what can be done on what version of Powershell, what version OS/SQL you are running so I'm not sure it's as straightforward as is sometimes indicated. If a company hasn't really adopted Powershell, I would have concerns about using it as it seems every DBA can (or should) be able to troubleshoot a t-sql script. I don't think the same can be said for a Powershell script. Just my take.

Sue