Use PowerShell to Work with the SQL Server Object

Doctor Scripto

October 19th, 20120 0

Summary: Microsoft Scripting Guy, Ed Wilson, talks about using Windows PowerShell to examine the SQL Server object. Microsoft Scripting Guy, Ed Wilson, is here. Today I want to continue exploring the SQL Server provider for Windows PowerShell. We will dive into the named instance node of SQL Server.

Note Yesterday I introduced the SQL Server provider for Windows PowerShell. You should definitely read Introduction to the SQL Server 2012 PowerShell Provider prior to reading today’s blog. It provides essential background information, including the installation of the optional features that are required to use the provider.

Examining the SQL Server node

Note I am using Windows PowerShell remoting to enter a remote Windows PowerShell session on my computer running SQL Server. I am doing this from my desktop. Therefore, my Windows PowerShell prompt reflects the name of the remote machine. When I connect to the specific server that is using the SQL Server 2012 provider for Windows PowerShell, the prompt changes to reflect the server name. In my case, it is SQL1. This is shown here.

[sql1]: PS SQLSERVER:sqlsql1>

Now, when I use the dir alias, I can see both instances appear as shown here.

[sql1]: PS SQLSERVER:sqlsql1> dir

Instance Name

————-

INSTANCE_A

INSTANCE_B To connect to one of the specific instances, I use the Get-Item cmdlet. Whereas this name may seem a bit strange, keep in mind that the command behind the alias dir is in fact Get-ChildItem, so the naming convention actually makes sense. I retrieve Instance_A and store it in a variable. Note Whereas tab expansion works, it works REALLY slowly when working with SQL Server 2012 provider for Windows PowerShell. Therefore, it is probably best to avoid hitting the Tab key.

[sql1]: PS SQLSERVER:sqlsql1> $instance = Get-Item Instance_A

[sql1]: PS SQLSERVER:sqlsql1> $instance

Instance Name

————-

INSTANCE_A To see the type of object that is stored in the $Instance variable, I use the GetType method. I also pipe the results to the Format-Table cmdlet so I can look at the name and the BaseType of the object. This command is shown here.

[sql1]: PS SQLSERVER:sqlsql1> $instance.GetType() | ft name, basetype -a

Name BaseType

—- ——–

Server Microsoft.SqlServer.Management.Smo.SqlSmoObject The specific SqlSmoObject is a Server object, and it contains a number of very useful properties that describe Instance_A. These properties are shown here.

[sql1]: PS SQLSERVER:sqlsql1> $instance | Get-Member -membertype property | fw -a

ActiveDirectory AffinityInfo AuditLevel

Audits AvailabilityGroups BackupDevices

BackupDirectory BrowserServiceAccount BrowserStartMode

BuildClrVersion BuildClrVersionString BuildNumber

ClusterName ClusterQuorumState ClusterQuorumType

Collation CollationID ComparisonStyle

ComputerNamePhysicalNetBIOS Configuration ConnectionContext

Credentials CryptographicProviders Databases

DefaultFile DefaultLog DefaultTextMode

DomainInstanceName DomainName Edition

Endpoints EngineEdition ErrorLogPath

Events FilestreamLevel FilestreamShareName

FullTextService HadrManagerStatus Information

InstallDataDirectory InstallSharedDirectory InstanceName

IsCaseSensitive IsClustered IsDesignMode

IsFullTextInstalled IsHadrEnabled IsSingleUser

JobServer Language Languages

LinkedServers LoginMode Logins

Mail MailProfile MasterDBLogPath

MasterDBPath MaxPrecision Name

NamedPipesEnabled NetName NumberOfLogFiles

OleDbProviderSettings OSVersion PerfMonMode

PhysicalMemory PhysicalMemoryUsageInKB Platform

Processors ProcessorUsage Product

ProductLevel Properties ProxyAccount

ResourceGovernor ResourceLastUpdateDateTime ResourceVersion

ResourceVersionString Roles RootDirectory

ServerAuditSpecifications ServerType ServiceAccount

ServiceInstanceId ServiceMasterKey ServiceName

ServiceStartMode Settings SqlCharSet

SqlCharSetName SqlDomainGroup SqlSortOrder

SqlSortOrderName State Status

SystemDataTypes SystemMessages TapeLoadWaitTime

TcpEnabled Triggers Urn

UserData UserDefinedMessages UserOptions

Version VersionMajor VersionMinor

VersionString There are actually four properties that are related to the version of SQL Server installed on the instance. The properties are Version, VersionMajor, VersionMinor, and VersionString. The best way to understand these properties is to see them in action. The following script shows the properties.

[sql1]: PS SQLSERVER:sqlsql1> $instance.version

Major Minor Build Revision

—– —– —– ——–

11 0 2100 -1

[sql1]: PS SQLSERVER:sqlsql1> $instance.versionmajor

[sql1]: PS SQLSERVER:sqlsql1> $instance.versionminor

[sql1]: PS SQLSERVER:sqlsql1> $instance.versionstring

11.0.2100.60 Remember, this is Windows PowerShell, and therefore wildcard characters work. So, an easier way to examine the different version properties is to pipe the $instance variable and use Format-List to show the version properties. This is shown here.

[sql1]: PS SQLSERVER:sqlsql1> $instance | fl version*

VersionMajor : 11

VersionMinor : 0

VersionString : 11.0.2100.60

Version : 11.0.2100 I can also easily find information about the edition of SQL Server 2012 that is running, in addition to the login accounts that are set up. This is shown here.

[sql1]: PS SQLSERVER:sqlsql1> $instance.Edition

