If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

As you probably know, Windows PowerShell is the new command shell and scripting language that provides a command line environment for interactive exploration and administration of computers. In addition, it provides an opportunity to script these commands so that we can schedule and run these scripts multiple times.

Windows PowerShell depends on .NET framework 2.0.

SQL Server Management Objects, known as SMO, is an object model for SQL Server and its configuration settings. SMO-based applications use .NET Framework languages to program against this in-memory object model, rather than sending Transact-SQL (T-SQL) commands to SQL Server to do so.

In this article series, I am going to illustrate the power of Windows PowerShell in conjunction with SQL Server 2005.

Part I of this series is going to illustrate how to install and use a simple PowerShell command and a simple SMO command.

Assumption

a.  The machine you use already has .NET 2.0 installed

b.  The machine you use already has SQL Server 2005 client installed with the latest service pack

Download and Install Microsoft PowerShell

a.  Download Microsoft PowerShell “WindowsXP-KB926139-x86-ENU.exe” from http://download.microsoft.com

b.  Install PowerShell

Step 1: Double click on the “WindowsXP-KB926139-x86-ENU.exe’ executable. [Refer Fig 1.0]

Fig 1.0

Step 2: Click “Run”. [Refer Fig 1.1]

Fig 1.1

Step 3: Click “Next”. [Refer Fig 1.2]

Fig 1.2

Step 4: Select the option “I agree”. [Refer Fig 1.3]

Fig 1.3

Step 5: Watch the progress of installation. Refer Fig 1.4

Fig 1.4

Step 6: Click Finish. [Refer Fig 1.5]

Fig 1.5

Launch PowerShell

There are few ways to launch PowerShell. One method is to go to the command prompt and type the following command. [Refer Fig 1.6]

PowerShell

Fig 1.6

After a short pause, the PowerShell prompt appears. [Refer Fig 1.7]

Fig 1.7

Alternatively, you can start PowerShell by selecting Programs-Windows PowerShell 1.0-Windows PowerShell. [Refer Fig 1.8]

Fig 1.8

Command Help

Inside Windows PowerShell, you can access the command list by typing the command:

Get-command

This displays all of the commands available in PowerShell. [Refer Fig 1.9]

Add-Content Add-History Add-Member Add-PSSnapin Clear-Content Clear-Item Clear-ItemProperty Clear-Variable Compare-Object ConvertFrom-SecureString Convert-Path ConvertTo-Html ConvertTo-SecureString Copy-Item Copy-ItemProperty Export-Alias Export-Clixml Export-Console Export-Csv ForEach-Object Format-Custom Format-List Format-Table Format-Wide Get-Acl Get-Alias Get-AuthenticodeSignature Get-ChildItem Get-Command Get-Content Get-Credential Get-Culture Get-Date Get-EventLog Get-ExecutionPolicy Get-Help Get-History Get-Host Get-Item Get-ItemProperty Get-Location Get-Member Get-PfxCertificate Get-Process Get-PSDrive Get-PSProvider Get-PSSnapin Get-Service Get-TraceSource Get-UICulture Get-Unique Get-Variable Get-WmiObject Group-Object Import-Alias Import-Clixml Import-Csv Invoke-Expression Invoke-History Invoke-Item Join-Path Measure-Command Measure-Object Move-Item Move-ItemProperty

New-Alias New-Item New-ItemProperty New-Object New-PSDrive New-Service New-TimeSpan New-Variable Out-Default Out-File Out-Host Out-Null Out-Printer Out-String Pop-Location Push-Location Read-Host Remove-Item Remove-ItemProperty Remove-PSDrive Remove-PSSnapin Remove-Variable Rename-Item Rename-ItemProperty Resolve-Path Restart-Service Resume-Service Select-Object Select-String Set-Acl Set-Alias Set-AuthenticodeSignature Set-Content Set-Date Set-ExecutionPolicy Set-Item Set-ItemProperty Set-Location Set-PSDebug Set-Service Set-TraceSource Set-Variable Sort-Object Split-Path Start-Service Start-Sleep Start-Transcript Stop-Process Stop-Service Stop-Transcript Suspend-Service Tee-Object Test-Path Trace-Command Update-FormatData Update-TypeData Where-Object Write-Debug Write-Error Write-Host Write-Output Write-Progress Write-Verbose Write-Warning

