Running SQL Server commands from Powershell?

Question

I'm writing a Powershell script to automate something with SQL Server, and part of that requires adding my local account to the database as an admin.

create login [MYDOMAIN\myusername] from windows
go

sp_addsrvrolemember [MYDOMAIN\myusername], 'sysadmin'
go

This needs to be run through an elevated sqlcmd command.

I'm new to both Powershell and SQL Server, so thank you for your help.

Cookie Monster · Accepted Answer · 2014-09-30 21:52:29Z

There are a few popular TSQL options from PowerShell

Invoke-SqlCmd2
- Download and dot source the function
- + Flexible, portable TQSL function based on .NET in ~400 lines
- + Simple parameterized queries
- + Optional efficient conversion of DBNull to Null (data bahaves as expected in PowerShell)
- - Not an official Cmdlet from Microsoft
- - Does not support 'Go' statement

Invoke-SqlCmd
- Download and install the latest SQL Feature pack (generally backwards compatible). In particular:
  - SQLSysClrTypes
  - Shared ManagementObjects
  - PowerShellTools
- Alternatively, install Management Tools along with SQL
- + Official Cmdlet from Microsoft
- - No support for parameterized queries (or terrible wonky - is 'Variable' parameter for this?)
- - Known to be buggy. As of SQL 2014 CTP there were still bugs being addressed

.NET - System.Data.SqlClient
- Search MSDN for various classes. Examples abound on this site.
  - System.Data.SqlClient.SQLConnection
  - System.Data.SqlClient.SqlCommand
  - System.Data.DataSet
  - System.Data.SqlClient.SqlDataAdapter
- + Portable
- + Powerful
- - Not as simple as a Cmdlet or function
- - Readers may find your code less clear
- - While helpful if you have specific niche needs, re-usable code like Invoke-SqlCmd2 would be more efficient

sqlcmd.exe
- If you really want to call it, you can call it from PowerShell. You might find --% helpful...

Example use with Invoke-SqlCmd2 (generally my go command for TSQL):

Invoke-Sqlcmd2 -ServerInstance SomeInstance -Database SomeDatabase -Query "create login [MYDOMAIN\myusername] from windows;"

On a side note, be sure to check out the SQL SMO, and SQLPSX:

The SQL SMO is fantastic for automation and control of SQL, although you would need to be comfortable with using .NET. There are many resources available, just google around for what you want to do, and tack on 'SQL SMO PowerShell'
SQLPSX generally uses .NET and the SMO libraries, providing more user friendly functions that behave like Cmdlets. Unfortunately, this appears to be a dead project, and some of the material is dated. Here's a good post on getting started by one of the contributors.

Good luck!

Rahul · Accepted Answer · 2014-09-30 19:20:28Z

0

You are looking for Invoke-Sqlcmd cmdlet.

Save your SQL Server command (below ones) in a file and save it as SQLInput.sql

create login [MYDOMAIN\myusername] from windows
go

sp_addsrvrolemember [MYDOMAIN\myusername], 'sysadmin'
go

Then, You can run PowerShell command prompt with Run As Administrator and run the below command like:

Invoke-Sqlcmd -InputFile "C:\SQLInput.sql" | Out-File -filePath "C:\SQLOutput.rpt"

EDIT:

You can use -Query option to run the query inline like

Invoke-Sqlcmd -Query "create login [MYDOMAIN\myusername] from windows;"

Catching response, is to make sure whether Query ran successfully or not. You can use -AbortOnError switch; which will abort the command on error.

Else, you can use Try .. Catch .. Finally construct in PowerShell like

Try
 {
   Invoke-Sqlcmd -Query "create login [MYDOMAIN\myusername] from windows;"
 }

Catch
 {
  [system.exception]
  "caught a system exception"
 }

Finally
 {
  "end of script"
 }

Read more about exception handling in Powershell Here

edited Sep 30, 2014 at 19:20

answered Sep 30, 2014 at 18:41

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

2 Comments

Benjin Over a year ago

Is there a good way to do this without a separate file? The script's purpose is to assist with onboarding, so I'd like to keep everything as "in one place" as possible. Bonus points for being able to read SQL Server's responses to the command.

Rahul Over a year ago

See edited answer. Read more in MSDN to find out more.

Collectives™ on Stack Overflow

Running SQL Server commands from Powershell?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related