Get-SQLServer2.ps1

1. #Requires -Version 3.0 
2. <# 
3. .SYNOPSIS 
4.     This script Gets a list of SQL Severs on the Subnet 
5. .DESCRIPTION 
6.     This script uses SMO to Find all the local SQL Servers  
7.     and displays them 
8.  
9. .NOTES 
10.     File Name  : Get-SQLServer2.ps1 
11.     Author     : Thomas Lee - tfl@psp.co.uk 
12.     Requires   : PowerShell Version 3.0 
13. .LINK 
14.     This script posted to: 
15.         http://www.pshscripts.blogspot.com 
16. .EXAMPLE 
17.     PS>  # On a Lync Server looking at Lync Implementation 
18.     PS>  Get-SQLServer2 
19.     There are 7 SQL Server(s) on the Local Subnet 
20.  
21.     ServerName      InstanceName Version      
22.     ----------      ------------ -------      
23.     2013-LYNC-MGT   MON          10.50.2500.0 
24.     2013-LYNC-MGT   SCOM         10.50.2500.0 
25.     2013-TS         RTCLOCAL     11.0.2100.60 
26.     2013-SHAREPOINT SPSDB        11.0.3000.0  
27.     2013-LYNC-FE    RTC          11.0.2100.60 
28.     2013-LYNC-FE    RTCLOCAL     11.0.2100.60 
29.     2013-LYNC-FE    LYNCLOCAL    11.0.2100.60 
30.      
31. #> 
33. Import-Module SQLPS 
34.  
35. # Now get all the database servers on the local subnet 
36.  
37. $SQLservers = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() 
38. $Srvs= @() 
39.  
40. # Convert collection to an array 
41. Foreach ($srv in $SQLservers) { 
42. $srvs += $srv 
43. } 
44.  
45. # Now display results 
46. If ($Srvs.count -LE 0) { 
47. "There are no SQL Servers on the Local Subnet" 
48. return
49. } 
50.  
51. # Now print server details 
52. "There are {0} SQL Server(s) on the Local Subnet" -f $Srvs.count 
53. $Srvs | Select ServerName, InstanceName, Version | Format-Table -AutoSize 

Technorati Tags: SQL Server 2012,SQL Server 2012 SMOs

Set-SQLServerOption.ps1

1. <# 
2. .SYNOPSIS 
3.     This script sets options on a SQL Server using SMO 
4. .DESCRIPTION 
5.     This script first loads the SQL cmdlet and provider snapin and 
6.     displays information about the SQL Server. The script then sets 
7.     two server options and alters to database to persist the changes. 
8. .NOTES 
9.     File Name  : Set-SQLServerOption.ps1 
10.     Author     : Thomas Lee - tfl@psp.co.uk 
11.     Requires   : PowerShell Version 2.0 
12. .LINK 
13.     This script posted to: 
14.         http://www.pshscripts.blogspot.com 
15. .EXAMPLE 
16.     PSH:[C:\Foo]: .\Set-SQLServerOption.ps1 
17.     Network Name   SQL1 
18.     Instance Name   
19.     OS Version     6.1 (7600) 
20.     SQL Edition    Enterprise Edition (64-bit) 
21.     Settings State Existing 
22. #> 
23.  
24. # Load the SMO Objects 
25. $null = Add-PSSnapIn SqlServerCmdletSnapin100   -erroraction silentlycontinue 
26. $null = Add-PSSnapIn SqlServerProviderSnapin100 -erroraction silentlycontinue 
27.  
28. # Set the path context to the local, default instance of SQL Server. 
29. CD sqlserver:\sql\localhost\ 
30. $srv = get-item default 
31.  
32. # Display information about the instance of SQL Server and settings state 
33. "Network Name   {0}"  -f $srv.NetName 
34. "Instance Name  {0}"  -f $srv.InstanceName 
35. "OS Version     {0}"  -f $srv.Information.OSVersion 
36. "SQL Edition    {0}"  -f $srv.Edition 
37. "Settings State {0}"  -f $srv.Settings.State.ToString() 
38.  
39. # Modify LoginMode settings 
40. $srv.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Integrated 
41.  
42. # Modify settings specific to the current connection in UserOptions 
43. $srv.UserOptions.AbortOnArithmeticErrors = $true 
44.  
45. # Run the Alter method to make the changes on the instance of SQL Server 
46. $srv.Alter() 

Technorati Tags: PowerShell,script,SQL,SMO

Get-SQLServer.ps1

Microsoft.SqlServer.Management.Smo.Server

