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