PowerShell操作SQLServer

自己使用Powershell的目的是为了更方便的管理与SQLServer相关，所以就顺便发到SQLServer版本，希望对大家有所启发。Powershell是微软开发的windows平台上的新脚本语言，目的在于实现类似于bash等方便管理windows系统的功能，为系统管理员带来更大的方便。在实际应用中自己也尝试着去学习和应用powershell，经过一段时间的摸索，发现其功能确实强大，能够在windows平台上灵活的管理文件系统、注册表、进程、服务、SQLServer、exchange等，而且容易上手，简化了很多管理的方式。一、Powershell的资源网上有很多Powershll的优秀资源，顺便跟大家分享下1，微软的Powershll官方网站要想了解一项新技术，最好的资源莫过于官方网站了，有一些非常好的入门资料，值得一看。2，powershell.com这是idera公司维护的一个关于powershell的学习网站，里面有很多非常好的实践和问题解决方案，能够提供很好的启发。3，simple-talk网站=powershellsimple-talk是redgate维护的一个知识网站，里面网罗了很多数据库和其他技术的大师（如JoeCelko），是一个非常好的知识库。最早引起自己对Powershell兴趣的就是看了上面地址的一系列利用powershell管理SQLServer的文章。4,电子书首推下述两本1，《Manning.Power.Shell.in.Practice.Jun.2010》从书名也可以看出具有很强的实践性2，《OReilly.Windows.PowerShell.Cookbook.Oct.2007》OReilly出这种语言书也很具含金量不过都是英文版。二、注意事项Powershell目前已经发展到了V2版本，在安装SQLServer2008时，会自动安装V1，但有很大的功能限制，为了获得更好的功能，尽量考虑使用V2。下载地址为：三、Powershell和SQLServer在使用SQLServer2008时，我们会发现自动安装了POWERSHELL。说明POWERSHELL已经和SQLServer进行了很好的融合，要通过powershell来管理SQLServer主要是通过直接操作SMO(SQLServerMnagementObject)来实现。但平时应用的不多，所以未对其过多深入，有兴趣的朋友可以参考下联机丛书四、自己的Powershell实践像我在管理SQLServer时，最经常遇到的需求是：1，如何获得windows的事件查看器里的警告和错误信息，并发送警告，2，如何获得SQLServer相关服务的运行状态，并发送警告；3，如何获得SQLServer服务器的空间信息，并发送警告；4，如何移动历史备份文件到新的目录，防止做磁带备份时产生冗余。当然，上述内容，通过SQLServer编写T-SQL应该也能实现，但相对而言会复杂很多，而通过POWERSHELL的话，有些甚至只需要几行语句就可以了。1，如何获得windows的事件查看器里的警告和错误信息，并发送警告，Pythoncode#Createdat2010-08-23#Createdbyobuntu##创建获取服务器错误和警告信息的函数#在POWERSHELL里，Get-EventLog还可以管理远程服务器，如果有多台服务器的话，会更方便管理functionget-sqlEventLog{#参数为服务器名，后续调用函数时，输入服务器名就可以Param([String]$serverName)#EntryType用来筛选错误和警告信息#APPLICATION指的是应用程序的信息，其他的还有SYSTEM,SECURITY#利用get-date获取最近1个小时的错误和警告信息Get-EventLogApplication-ComputerName$serverName-EntryType'Error','Warning'-After((get-date).addhours(-1))|Format-List}$headline=(Get-Date).toString()列'FOR列名/*将你要设置为默认值对象的列*/--用命令创建CHECK约束定义某字段.USE销售管理系统--引入数据库ALTERTABLE销售表--修改表ADDCONSTRAINTck_sl--创建检查约束CHECK(数量=1and数量=10000)--添加约束条件--用命令创建规则对象，并与表中字段绑定.--创建规则使用CREATERULE语句创建CREATERULERULE_AGE--创建规则AS@AGE!40--规则条件不能大于40CREATERULElist_rule--创建规则AS@listIN('1001','1002','1003')--规则条件只能输入1001、1002、1003CREATERULEpattern_rule--创建规则AS@valueLIKE'__-%[0-9]'--规则条件在任意两个字符的后面跟一个连字符和任意多个字符，并以0到9之间的整数结尾--创建好一个规则后，必须使用绑定才能够使用规则，一般情况下，规则可以绑定在用户自定义数据类型或是数据列中。下面可以使用SQLServer中的系统存储过程sp_bindrule将规则绑定在数据表中。EXECsp_bindrule'RULE_AGE','操作员信息表.操作员年龄'--用命令创建用户自定义数据类型，并用该数据类型定义表中字段.--在“student”数据库中，创建用来存储邮政编码信息的“postalcode”用户定义数据类型。--SQL语句如下：USEstudentEXECsp_addtypepostalcode，'char(8)','notnull'2，如何获得SQLServer相关服务的运行状态，并发送警告；Pythoncode#Createdat2010-08-23#Createdbyobuntu#在-match后面可以利用正则表达式来筛选自己想关注的服务状态#在-computername后面指定多台服务器$message=Get-Service-computername192.168.2.1,192.168.3.1,192.168.4.1|Where-Object{$_.Name-match\bmsdtc\b|\bmssqlserver\b|\bsqlwriter\b|\bmsdtsserver100\b|\bsqlserveragent\b}`|sortmachinename|format-table-Propertymachinename,name,status,displayname-auto|Out-String#如果有服务停止，立即发送邮件if($message-matchstopped){$emailFrom=[e-mail]$emailTo=[e-mail]$subject=服务停止警告$body=有服务发生停止，请尽快处理!!$body=$body+$message$smtpServer=192.168.5.1$smtp=new-objectNet.Mail.SmtpClient($smtpServer)$smtp.Send($emailFrom,$emailTo,$subject,$body)}3，如何获得SQLServer服务器的空间信息，并发送警告；Pythoncode#byobuntu#2010-8-23$message=Get-WmiObjectwin32_logicaldisk-computername`192.168.2.1,192.168.3.1,192.168.4.1|Where-Object{$_.DriveType-eq3}|Format-Table`-autosystemname,DeviceID,`@{label=FreeSpace(GB);expression={{0:F2}-f($_.FreeSpace/1024/1024/1024)}},`@{label=TotalSize(GB);expression={{0:F2}-f($_.Size/1024/1024/1024)}},`@{label=Note;expression={if((($_.FreeSpace/1024/1024/1024)-lt5){此硬盘剩余空间小于5G，请及时释放!!}else{}}}|out-string#每天的8点和14点进行磁盘空间检查$h=(get-date).hourif(($h-eq8)-or($h-eq16)){$emailFrom=[e-mail]$emailTo=[e-mail]$subject=SQLServer服务磁盘空间检查$body=$message$smtpServer=192.168.3.2$smtp=new-objectNet.Mail.SmtpClient($smtpServer)$smtp.Send($emailFrom,$emailTo,$subject,$body)}elseif($message-match小于5G){$emailFrom=[e-mail]$emailTo=[e-mail]$subject=磁盘空间预警$body=有磁盘空间小于5G，请尽快处理!!$body=$body+$message$smtpServer=192.168.3.2$smtp=new-objectNet.Mail.SmtpClient($smtpServer)$smtp.Send($emailFrom,$emailTo,$subject,$body)}4，如何移动历史备份文件到新的目录，防止做磁带备份时产生冗余。Pythoncode#byobuntu#2010-8-23#将F:\bk_test里的所有文件，包括目录，满足一定条件的移动到F:\bk_test2下$sourcePath=F:\bk_test$targetPath=F:\bk_test2$sourcePathD=$sourcePath+\*$DateToCompare=(Get-date).AddDays(-7)Copy-Item$sourcePathD$targetPath-ForceGet-ChildItem-Path$sourcePath-Recurse-include*.*|where-object{$_.lastwritetime–lt$DateToCompare}`|Move-Item-Destination{Join-Path$targetPath$_.FullName.SubString(($sourcePath).Length)}-Force五、小结只是单纯介绍一些POWERSHELL的实际应用，对一些语法并未进行介绍，但如果想了解一个命令的话，有2种方式，如想要了解Copy-Item的用法，可以用Get-HelpCopy-Item和Copy-Item|Get-Member。上述脚本，可以使用任务计划运行，也可以通过SQLServer创建作业来运行，其实也包含了基本的监控雏形。