Microsoft® SQL Server® 2012 Bible

777

Chapter 30: Confi guring and Managing SQL Server with PowerShell

30

lays help for cmdlets on a single page.

RELATED LINKS

Get-Command

Get-PSDrive

Get-Member

REMARKS

For more information, type: "get-help Get-Help -detailed".

For technical information, type: "get-help Get-Help -full".

Creating Scripts

Although it’s sometimes useful to enter ad hoc commands into PowerShell to evaluate sys-

tem state or other information, the real power of PowerShell comes with writing scripts. A

good collection of scripts to perform normal administrative functions is a sign of an effec-

tive administrator.

For example, it’s a good idea to have information about the physical servers on which

SQL Server runs. Windows Management Instrumentation (WMI) provides this informa-

tion through simple queries, available to PowerShell through the Get-WMIObject cmdlet

(aliased as gwmi). A simple script to gather this information is shown in Listing 30-1.

In this script, four different WMI classes are polled, and their results are piped into the

select-object cmdlet (aliased as select), where the specifi c properties needed are

retrieved. Those results are then piped into the format-list cmdlet for presentation.

LISTING 30-1 Get System Info

#getsysinfo.ps1

# Use WMI queries to retrieve information about the computer, operating

# system and disk devices

gwmi -query "select * from Win32_ComputerSystem" | select Name, Model,

Manufacturer, Description, DNSHostName, Domain, DomainRole,

PartOfDomain, NumberOfProcessors, SystemType, TotalPhysicalMemory,

UserName, Workgroup | format-list

gwmi -query "select * from Win32_OperatingSystem" | select Name,

Version, FreePhysicalMemory, OSLanguage, OSProductSuite, OSType,

ServicePackMajorVersion, ServicePackMinorVersion | format-list

gwmi -query "select * from Win32_PhysicalMemory" | select Name,

Capacity, DeviceLocator, Tag | format-table -Autosize

gwmi -query "select * from Win32_LogicalDisk where

DriveType=3" | select Name, FreeSpace, Size | format-table -Autosize

c30.indd 777c30.indd 777 7/31/2012 9:46:21 AM7/31/2012 9:46:21 AM

http://www.it-ebooks.info