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