Friday 16 July 2010

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: ,,,

No comments: