Connecting to SQL Server and running query

We have a small requirement, connect to SQL Server while in powershell and run a query. This can be done using below code

# Declare a table to hold the returned data
$out_table = new-object System.Data.DataTable
# Declare a connection to connect to sql server
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=BOLT\BOLT_TST2;Initial Catalog=master;Integrated Security=True")
# Declare an Adapter to connect and run the query
$qr = new-object System.Data.SqlClient.SqlDataAdapter("Select * from sysobjects where xtype='U'",$cn)
# fill the adapter with result
$qr.Fill($out_table)
# print the result
write-output $out_table | Format-Table


It's quite straightforward, you get the result in your powershell window.
Please note that this is not connecting using SMO (as discussed in other posts of this blog).

Best Powershell Resources

Starting learning Powershell for regular DBA activities is a clever and on time decision. I know it will take sometime to make it workable for you (as for me too), but we can speed up this process with help of some good online resources.

One resource is this blog (;) - I am not kidding, I mean it :))

I have hand picked few resources for you, which I am using right now.

1. Free ebook on Powershell
http://powershell.com/cs/blogs/ebook/

This ebook is not only free its one of the best you can get. You can download it or read online.

2. SQL Tips on Powershell
http://www.mssqltips.com/category.asp?catid=81

MSSQLTIPS.com is one more resource to get hands on it. You have option to discuss topics with there author.

3. Online forum for Powershell
http://powershellcommunity.org/Forums/tabid/54/Default.aspx

You can check regular forum topics on powershell on this forum. There is a separate SQL Powershell category also. I tried this forum last week and I got my answer in less than an hour (impressive !!). Read others problem and try to learn from others mistake (;)).

4. Microsoft learning, tip of the week
http://www.microsoft.com/technet/scriptcenter/resources/pstips/default.mspx

This lists some very clever tricks for Powershell. Just read few, this will help in leaning.

5. GUI Tool for scripting - PowerGUI
http://powergui.org/index.jspa

You need a good editor for scripting and executing Powershell. PowerGui is the answer, a intellitext (drop down like Visual Studio), one in all scripting editor for Powershell.
Coloring is also very important especially if you learning. You can catch errors better with coloring, a black and white screen or notepad plain text is hard to focus on errors, but here with good coloring, you can develop fast.

Powershell : SQL Server SMO

Now, as you have decided to use Powershell for your daily SQL Server admin activities, lets get down and start working.

In powershell you can connect to -

• SQL Server using connection
• SQL Server SMO

If you have to run queries just like you do inside SQL Server, you can use sql server connection and then run your queries, but this is again going back to our old DOS Batch + SQL concept, which I feel is no new for SQL DBAs. Using SMO class and its objects is certainly something more fascinating.

SQL Server SMO (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx) is best way to get information about SQL Server, this is our very own management studio right here in Powershell. You can get property, call some method or catch some events.

First thing is to declare your SMO Object and initialize your SQL Server in it -

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\BOLT_TST2"

Just take the above statement as it is, only thing you have to remember -
$srv : is the variable for your SQL Server
"LOCALHOST\BOLT_TST2" : is the server name of your choice, I mean, SQL Server you want to connect (for localhost you can also use a dot '.')

Once these two lines you have copied and pasted on your powershell window, you can use $srv for all queries.

Lets see some use of it.

1. Database property
Get database list with their properties

$srv.databases | Format-Table name, id, Owner, size, DataSpaceUsage

I got following output from my server

Name ID Owner Size DataSpaceUsage
---- -- ----- ---- --------------
AdventureWorks 6 BOLT\Administrator 165.9375 101288
AdventureWorksDW 5 BOLT\Administrator 70.5 43560
master 1 sa 4.5 1056
model 3 sa 1.6875 472
msdb 4 sa 5.1875 3008
tempdb 2 sa 8.5 488

You can see following properties also

• name
• Collation
• CompatibilityLevel
• CreateDate
• DataSpaceUsage
• FileGroups
• ID
• LastBackupDate
• LastLogBackupDate
• Name
• Owner
• ReadOnly
• RecoveryModel
• Size
• SpaceAvailable
• State

I know your skeptic mind is thinking "HOW I WILL GET THE WHOLE LIST OF PROPERTIES!!?"
Its easy, just run below command and whole list for properties, methods and events for Databases object will come

$srv.Databases | Get-Member


2. Get server info

We can see server info by running following command

$srv | Format-Table name, collation, Edition, Productlevel

For me below is output

Name Collation Edition ProductLevel
---- --------- ------- ------------
LOCALHOST\BOLT_TST2 SQL_Latin1_General_CP1_CI_AS Enterprise Edition RTM

As I told already, you can get the whole list of properties, methods and event for this object using Get-Member verb-noun set.




Further reading :

http://www.mssqltips.com/tip.asp?tip=1798


Using Powershell for SQL Server 2005 Using Powershell Powershell for SQL DBA List of databases using Powershell Listing server property in Powershell Powershell SQL SMO Using SMO in SQL Server SQL Server 2000 SQL Server 2005 SQL Server 2008 Tips for SQL Server DBA