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]
Figure 1
Step 2 : Click ' Run '. [Figure 1.1]
Figure 2
Step 3 : Click ' Next '. [Figure 1.2]
Figure 3
Step 4 : Select the ' I agree ' option. [Figure 1.3]
Figure 4
Step 5 : Wait for the installation process. [Figure 1.4]
Figure 5
Step 6 : Click Finish . [Figure 1.5]
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
Figure 7
The PowerShell command appears. [Figure 1.7]
Figure 8
Alternatively, you can run PowerShell by selecting Programs - Windows PowerShell 1.0 - Windows PowerShell . [Figure 1.8]
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
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]
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
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 2
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 4
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11
You should read it
- Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4
- 10 tips with PowerShell in Windows Server 2008 - Part 1
- Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7
- Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2
- Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11
- 10 tips with PowerShell in Windows Server 2008 - Part 2
- Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8
- About PowerShell
May be interested
- Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10in part 10, i will show you how to use powershell scripts in conjunction with smo and parameters to create sql server scripts. creating sql server scripts is an important task for administrators and sql server database development professionals.
- 10 tips with PowerShell in Windows Server 2008 - Part 1in fact, there are a lot of windows server 2008 tasks that we can do a lot faster with powershell than the gui-based application or tool. in the following article, we will introduce you some basic and most frequently used operations with powershell ...
- Test SQL Server with Windows PowerShell - Part 5in this part 5, we will check whether we can connect to sql server and see if we can query some properties related to sql server.
- Test SQL Server with Windows PowerShell - Part 6part 6 will show you how to check all existing databases in the sql server instance and query the database properties.
- Instructions on how to use PowerShell in Windows Server 2012what is powershell? windows powershell is a command-line shell language interpreter and scripting language specifically designed for system administrators. built on the .net framework, windows powershell helps it professionals control and automate windows operating system administration tasks as well as applications running on windows server environments.
- Test SQL Server with Windows PowerShell - Part 1instead of using vbscript, bat files, or sql client implementations, ... we'll show you the power of windows powershell in checking sql servers status.
- Test SQL Server with Windows PowerShell - Part 3in part 3, i will show you how to find some hardware and operating system information from the host machine.
- Next time, Microsoft will release PowerShell updates via Windows Update Windowsmicrosoft is making it easier to update powershell on windows 10 and windows server devices by releasing updates via microsoft update in the future.
- Test SQL Server with Windows PowerShell - Part 4in this next section, i will show you the information about network card and hard drive from the server.
- Test SQL Server with Windows PowerShell - Part 7in this section, i will show you how to get that information in top 10 queries based on cpu performance.