Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1

The MAK

As you probably know, Windows PowerShell is a new command-line utility and a scripting language that provides a command-line environment for computer interaction and administration surveys. In addition, it provides a scripting capability for these commands so that we can plan and run them 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 is set. The SMO platform application uses .NET Framework languages ​​to program against the memory object model.

In this series, I will demonstrate the power of Windows PowerShell in collaboration with SQL Server 2005. Specifically in this Part I will show you how to set up and use a simple PowerShell and SMO command.

Hypothesis

a. The computer has .NET 2.0 installed

b. The computer has installed the latest version of SQL Server 2005 service pack

Download and install Microsoft PowerShell

a. Download the Microsoft PowerShell ' WindowsXP-KB926139-x86-ENU.exe ' installation file from http://download.microsoft.com

b. Install PowerShell on your computer

Step 1 : Double-click the executable file ' WindowsXP-KB926139-x86-ENU.exe '. [Figure 1.0]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 1
Figure 1

Step 2 : Click ' Run '. [Figure 1.1]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 2
Figure 2

Step 3 : Click ' Next '. [Figure 1.2]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 3
Figure 3

Step 4 : Select the ' I agree ' option. [Figure 1.3]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 4
Figure 4

Step 5 : Wait for the installation process. [Figure 1.4]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 5
Figure 5

Step 6 : Click Finish . [Figure 1.5]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 6
Figure 6

Run PowerShell

There are several ways to run PowerShell. One method is to find the command prompt and enter the PowerShell command. [Figure 1.6]

PowerShell

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 7
Figure 7

The PowerShell command appears. [Figure 1.7]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 8
Figure 8

Alternatively, you can run PowerShell by selecting Programs - Windows PowerShell 1.0 - Windows PowerShell . [Figure 1.8]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 9
Figure 9

Help command

In PowerShell windows, you can access the list of commands by entering commands

Get-command

This command displays all the commands available in PowerShell. [Figure 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

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 10
Figure 10

A simple SMO in PowerShell

Query the sys.sydatabases of SQL Server instances 'SQLEXPRESS' from the 'HOME' server using PowerShell and SMO.

Step 1 : Go to the command prompt : Start - run - cmd

Step 2 : Start PowerShell by entering the command

PowerShell

Step 3: Execute the commands, step by step, as follows

[reflection.assembly] :: LoadWithPartialName ("Microsoft.SqlServer.Smo")
$ Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "HOMESQLEXPRESS"
foreach ($ database in $ Server.databases) {$ database.name}

You will get the result as shown below. [Figure 2.0]

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 11
Figure 11

SMO components

To display all components related to the $ Server object, execute the command below.

Step 1 : Go to the command prompt : Start - run - cmd

Step 2 : Start PowerShell by entering the command

PowerShell

Step 3 : Execute the commands, step by step, as follows

[reflection.assembly] :: LoadWithPartialName ("Microsoft.SqlServer.Smo")
$ Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "HOMESQLEXPRESS"
$ server | get-member

You will get the result as shown below. [Figure 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

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 12
Figure 12

Conclude

As explained in the beginning of this article, this series will illustrate the power of Windows PowerShell in collaboration with SQL Server 2005. In this series, I will demonstrate the power of Windows PowerShell in collaboration with SQL Server 2005. Part I of This series has shown how to set up and use a simple PowerShell and SMO command.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 13 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 14 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 15 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 4
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 16 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 17 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 18 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 19 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 20 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 21 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Picture 22 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11