Enterprise Edition (64-bit)

[sql1]: PS SQLSERVER:sqlsql1> $instance.EngineEdition

EnterpriseOrDeveloper

[sql1]: PS SQLSERVER:sqlsql1> $instance.logins

Name Login Type Created

—- ———- ——-

##MS_PolicyEventProcessingLogin## SqlLogin 2/10/2012 9:07 PM

##MS_PolicyTsqlExecutionLogin## SqlLogin 2/10/2012 9:07 PM

NT AUTHORITYSYSTEM WindowsUser 10/1/2012 9:47 AM

NT ServiceMSSQL$INSTANCE_A WindowsUser 10/1/2012 9:47 AM

NT SERVICEReportServer$INSTANCE_A WindowsUser 10/1/2012 9:47 AM

NT SERVICESQLAgent$INSTANCE_A WindowsUser 10/1/2012 9:47 AM

NT SERVICESQLWriter WindowsUser 10/1/2012 9:47 AM

NT SERVICEWinmgmt WindowsUser 10/1/2012 9:47 AM

NWTRADERSadministrator WindowsUser 10/1/2012 9:47 AM

sa SqlLogin 4/8/2003 9:10 AM I might also be interested in account information, such as the service account, the service start mode, and the name of the service. This information is shown here.

[sql1]: PS SQLSERVER:sqlsql1> $instance.ServiceAccount

NT ServiceMSSQL$INSTANCE_A

[sql1]: PS SQLSERVER:sqlsql1> $instance.ServiceStartMode

Auto

[sql1]: PS SQLSERVER:sqlsql1> $instance.Servicename

INSTANCE_A I can use this same technique to see all the directory locations for my instance of SQL Server 2012 as shown here.

[sql1]: PS SQLSERVER:sqlsql1> $instance | fl *dir*

BackupDirectory : C:Program FilesMicrosoft SQL

ServerMSSQL11.INSTANCE_AMSSQLBackup

InstallDataDirectory : C:Program FilesMicrosoft SQL

ServerMSSQL11.INSTANCE_AMSSQL

InstallSharedDirectory : C:Program FilesMicrosoft SQL

ServerMSSQL11.INSTANCE_AMSSQL

RootDirectory : C:Program FilesMicrosoft SQL

ServerMSSQL11.INSTANCE_AMSSQL

ActiveDirectory :

Server object methods

There are also a number of methods that are exposed by the Server object.

[sql1]: PS SQLSERVER:sqlsql1> $instance | gm -mem method | fw -a

Alter AttachDatabase

CompareUrn DeleteBackupHistory

Deny DesignModeInitialize

DetachDatabase DetachedDatabaseInfo

Discover EnumActiveCurrentSessionTraceFlags

EnumActiveGlobalTraceFlags EnumAvailableMedia

EnumClusterMembersState EnumClusterSubnets

EnumCollations EnumDatabaseMirrorWitnessRoles

EnumDetachedDatabaseFiles EnumDetachedLogFiles

EnumDirectories EnumErrorLogs

EnumLocks EnumMembers

EnumObjectPermissions EnumPerformanceCounters

EnumProcesses EnumServerAttributes

EnumServerPermissions EnumStartupProcedures

EnumWindowsDomainGroups EnumWindowsGroupInfo

EnumWindowsUserInfo Equals

GetActiveDBConnectionCount GetConnection

GetDefaultInitFields GetDomainRoot

GetHashCode GetLogicalVersion

GetParent GetPropertyNames

GetPropertySet GetPropertyType

GetPropertyValue GetSmoObject

GetStringComparer GetType

GetUrn Grant

Initialize IsDetachedPrimaryFile

IsWindowsGroupMember JoinAvailabilityGroup

KillAllProcesses KillDatabase

KillProcess PingSqlServerVersion

ReadErrorLog Refresh

Resolve Revoke

Script SetConnection

SetDefaultInitFields SetObjectState

SetPropertyValue SetTraceFlag

SfcHelper_GetDataTable SfcHelper_GetSmoObject

SfcHelper_GetSmoObjectQuery ToString

Validate To call the methods, I need to know the requirements for the method. I can find some of the information via Get-Member, but other information will need to come from TechNet in the description of the methods of the Server object. To call the GetDirectories method, I need to supply the directory for the installation of SQL Server as shown here.

[sql1]: PS SQLSERVER:sqlsql1> $instance.EnumDirectories(“C:Program FilesMicrosoft SQL Server”)

Name

—-

100

110

MSAS11.INSTANCE_A

MSAS11.INSTANCE_B

MSRS11.INSTANCE_A

MSRS11.INSTANCE_B

MSSQL11.INSTANCE_A

MSSQL11.INSTANCE_B I do not have to type a path—I can use one of the properties from the Server object. In this example, I enumerate the directories beneath the root of the SQL Server instance.

[sql1]: PS SQLSERVER:sqlsql1> $instance.EnumDirectories($instance.RootDirectory)

Name

—-

Backup

Binn

DATA

FTData

Install

JOBS

Log

repldata Join me tomorrow when I will talk about more cool Windows PowerShell and SQL Server stuff as I move to the database node. I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace. Ed Wilson, Microsoft Scripting Guy

Use PowerShell to Work with the SQL Server Object

Examining the SQL Server node

Server object methods

Doctor Scripto Scripter, PowerShell, vbScript, BAT, CMD

0 comments

Use PowerShell to Work with the SQL Server Object

Examining the SQL Server node

Server object methods

Doctor Scripto Scripter, PowerShell, vbScript, BAT, CMD

Read next

0 comments