PowerShell导入SQLServer的PS模块
- WBOYOriginal
- 2016-06-07 14:55:561716browse
接触过UNIX或者Linux 的朋友都知道此类系统有着功能强大、无所不能的壳程序,称之为Shell。微软公司于2006年第四季度正式发布PowerShell，它的出现标志着, 微软公司向服务器领域迈出了重要的一步, 不仅提供简便的图形化操作界面，同时提供类似于Unix, Linux等
接触过UNIX或者Linux 的朋友都知道此类系统有着功能强大、无所不能的壳程序,称之为Shell。微软公司于2006年第四季度正式发布PowerShell，它的出现标志着, 微软公司向服务器领域迈出了重要的一步, 不仅提供简便的图形化操作界面，同时提供类似于Unix, Linux等操作系统中强大的Shell管理模式。
经过几年的努力，微软已逐步开发各服务器端产品对PowerShell 的支持。不懂PowerShell必将会被淘汰，今天我们就来看看SQL Server对PowerShell的支持。从实质上来说，SQL Server 2008应该是第一个支持Powershell的SQL Server版本，但是其功能不完善，极少有DBA会用到此功能。在SQL Server 2012中增加了更多的cmdlet，功能趋于完善，本文的目的就是介绍在SQL Server 2012环境下如何通过Powershell来实现对SQL Server服务器的管理。
笔者在测试环境Windows Server 2008 R2 SP1上部署的SQL Server 2012，更新PowerShell版本到V3，见之前的博文在 Windows 7 和 Windows Server 2008 R2 上安装 Windows PowerShell 3.0，在PowerShell环境下，输入：
Get-PSDrive
你可以看到类似下面的截屏：
注意：没有对于SQL Server的PSDrive。
导入SQL Server的PS模块
微软虽然针对不同的产品都有各自的Shell，但是这些产品都有相同的shell环境，而不同之处只是导入了不同的PS模块或者说是PowerShell插件。针对SQL Server 2012的管理也是如此，从PowerShell 管理 SQL Server 的方法是将 SQL Server的管理模块sqlps 模块导入到 Windows PowerShell环境中。 该模块将加载并注册 SQL Server 管理单元和管理程序集。
首先，以系统管理员身份启动PowerShell，点击PowerShell图标，右键点击“Run as Administrator”，我们需要使用 Set-ExecutionPolicy cmdlet 设置相应的脚本执行策略。为了防止恶意脚本的执行，PowerShell有一个执行策略，默认情况下，这个执行策略被设为受限的也就是Restricted，意味着PowerShell脚本无法执行。我们可以使用Get-ExecutionPolicy查看当前的执行策略，如下图所示：
默认情况下PowerShell脚本是不允许执行的，我们可以根据需要进行相应的人修改，可供选择的参数有：Restricted、RemoteSigned、AllSigned、Unrestricted。其中Restricted就是默认设置，脚本不能运行; RemoteSigned的意思是本地创建的脚本可以运行，但从网上下载的脚本不能运行，除非它们拥有由受信任的发布者签署的数字签名; AllSigned的意思是仅当脚本由受信任的发布者签名才能运行。Unrestricted则指脚本执行不受限制，不管来自哪里，也不管它们是否有签名，这是个完全放开的策略。我们在此是测试环境，我就直接放开，但方便的同时，就要面临一定的安全风险。如下图所示：
接下来就可以加载SQLPS模块了，在此，我们使用 Import-Module cmdlet，默认情况下，会显示与 Encode-Sqlname 和 Decode-Sqlname 有关的警告，如果不希望显示此类警告信息，可以使用 DisableNameChecking 参数，如下图所示：
此时就成功将 SQL Server 组件导入到 Windows PowerShell环境中。
SQL Server PowerShell组件
Sqlps 模块加载两个Windows PowerShell 管理单元来实现相应的管理功能。 其中一个称为SQL Server PowerShell provider，它允许使用将SQL Server当成一个驱动器来使用，就像我们使用的文件系统路径一样，在该路径中，驱动器与 SQL Server 管理对象模型关联，节点基于对象模型类，如下图所示，使用Get-PSDrive可以列出驱动器信息，在此图中可以看到多出SQLSERVER驱动器。
用户可以使用诸如 dir、cd、del、ren熟悉的命令在命令提示符窗口中定位文件夹以及针对路径中的节点执行操作。 有了SQL Server驱动器后就可以像访问文件系统那样访问SQL Server对象，如实例名、数据库、表、关系等。我们还可以通过dir或者是get-chilitem来查看SQLSERVER驱动器中的内容。如下图所示：
甚至说，我们可以使用dir命令列出本实例上的所有数据库信息，如下图所示：
可以看到，当前实例中有AdventureWorks、AdventureWorks2012、demo、ReportServer、ReportServerTempDB五个数据库，如果不确定可以和图形界面中的结果进行对比，如下图所示：
至于显示其他对象，如审核、可用性组、作业、登录、邮件、触发器等对象的操作方法是一样的，在此不再介绍。
除了SQL Server PowerShell provider程序之外，还可以使用 cmdlet命令进行相应的SQL Server管理， SQL Server cmdlet 支持各种操作，如运行包含 Transact-SQL 或 XQuery 语句的 sqlcmd 脚本。 由于命令较多，大家可以在使用的过程中随时通过Get-Help cmdlet 了解每个 cmdlet 的帮助信息。例如，需要查看Invoke-sqlcmd的帮助信息，就可以如下图所示：
Get-Help 返回各种信息，如语法、参数定义、输入和输出类型以及 cmdlet 所执行操作的说明。在此，还可以加上-Examples 参数列出相应的示例：
SQL Server PowerShell管理示例
在前面的内容中我们了解到SQL Server的扩展插件添加到PowerShell后，SQL Server PowerShell的许多功能以驱动器的形式出现。SQL Server PowerShell provider使得SQL Server看起来更像是一个大磁盘驱动器，像分析服务和数据库引起这些各种组件都像“文件夹”一样。配置设定以“文件”形式展现，用户使用专门的PowerShell命令集如Set-ItemProperty和Get-ItemProperty来操纵这些设定。此外还提供了“动词-名词”形式的Windows PowerShell cmdlet单函数命令来执行相应的Transact-SQL 脚本。下面我们就来看3个小示例：
示例1：显示当前SQL Server的服务器名以及SQL Server的版本号。使用的cmdlet是Invoke-sqlcmd -Query "select @@version,@@servername;"在此，我把结果暂存于变量a中，如下图所示：
一般情况下，输出结果立即被使用，但有时候，用户可能需要将输入结果进行保存下来，以便后期可以再次使用。
示例2：列出SQLSERVER: 路径中的 Databases 节点下的集合子项。Databases节点下存放的是用户数据库的信息，我们前面通过cd、dir命令简单进行了查看，下面我们换一种方式。先使用Set-Location SQLSERVER:\SQL\localhost\DEFAUlt\Databases切换到目标路径下，再通过Get-ChildItem列出其中的内容。如下图所示：
在此界面中可以看到，列出的数据库信息包括了数据库的名称、状态、恢复模式以及排序字符等。
示例3：显示指定数据库中的表的信息。在此我使用的数据库是demo，在此数据库中有一个四个表。如下图所示，首先还是使用:
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\demo\Tables切换到目标节点下，然后使用Get-ChildItem列出相应的表信息。如下图所示：
如果在此只希望列出dbo架构的表怎么办?命令改为：Get-ChildItem | where {$_.Schema -eq "dbo"} 即可，到了V3版本可以修改为：Get-ChildItem | where Schema –eq “dbo”即可，如下图所示：
示例4：使用cmdlet对当前实例中的demo数据库进行完全备份，并将备份文件存放到网络路径\\192.168.18.235\dbbak下，文件名为demo.bak。
首先，我们需要打开“对象资源管理器”。切换要进行处理的对象的节点，在此切换到“数据库”节点即可。右键单击该对象，然后选择“启动 PowerShell”。如下图所示：
接着，我们就可以使用backup-sqldatabase命令对数据库进行备份，此命令功能较强，在此只演示如何进行完全备份。如下图所示：
看到如上所示的结果，就说明操作成功了，当然用户也可以到目标路径中查看文件是否存在，整个命令还是相当简洁明了的。好了，我们的演示就到此结束。
SQL Server开发团队已经把PowerShell广泛地集成到该产品中。管理员需要认真学习PowerShell，这对未来的发展是有益的。亲爱的朋友，千万不要轻视PowerShell，不仅仅是在SQL Server 2012产品中，其他微软服务器也是如此，因为PowerShell是微软服务器管理方式的发展方向，一起努力吧!