Microsoft Windows Powershell and SQL Server 2005 SMO - Part 1

Use this guide to understand Microsoft Windows powershell and SQL server 2005 smo - part 1 and the factors that affect it. The information is organized to make the topic easier to apply in practice.

The MAK

How Microsoft Windows Powershell and SQL Server 2005 SMO - Part 1 Works

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