1. <# 
2. .SYNOPSIS 
3.     This script uses SQL Server Server SMO objects to display server information. 
4. .DESCRIPTION 
5.     This script first loads the SMO assembly and then creates a server object. 
6.     The script then prints basic server information plus details of databases and 
7.     tables. 
8. .NOTES 
9.     File Name  : Get-SQLServer.ps1 
10.     Author     : Thomas Lee - tfl@psp.co.uk 
11.     Requires   : PowerShell Version 2.0 
12. .LINK 
13.     This script posted to: 
14.        http://pshscripts.blogspot.com/2010/05/get-sqlserverps1.html 
15.     MSDN Sample posted at: 
16.        http://msdn.microsoft.com/en-us/library/microsrosoft.management.smo.server.aspx 
17. .EXAMPLE 
18.     PS C:\Foo> .\Get-SQLServer.ps1' 
19.     Server Details 
20.     -------------- 
21.     Server Name:      SQL1 
22.     Product:          Microsoft SQL Server 
23.     Edition:          Enterprise Edition (64-bit) 
24.     Type:             Singleton 
25.     Version:          10.0.2531 
26.     Version String:   10.0.2531.0 
27.     Service Account:  .\sql 
28.   
29.     Databases and Table 
30.     ------------------- 
31.     master (contains 6 tables) 
32.     model (contains 0 tables) 
33.     msdb (contains 102 tables) 
34.     PSMC (contains 1 tables) 
35.     tempdb (contains 0 tables) 
36. #>  li class="alt">## 
37. # Start of Script 
38. ## 
39.  
40. # Load the SMO Assembly 
41. $null = [system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") 
42.   
43. # Create Server Object 
44. $server = New-Object Microsoft.SqlServer.Management.Smo.Server "SQL1" 
45.   
46. # Display key properties  
47. "Server Details" 
48. "--------------" 
49. "Server Name:      {0}" -f $Server.Netname 
50. "Product:          {0}" -f $Server.Product 
51. "Edition:          {0}" -f $Server.Edition 
52. "Type:             {0}" -f $Server.ServerType 
53. "Version:          {0}" -f $Server.Version 
54. "Version String:   {0}" -f $Server.Versionstring 
55. "Service Account:  {0}" -f $Server.ServiceAccount 
56. "" 
57.   
58. # Display Database/Table info 
59. "Databases and Table" 
60. "-------------------" 
61. foreach ($database in $server.Databases) { 
62. "{0} (contains {1} tables)" -f $Database.name, $Database.Tables.Count 
63. } 

Technorati Tags: PowerShell,Code,Scripts,SQL,MIcrosoft.SQLServer.Management.SMO.Server

Get-SQLServerVersion.ps1

1. <# 
2. .SYNOPSIS 
3.     This script displays the versions of SQL Server running on a system 
4. .DESCRIPTION 
5.     This script uses WMI to get the SQLServiceAdvancedProperty class from 
6.     the ComputerManagement namespace to print out the versions. This  
7.     script is an adaptation of the VBS script on MSDN. 
8. .NOTES 
9.     File Name  : Get-SQLServerVersion.ps1 
10.     Author     : Thomas Lee - tfl@psp.co.uk 
11.     Requires   : PowerShell V2 CTP3 
12. .LINK 
13.     This script posted to: 
14.         http://www.pshscripts.blogspot.com 
15.     MSDN Sample posted at: 
16.         http://msdn.microsoft.com/en-us/library/ms186353.aspx 
17. .EXAMPLE 
18.     PSH [C:\foo]: .\Get-SQLVersion.ps1' 
19.     You are running the following versions of SQL: 
20.     Service Name            Version 
21.     ------------            ------- 
22.     MSSQL$MICROSOFT##SSEE   9.3.4035.00 
23.     MSSQL$SQLEXPRESS        9.3.4035.00 
24. #> 
25.  
26. ## 
27. # Start of script 
28. ## 
29.  
30. # Get the versions of SQL from WMI 
31. $Versions = Get-WmiObject -Namespace root\Microsoft\SQLServer\computerManagement -Class SqlServiceAdvancedProperty | where {$_.SqlServiceType -eq 1 -and $_.PropertyName -eq "VERSION"} 
32.  
33. # Now display results 
34. "You are running the following versions of SQL:" 
35. "Service Name            Version" 
36. "------------            -------" 
37. foreach ($version in $versions) { 
38. "{0} `t{1}" -f $version.servicename,$version.propertystrvalue 
39. } 
40. #End of script 

Technorati Tags: SQL,PowerShell,Code,SMO