Fig 1.9

A simple SMO in PowerShell

Let us query the sys.sydatabases of the SQL Server instance “SQLEXPRESS” from the host machine “HOME” using PowerShell and SMO.

Step 1

Go to the command prompt. Start – run – cmd

Step 2

Start PowerShell by typing the following command.

PowerShell

Step 3

Execute the following command, one by one, as shown below.

[reflection.assembly]::LoadWithPartialName(”Microsoft.SqlServer.Smo”)
$Server = new-object (”Microsoft.SqlServer.Management.Smo.Server”) “HOME\SQLEXPRESS”
foreach($database in $Server.databases) {$database.name}

You will see the results as shown below. [Refer Fig 2.0]

Fig 2.0

SMO Members

In order to display all of the members related to the object $Server, execute the following command as shown.

Step 1

Go to the command prompt. Start – run – cmd

Step 2

Start PowerShell by typing the following command:

PowerShell

Step 3

Execute the following commands, one by one, as shown below.

[reflection.assembly]::LoadWithPartialName(”Microsoft.SqlServer.Smo”)
$Server = new-object (”Microsoft.SqlServer.Management.Smo.Server”) “HOME\SQLEXPRESS”
$server | get-member

You will see the following results. [Refer Fig 2.1]

Alter AttachDatabase CompareUrn DeleteBackupHistory Deny DetachDatabase DetachedDatabaseInfo EnumAvailableMedia EnumCollations EnumDatabaseMirrorWitnessRoles EnumDetachedDatabaseFiles EnumDetachedLogFiles EnumDirectories EnumErrorLogs EnumLocks EnumMembers EnumObjectPermissions EnumPerformanceCounters EnumProcesses EnumServerAttributes EnumServerPermissions EnumStartupProcedures EnumWindowsDomainGroups EnumWindowsGroupInfo EnumWindowsUserInfo Equals GetActiveDBConnectionCount GetDefaultInitFields GetHashCode GetPropertyNames GetSmoObject GetType get_ActiveDirectory get_BackupDevices get_Configuration get_ConnectionContext get_Credentials get_Databases get_DefaultTextMode get_Endpoints get_Events get_FullTextService get_Information get_InstanceName get_JobServer get_Languages get_LinkedServers get_Logins get_Mail get_Name get_NotificationServices get_Properties get_ProxyAccount get_ReplicationServer get_Roles get_ServiceMasterKey get_Settings

get_State get_SystemDataTypes get_SystemMessages get_Triggers get_Urn get_UserData get_UserDefinedMessages get_UserOptions Grant Initialize IsDetachedPrimaryFile IsWindowsGroupMember KillAllProcesses KillDatabase KillProcess PingSqlServerVersion ReadErrorLog Refresh Revoke SetDefaultInitFields set_DefaultTextMode set_UserData ToString ActiveDirectory BackupDevices Configuration ConnectionContext Credentials Databases DefaultTextMode Endpoints Events FullTextService Information InstanceName JobServer Languages LinkedServers Logins Mail Name NotificationServices Properties ProxyAccount ReplicationServer Roles ServiceMasterKey Settings State SystemDataTypes SystemMessages Triggers Urn UserData UserDefinedMessages UserOptions

Fig 2.1

Conclusion

As explained in the beginning of this article, this series is going to illustrate the power of Windows PowerShell in conjunction with SQL Server 2005. Part I of this series illustrated how to install and use a simple PowerShell command and a simple SMO command.

—

by Muthusamy Anantha Kumar