图书
电子书
课程
VIP会员
书课包

PowerShell V3——SQL Server 2012数据库自动化运维权威指南

978-7-115-40877-8
作者: 【加拿大】Donabel Santos
译者: 许昌永
编辑: 赵轩
分类: SQL Server

图书目录:

详情

PowerShell是微软的新命令行Shell和脚本语言，承诺会简化自动化过程，集成在微软的不同应用和组件中。数据库专业人士可以利用它内置的大量cmdlets，或使用任意可用的.NET类来操作PowerShell，来自动化数据库任务，简化集成，或只是为了使用新的方法完成手头的工作。

图书摘要

版权信息

书名：PowerShell V3——SQL Server 2012数据库自动化运维权威指南

ISBN：978-7-115-40877-8

本书由人民邮电出版社发行数字版。版权所有，侵权必究。

您购买的人民邮电出版社电子书仅供您个人使用，未经授权，不得以任何方式复制和传播本书内容。

我们愿意相信读者具有这样的良知和觉悟，与我们共同保护知识产权。

如果购买者有侵权行为，我们可能对该用户实施包括但不限于关闭该帐号等维权措施，并可能追究法律责任。

•　著 　　　　[美]Donabel Santos

　 译　　　　许昌永

　 责任编辑　赵　轩

•　人民邮电出版社出版发行　　　　北京市丰台区成寿寺路11号

　 邮编 100164　　　　电子邮件 315@ptpress.com.cn

　 网址 http://www.ptpress.com.cn

•　 读者服务热线：(010)81055410

　  反盗版热线：(010)81055315


本书面向希望学习如何管理SQL Server环境的初中级数据库管理员，适用于已经掌握了SQL Server基础知识，但缺少实践经验的读者，对初学者来说更是一本进阶的必备指导用书。

本书适用于旨在使用PowerShell 自动化、整合和简化数据库任务的数据库专业人士（DBA、开发、BI 开发），同时也适用于没有脚本编程背景的读者。

本书特色

从本书可以学到：


Copyright © 2012 Packt Publishing. First published in the English language under the title SQL Server 2012 with PowerShell V3 Cookbook

All rights reserved.

本书由美国Packt Publishing 公司授权人民邮电出版社出版。未经出版者书面许可，对本书的任何部分不得以任何方式或任何手段复制和传播。

版权所有，侵权必究。


PowerShell是微软的新命令行Shell和脚本语言，承诺会简化自动化过程，集成在微软的不同应用和组件中。数据库专业人士可以利用它内置的大量cmdlets，或使用任意可用的.NET类来操作PowerShell，来自动化数据库任务，简化集成，或只是为了使用新的方法完成手头的工作。

本书从基础的主题开始，简单介绍SQL Server 和PowerShell 脚本，然后进入高级的主题，帮助你管理、维护SQL Server 数据库。


PowerShell 是微软的新命令行Shell和脚本语言，可以简化微软不同的应用程序和组件之间的自动化过程和整合过程。数据库专业人士可以利用它内置的大量cmdlets或任意可用的.NET 类来操作PowerShell，使数据库任务自动化，简化整合过程，或者探索完成手头工作的新方法。

本书向忙碌的数据库专业人士提供了大量简单易学的实战案例。无论是你是在审核服务器，导出数据，还是部署报表，总有一种方案适合你！

本书从基础的主题开始，简单介绍SQL Server 和 PowerShell 脚本，然后进入高级的主题，帮助你管理、维护SQL Server 数据库。

前面几章描述了如何配置SQL Server和操作对象，包括导出对象、创建数据库、配置实例参数和部署报表。本书随后深入更多管理主题，例如备份和恢复、凭据、策略和作业。

其他开发和BI相关主题也有涉及，包括部署和下载程序集、BLOB数据、SSIS包和SSRS报表。

附录提供了简单的PowerShell入门资料，数据库专业人士可用于温习基础知识或作为参考资料。本书内含100多个实战性的可用脚本，可以作为自动化运维和管理SQL Server的参考。

第1章，SQL Server和PowerShell入门，解释了PowerShell是什么以及你为什么应该学习PowerShell。介绍了PowerShell V3的新属性，解释了在使用SQL Server 2012和PowerShell工作时需要哪些准备。

第2章，SQL Server和PowerShell的基础任务，展示了使用PowerShell完成基本的SQL Server任务的脚本和代码片段。我们从执行简单的任务开始，例如列出SQL Server实例，创建对象，例如表、索引、存储过程和函数，使你通过编程方式更加轻松地使用SQL Server。

第3章，基本管理，着手处理更多使用PowerShell能完成的可管理性任务，提供了将大量重复任务自动化的方案。有的方案处理实例和数据库属性；有的方案提供检查磁盘空间，创建WMI警告，配置数据库邮件以及创建和维护SQL Server作业的方法。

第4章，安全，提供了简化安全监控的片段，包含如何通过解析事件日志检查失败的登录尝试，或者如何管理角色和许可。

第5章，高级管理，介绍了PowerShell如何帮助你掌握基于策略的管理（PBM）和加密等属性。本章也包含通过编程方式使用SQL Server Profiler跟踪文件和事件。

第6章，备份和恢复，深入讲解通过使用Powershell以编程方式备份和恢复SQL Server数据库的不同方法。

第7章，SQL Server开发，提供了代码片段和引导，教你如何在PowerShell和SQL Server中使用XML、XSL、二进制数据和CLR程序集。

第8章，商业智能，包含PowerShell如何管理与BI相关的任何任务并实现自动化—从渲染SQL Server报表服务（SSRS）报表，到部署新的SQL Server集成服务（SSIS）2012 ISPAC文件，再到备份和恢复SQL Server分析服务（SSAS）cubes。

第9章，有用的PowerShell代码，包括大量并非专用于SQL Server的方案，但是你会发现这些方案在PowerShell中非常有用。方案包括使用时间戳创建文件，针对当前的系统错误分析时间日志以及将进程列表导出到CSV或XML文件中。

附录A，SQL Server和PowerShell的快捷键，提供了当使用PowerShell操作SQL Server时常用的术语和片段的简明快捷键。

附录B，PowerShell入门，简单介绍PowerShell原理的入门知识。

附录C，资源，列出了学习PowerShell和SQL Server的其他书籍、博客和链接。

附录D，创建SQL Server VM，提供了关于如何创建和配置虚拟机用于本书的循序渐进的教程。

Windows Server 2008 R2
SQL Server 2012 Developer
Visual Studio 2010 Professional
Windows Management Framework 3.0（包含PowerShell 3.0、WMI和WinRM）

本书适用于旨在使用PowerShell自动化、集成和简化数据库任务的数据库专业人士（DBA、开发、BI开发）。同时适用于没有脚本编程背景的读者。


PowerShell是一款拥有Shell和脚本能力的可管理工具，可以用来调用Windows管理规范（Windows Management Instrumentation，WMI）、COM组件和.NET库。PowerShell在一代一代的微软产品中变得更加杰出。在大量的、新的、即将发布的微软产品中，对于它的支持是捆绑式的，并有所加强。Windows Server、Exchange、ActiveDirectory、SharePoint，甚至SQL Server，所有这些都添加了PowerShell支持和cmdlets。甚至一些供应商如VMWare、Citrix、Cisco和Quest提供了允许他们的产品被PowerShell访问的方法。

是什么使得PowerShell发威？每个系统管理员可能都知道通过脚本集成各种系统的痛苦。曾经用到的解决方案是集成各种脚本，包括VBScript、一些批处理文件，或者C#代码、Perl脚本。系统管理员要么捆绑不同的语言来工作，要么因为复杂的代码而束手无策。

这就是PowerShell的来由。PowerShell最重要的一点是，它简化了自动化和在不同微软子系统之间的集成。因为大多数产品都支持PowerShell，让一个系统与另一个系统沟通只是一件探索在脚本中添加哪些cmdlets、功能或模块的事情。即便产品现在还不支持PowerShell，但它很有可能已经有了.NET或COM组件的支持，能够轻松使用PowerShell。

在最新版本中，值得注意的属性如下所示。

#check current modules in session
Get-Module
#use cmdlet from CimCmdlets module, which
#is not loaded yet
Get-CimInstance win32_bios 
#note new module loaded CimCmdlets
Get-Module 
#use cmdlet from SQLPS module, which
#is not loaded yet
Invoke-Sqlcmd -Query "SELECT GETDATE()" -ServerInstance "KERRIGAN"
#note new modules loaded SQLPS and SQLASCmdlets 
Get-Module

Get-Service | Where-Object { $_.Status –eq' Running' }
Get-Service | Where-Object Status –eq' Running'
Get-PSDrive
Set-ExecutionPolicy RemoteSigned

在我们深入方法之前，重温一些重要的概念和术语，将会帮助我们理解SQL Server和PowerShell如何一起工作。

Get-PSProvider
Add-PSSnapin SqlServerCmdletSnapin100
Import-Module SQLPS

本书中的示例已经针对在Windows Server 2008 R2上的SQL Server 2012测试过。

PS C:\Users\Administrator>Import-Module ServerManager
PS C:\Users\Administrator>Add-WindowsFeature PowerShell-ISE
PS C:\Users\Administrator>powershell_ise
Set-ExecutionPolicy RemoteSigned

如果你想使用当前的机器，而不想创建一个单独的VM虚拟机，像附录D中描述的那样，创建一个SQL Server VM虚拟机，通过以下步骤准备你的机器。

1．在你当前的操作系统上安装SQL Server 2012 – Windows 7或者Windows Server 2008 R2。查看SQL Server 2012支持的操作系统列表。

http://msdn.microsoft.com/en-us/library/ms143506.aspx

2．安装PowerShell V3。

尽管PowerShell V3开始安装在Windows 8和Windows Server 2012上，在写本书时，这两个操作系统没有列在SQL Server 2012支持的操作系统列表中。

在Windows 7 SP1、Windows Server 2008 SP2或Windows Server 2008 R2 SP1中安装PowerShell V3。

安装Microsoft .NET Framework 4.0，如果还没有安装。

下载并安装Windows Management Framework 3.0，它包含PowerShell V3。在写本书时，Release Candidate（RC）可从这里获得。

http://www.microsoft.com/en-us/download/details.aspx?id=29939

3．开启PowerShell V3 ISE。在本书中我们将会在很多示例中使用增强集成脚本环境。

http://technet.microsoft.com/en-us/edge/Video/hh533298

http://msdn.microsoft.com/en-us/library/hh245198(SQL.110).aspx

在SQL Server 2012中，原始的迷你Shell已经被废弃，SQLPS现在作为模块。从SSMS进入到PowerShell，现在是进入了一个Windows PowerShell会话，导入SQLPS模块，设置当前的安全上下文为PowerShell会话进入的源条目。DBA和开发可以从这里浏览对象架构。

登录SSMS

在这个方案中，我们将从SSMS进入PowerShell会话来浏览SQL Server PowerShell层级结构。

1．右击实例节点。

2．单击“Start PowerShell”。将进入PowerShell会话，导入SQLPS模块。窗口看起来类似一个命令提示符，有一个当前窗口的SQL Server对象源的提示。

注意窗口的开始路径。

3．输入dir。列出当前实例下可访问的所有对象的列表，对于我来说，默认实例是WIN- LLPKR5BVV6S。注意dir是cmdlet Get-ChildItem的别名。

类似于在SSMS的“Object Explorer”中，在实例节点下所看到的对象。

4．当PowerShell窗口打开的时候，浏览SQL Server PSDrive或者SQL Server数据存储，PowerShell视为条目序列。输入cd \。到当前驱动器的根目录，就是SQL Server PSDrive的路径将会改变。

5．输入dir。列出所有SQL Server PSDrive根目录下可访问的条目，将会看到类似以下的截屏。

6．关闭窗口。

7．返回SSMS，右击一个用户数据库。

8．单击“Start PowerShell”。注意，这将会进入另一个PowerShell会话，路径将指向你右击的数据库。

注意，这个窗口的开始路径不同于第二步中第一次进入PowerShell的路径。如果你从当前位置输入dir，将会看到在AdventureWorks 2008 R2数据库下面的所有条目。

如果你展开AdventureWorks2008R2数据库节点，你可以看到在SSMS的“Object Explorer”中列举的一些条目。

当PowerShell从SSMS进入时，会创建一个上下文敏感的PowerShell，它将自动导入SQLPS模块。提示符很明显显示了“Start PowerShell”菜单被单击的源对象的路径。

SQL Server 2008/2008 R2作为SQLPS迷你Shell，也作为SQLPS工具集。也可以通过从SSMS的“Object Explorer”右击一个对象，然后单击“Start PowerShell”进行启动。这个迷你Shell被设计为预加载了SQL Server扩展的封闭Shell。这个Shell意味着只能用于SQL Server，被证明是相当有限的，因为DBA和开发常常需要通过PowerShell导入插件和模块来集成SQL Server和其他系统。另一个方法是进入一个成熟的PowerShell会话，这依赖于你的PowerShell版本要么导入插件，要么导入SQLPS模块。

在SQL Server 2012中，原始的迷你Shell已经被废弃了。当你从SQL Server 2012的SSMS进入一个PowerShell会话时，它进入了一个成熟的PowerShell会话，默认导入更新的SQLPS模块。

SQL Server作为一个PowerShell驱动设备（PSDrive），可以像切换目录和文件一样切换对象。因此，提供者支持熟悉的切换目录命令，如dir或者ls。注意，这些熟悉的命令常常只是真实的cmdlet的别名，这里是Get-ChildItem。

通过SSMS进入PowerShell时，你能立刻开始浏览SQL Server PowerShell的层次结构。

SQL Server 2005引入了SQL Server管理对象（SQL Server Management Objects，SMO），允许SQL Server通过编程方式访问和管理。SMO可以用于任何.NET语言，包括C#、VB.NET和PowerShell。SMO是实现大多数SQL Server任务自动化的关键。SMO也向后兼容之前的SQL Server版本，扩展支持一直到SQL Server 2000。

SMO由两大不同的类组成：实例类和工具集类。

实例类就是SQL Server对象。对象的属性，如服务器、数据库、表，可以被实例类访问和设置。

工具集类是完成普通SQL Server任务的助手或工具集类。这些类属于这三个组之一：传输类、备份和恢复类或者脚本导出者类。

为了能够访问SMO库，需要安装SMO，并导入SQL Server相关的程序集。

安装SMO有几种方法：

如果你正在安装SQL Server或者已经有SQL Server，请按以下步骤操作。

1．加载你的安装盘或镜像，单击setup.exe文件。

2．选择“New SQL Server standalone installation or add features to an existing installation”。

3．选择你的安装类型，单击“Next”。

4．在“Feature Selection”窗口，确保选择了“Client Tools SDK”。

5．完成安装。

现在，已经有了使用SMO所需的所有二进制文件。

如果没有安装SQL Server，必须通过SQL Server属性包在要使用SMO的机器上来安装SMO。

（1）打开浏览器，选择你喜欢的搜索引擎，搜索SQL Server 2012属性包。

（2）下载该包。

（3）双击“SharedManagementObjects.msi”安装。

默认情况下，SMO程序集安装在<SQL Server安装目录>\110\SDK\Assemblies。

在使用SMO库之前，需要导入程序集。在SQL Server 2012中，这个步骤比以前容易得多。

SQL管理对象（SQL Management Objects，SMO）必须已经安装在你的机器上。

在这个方案中，我们将导入SQLPS模块。

1．打开PowerShell控制台或者PowerShell ISE，或者你喜欢的PowerShell编辑器。

2．输入import-module命令。

Import-Module SQLPS

3．确认模块被导入。

Get-Module

导入SMO程序集的方法在不同PowerShell版本之间有所不同。

在PowerShell V1中，可以使用Load()或LoadWithPartialName()方法导入程序集。LoadWithPartialName()接受程序集的部分名称，从应用程序目录或者全局程序集缓存（Global Assembly Cache，GAC）导入。

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.
Smo")

尽管LoadWithPartialName()仍被支持，仍然保留了流行的导入程序集方法，但这个方法不应该再被使用，因为它将会在将来的版本中被废弃。

Load()需要程序集的完整标识名。

[void][Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, 
Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

在PowerShell V2中，可以通过Add-Type添加程序集。

Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

在PowerShell V3中，不再需要逐个导入程序集，只需导入SQLPS。

Import-Module SQLPS

如果你正在使用特定的SQL Server版本，可能在有些情况下你仍然需要导入特定的DLL版本，或者你可能只想导入特定的程序集而不是导入整个SQLPS模块。在这种情况下，Add-Type仍然是导入程序集的有效方法。

当你导入SQLPS模块时，你可能会看到关于冲突或未经批准之类的语言。

这意味着有些cmdlets不需要遵守PowerShell命名规范，但模块和它包含的cmdlets仍然全部导入到你的主机。为了阻止该警告，导入模块时使用-DisableNameChecking参数。

为了有效地使用SQL Server和PowerShell工作，需要了解如何浏览和发现cmdlets、插件和模块。

登录到SQL Server实例，进入PowerShell ISE。如果你更喜欢控制台，你也可以进入控制台。

在这个方案中，我们将会列出SQL Server相关的命令和cmdlets。

1．为了发现SQL相关的cmdlets，在PowerShell编辑器中输入如下命令并运行。

#how many commands from modules that
#have SQL in the name
Get-Command -Module "*SQL*" | Measure-Object

#list all the SQL-related commands
Get-Command -Module "*SQL*" |
Select CommandType, Name, ModuleName |

Sort -Property ModuleName, CommandType, Name |
Format-Table –AutoSize

执行命令后，输出窗口将会出现类似如下截屏。

2．查看哪些模块被导入，在编辑器中输入如下语句并运行。

Get-Module -Name "*SQL*"

如果你已经在之前的步骤中用了这些cmdlets中的任何一个，你都会看到SQLPS和SQLASCMDLETS。因此，你需要在使用它们之前导入这些模块。

3．为了明确地导入模块，输入如下语句并运行。

Import-Module -Name "SQLPS"

注意，当你导入SQLPS时，SQLASCMDLETS将会被导入。

PowerShell的核心是cmdlets。cmdlet（读作commandlet）可以是编译的、可重用的.NET代码，可以是一个高级函数，也可以是一个专门执行特殊任务的工作流。所有的cmdlets遵循“动词-名词”命名规范。

PowerShell包含很多cmdlets，如果这些cmdlets不满足你的目的，还可以进步一扩展。

扩展PowerShell的传统方法是注册额外的插件。插件（snap-in）是一个包含了cmdlets的二进制文件或一个包含cmdlets的DLL文件。你可以构建.NET源码，通过编译来创建自己的插件，然后注册插件。使用它们之前，需要注册插件。插件是扩展PowerShell的流行方法。

下表总结了关于插件的常用任务。

任务

语法

列出导入的插件

Get-PSSnapin

列出安装的插件

Get-PSSnapin –Registered

显示插件中的命令

Get-Command –Module “SnapinName”

导入一个特定的插件

Add-PSSnapin “SnapinName”

从PowerShell V2开始，模块成为扩展PowerShell的更好方法。

模块是包含cmdlets、提供者、函数、变量和别名的包。在PowerShell V2中，默认情况下，模块不会被导入，所以需要的模块必须明确被导入。

有关模块的常用任务总结见下表。

任务

语法

列出导入的模块

Get-Module

列出安装的模块

Get-Module –ListAvailable

显示模块中的命令

Get-Command –Module “ModuleName”

导入一个特定的模块

Import-Module –Name “ModuleName”

PowerShell V3增强的属性之一是支持自动导入模块。你不需要总是在使用包含的命令之前明确导入模块。在脚本中使用cmdlet会触发PowerShell导入包含它的模块。

SQL Server 2012模块位于安装目录的PowerShell/Modules文件夹下。

下表显示了这个版本的SQLPS和SQLASCMDLETS cmdlets列表。

CommandType Name

ModuleName

Cmdlet Add-RoleMember

SQLASCMDLETS

Cmdlet Backup-ASDatabase

SQLASCMDLETS

Cmdlet Invoke-ASCmd

SQLASCMDLETS

Cmdlet Invoke-ProcessCube

SQLASCMDLETS

Cmdlet Invoke-ProcessDimension

SQLASCMDLETS

Cmdlet Invoke-ProcessPartition

SQLASCMDLETS

Cmdlet Merge-Partition

SQLASCMDLETS

Cmdlet New-RestoreFolder

SQLASCMDLETS

Cmdlet New-RestoreLocation

SQLASCMDLETS

Cmdlet Remove-RoleMember

SQLASCMDLETS

Cmdlet Restore-ASDatabase

SQLASCMDLETS

Cmdlet Add-SqlAvailabilityDatabase

SQLPS

Cmdlet Add-SqlAvailabilityGroupListenerStaticIp

SQLPS

Cmdlet Backup-SqlDatabase

SQLPS

Cmdlet Convert-UrnToPath

SQLPS

Cmdlet Decode-SqlName

SQLPS

Cmdlet Disable-SqlHADRService

SQLPS

Cmdlet Enable-SqlHADRService

SQLPS

Cmdlet Encode-SqlName

SQLPS

Cmdlet Invoke-PolicyEvaluation

SQLPS

Cmdlet Invoke-Sqlcmd

SQLPS

Cmdlet Join-SqlAvailabilityGroup

SQLPS

Cmdlet New-SqlAvailabilityGroup

SQLPS

Cmdlet New-SqlAvailabilityGroupListener

SQLPS

Cmdlet New-SqlAvailabilityReplica

SQLPS

Cmdlet New-SqlHADREndpoint

SQLPS

Cmdlet Remove-SqlAvailabilityDatabase

SQLPS

Cmdlet Remove-SqlAvailabilityGroup

SQLPS

Cmdlet Remove-SqlAvailabilityReplica

SQLPS

Cmdlet Remove-SqlDatabase

SQLPS

Cmdlet Resume-SqlAvailabilityDatabase

SQLPS

Cmdlet Set-SqlAvailabilityGroup

SQLPS

Cmdlet Set-SqlAvailabilityGroupListener

SQLPS

Cmdlet Set-SqlAvailabilityReplica

SQLPS

Cmdlet Set-SqlHADREndpoint

SQLPS

Cmdlet Suspend-SqlAvailabilityDatabase

SQLPS

Cmdlet Switch-SqlAvailabilityGroup

SQLPS

Cmdlet Test-SqlAvailabilityGroup

SQLPS

Cmdlet Test-SqlAvailabilityReplica

SQLPS

Test-SqlDatabaseReplicaState

SQLPS

了解更多关于cmdlets的信息，使用Get-Help cmdlet。例如：

Get-Help "Invoke-Sqlcmd"
Get-Help "Invoke-Sqlcmd" -Detailed
Get-Help "Invoke-Sqlcmd" -Examples
Get-Help "Invoke-Sqlcmd" -Full

你也可以查阅有关SQL Server数据库引擎cmdlets的MSDN文章。

http://msdn.microsoft.com/en-us/library/cc281847.aspx

当你导入SQLPS模块时，一些程序集会导入到你的主机。

使用下面的脚本，可以获取导入了SQLPS模块的SQL Server相关程序集，它们在PowerShell V2和V3下都是有效的。

Import-Module SQLPS –DisableNameChecking
[appdomain]::CurrentDomain.GetAssemblies() | 
Where {$_.FullName -match "SqlServer" } | 
Select FullName

如果你想严格在V3环境下运行，可以利用如下简化的语法。

Import-Module SQLPS –DisableNameChecking
[appdomain]::CurrentDomain.GetAssemblies() | 
Where FullName -match "SqlServer" | 
Select FullName

下图将会显示所有导入的程序集，包括它们的公钥令牌。

有关运行PowerShell脚本的更多信息

默认情况下，PowerShell在受限模式（restricted）下运行，换句话说，它不能运行脚本。为了运行本书中的脚本，我们需要将执行策略设置为RemoteSigned。

Set-ExecutionPolicy RemoteSigned

如果将PowerShell代码保存到一个文件里，需要确保它的后缀名为.ps1，否则PowerShell将无法运行。理论上，给定的脚本文件名不会有空格。你可以从PowerShell控制台通过简单调用这个名字来运行这个脚本。例如，如果你在C:\目录下有一个名为myscript.ps1的脚本，按如下方式调用：

PS C:\> .\myscript.ps1

如果文件或文件的路径中有空格，你需要将完整路径和文件名用单引号或双引号括起来，并且用调用操作符（&）。

PS C:\>&'.\my script.ps1'

如果你想保持脚本中的变量和函数，在内存中，使得它们在会话中全局可用，你需要用点号加载这个脚本。点号加载就是在文件名或者文件的路径前加上逗号和空格。

PS C:\>. .\myscript.ps1
PS C:\>. '.\my script.ps1'

有关混合程序集错误的更多信息

当运行用旧版.NET创建的命令时，可能会遇到一个错误。有趣的是，你可能会在PowerShell ISE运行脚本时看到它，但在Shell中则不一定。

Invoke-Sqlcmd: Mixed mode assembly is built against version 'V2.0.50727' 
of the runtime and cannot be loaded in the 4.0 runtime without additional 
configuration information.

解决这个问题的步骤如下。

1．打开“Windows Explorer”。

2．找出Windows PowerShell ISE安装目录。你可以通过单击“Start | All Programs | Accessories | Windows | PowerShell”，然后右击“Windows PowerShell ISE”菜单条目，然后选择“Properties”。

对于32位的ISE，默认路径为：

%windir%\sysWOW64\WindowsPowerShell\v1.0\PowerShell_ISE.exe

对于64位的ISE，默认路径为：

%windir%\system32\WindowsPowerShell\v1.0\PowerShell_ISE.exe

3．进入PowerShell ISE安装目录。

4．创建一个空的文件，命名为powershell_ise.exe.config。

5．添加如下片段到内容中，并保存文件。

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" />
</startup>
<runtime>
<generatePublisherEvidence enabled="false" />
</runtime>
</configuration>

6．重新打开PowerShell ISE，并重试失败的命令。

大多数在SQL Server中的操作都需要连接到实例。

打开PowerShell控制台、PowerShell ISE或者你喜欢的PowerShell编辑器。

你需要注意你的实例名是什么。如果你有一个默认实例，你可以使用机器名。如果你有一个命名实例，这个格式将会是<机器名>\<实例名>。

如果你使用Windows验证连接到实例，使用你当前的Windows登录，按如下步骤操作。

1．导入SQLPS模块。

#import SQLPS module
Import-Module SQLPS –DisableNameChecking

2．将实例名保存到变量。

#create a variable for your instance name
$instanceName = "KERRIGAN"

3．如果你使用Windows验证，使用你登录的帐号登录到实例。

#create your server instance
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

如果你使用SQL验证连接，你需要知道用于验证的用户名和密码。在这里，你需要添加以下代码，设置连接为混合模式，并提示用户名和密码。

#set connection to mixed mode
$server.ConnectionContext.set_LoginSecure($false)
#set the login name
#of course we don't want to hardcode credentials here
#so we will prompt the user
#note password is passed as a SecureString type
$credentials = Get-Credential
#remove leading backslash in username
$login = $credentials.UserName -replace("\\", "")
$server.ConnectionContext.set_Login($login) 
$server.ConnectionContext.set_SecurePassword($credentials.Password)
#check connection string
$server.ConnectionContext.ConnectionString
Write-Verbose "Connected to $($server.Name)"
Write-Verbose "Logged in as $($server.ConnectionContext.TrueLogin)"

在你用程序访问和操作SQL Server之前，通常需要创建对象的参照。最基本的是服务器。

服务器实例使用Microsoft.SqlServer.Management.Smo.Server类型。默认情况下，到服务器的连接使用信任连接，意思是它使用你登录到服务器时所使用的Windows帐号。因此在参数列表中只需要实例名。

#create your server instance
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server-ArgumentList
$instanceName

而如果你需要使用SQL登录，你需要将SMO服务器类的ConnectionContext.LoginSecure属性设置为false。

#set connection to mixed mode
$server.ConnectionContext.set_LoginSecure($false)

你也需要明确设置用户名和密码。最好的方式是提示用户输入凭据。

#prompt
$credentials = Get-Credential

凭据窗口将捕获用户名和密码。如果没有使用域，Get-Credential返回带有前导反斜杠的用户名。在这里，我们想删除这个前导反斜杠。

#remove leading backslash in username
$login = $credentials.UserName -replace("\\","")

一旦获取到登录名，我们将它传给set_Login方法。密码已经是一个SecureString类型，也是set_SecurePassword要求的，因此我们乐意将它传给这个方法。

$server.ConnectionContext.set_Login($login)
$server.ConnectionContext.set_SecurePassword($credentials.Password)

你可能指定用户名，只提示输入密码，也可以这样：

$login="belle"
#prompt
$credentials = Get-Credential -Credential $login

在这个脚本中，你也注意到了，我们使用Write-Verbose替代Write-Host来显示结果。因为我们希望能控制输出而不用总是返回到我们的脚本，我们移除了所有的Write-Host命令。

默认情况下，脚本不会显示任何输出，也就是说，$VerbosePreference特殊变量设置为SilentlyContinue。如果你想在verbose模式运行脚本，你只需要将下面这行内容添加到你的脚本开头。

$VerbosePreference = "Continue"

当完成后，你只需要将该值修改为SilentlyContinue。

$VerbosePreference = "SilentlyContinue"

SQL管理对象（SQL Management Objects，SMO）拥有一个可编程访问的对象层次结构。例如，当你创建SMO服务器变量时，你可以访问数据库、登录和数据级别触发器。一旦获得单独数据库的句柄，我们可以切换它所包含的访问表、存储过程和视图。由于很多任务涉及SMO对象，如果你知道如何发现和浏览这些对象就会很有用。

打开PowerShell控制台、PowerShell ISE或者你喜欢的PowerShell编辑器。

你也需要注意你的实例名是什么。如果你使用默认实例，你可以使用机器名。如果你使用命名实例，这个格式将会是<机器名>\<实例名>。

在这个方法中，我们将使用SMO浏览对象层次结构。

1．导入SQLPS模块。

Import-Module SQLPS –DisableNameChecking

2．创建服务器实例。

$instanceName = "KERRIGAN"
$server = New-Object `
        -TypeName Microsoft.SqlServer.Management.Smo.Server `
        -ArgumentList $instanceName

3．获得可直接从$server对象可访问的SMO对象。

$server | 
Get-Member -MemberType "Property" | 
Where Definition -like "*Smo*"

4．现在查看在数据库下的SMO对象。执行如下操作。

$server.Databases | 
Get-Member -MemberType "Property" | 
Where Definition -like "*Smo*"

5．查看表，你可以输入以下内容并执行。

$server.Databases["AdventureWorks2012"].Tables | 
Get-Member -MemberType "Property" | 
Where Definition -like "*Smo*"

SMO包含了对象架构。在顶部有一个服务器对象，依次包含的对象包括Databases、Configuration、SqlMail、LoginCollection等。这些对象依次包含其他对象，例如，Databases是一个包含Database对象的集合，Database对象包含了Tables对象等。


本章介绍使用PowerShell完成一些基本的SQL Server任务的脚本和代码片段。我们将从简单任务开始，例如，列出SQL Server实例，创建对象，如表、索引、存储过程和函数，让你轻松以编程方式使用SQL Server。

你会发现一些使用PowerShell和SQL管理对象（SQL Management Object，SMO）的方法。SMO提供了很多SQL Server类，允许以编程方式操作，使一些数据库任务自动化。对于一些方法，我们也将会探究使用不同的本地PowerShell cmdlets来完成相同任务的不同方法。

虽然我们在探究如何使用PowerShell创建一些常用的数据库对象，但是我们会注意到，PowerShell并不总是完成任务的最好工具。有些任务最好使用T-SQL来完成。即便如此，了解使用PowerShell可以做什么和如何做仍然是好的，因此，你知道依赖于你的需求和环境的替代方案。

这些方案使用的开发环境的配置如下：

组件

语法

域

QUERYWORKS

机器名

KERRIGAN

实例

KERRIGAN or (local) or localhost QL01

数据库

AdventureWorks2008R2

域帐号

QUERYWORKS\aterra
QUERYWORKS\jraynor
QUERYWORKS\mhorner

为简化练习，在你的环境中以管理员帐号运行PowerShell脚本。此外，确保该帐号在你工作的服务器上有完全访问SQL Server实例的权限。

我们将在此任务中的所有脚本使用PowerShell ISE。你需要记住一些事情。

脚本窗格（Script Pane）是输入PowerShell代码的地方。输出窗格（Output Pane）是看到结果的地方。

命令窗格（Command Pane）输入即时查询命令的地方，只要你按Enter键立即执行。

在我们的方案中，我们将使用脚本窗格（Script Pane）来书写和执行脚本。根据任务，你可能需要做以下操作之一：

PS C:\>.\SampleScript.ps1 param1 param2
PS C:\>#if your path has no space
PS C:\>C:\MyScripts\SampleScript.ps1 param1 param2
　
PS C:\>#if your path has space
PS C:\>&" C:\My Scripts\SampleScript.ps1" param1 param2
PS C:\>. .\SampleScript.ps1 param1 param2
PS C:\>." C:\My Scripts\SampleScript.ps1" param1 param2

如果你更喜欢使用PowerShell控制台运行脚本而不是使用ISE，你可以按照如下步骤操作。

1．以扩展名.ps1保存文件。

2．通过“Start | Accessories | Windows PowerShell | Windows PowerShell”打开PowerShell控制台。

3．我们可能希望运行本地创建的脚本。为了这样做，我们需要将“执行策略”（ExecutionPolicy）设置为RemoteSigned。

4．设置执行策略为RemoteSigned。

5．你可以从如下选项中挑选。

在这个方案中，我们将会列出本地网络中的所有SQL Server实例。

以管理员帐号登录有SQL Server开发实例的服务器。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．让我们使用Start-Service cmdlet来启动SQLBrowser服务。

Import-Module SQLPS -DisableNameChecking
#sql browser must be installed and running
Start-Service "SQLBrowser"

（译者注：若SQLBrower的Start Mode为Disabled，则会失败）

3．接下来，你需要创建ManagedComputer对象来访问实例。输入如下脚本并运行。

$instanceName = "KERRIGAN"　
$managedComputer = New-Object 
'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' $instanceName
#list server instances　　　　
$managedComputer.ServerInstances

你的结果类似于下面的截屏。

注意，$managedComputer.ServerInstances不仅提供了实例名，而且包含额外的属性，例如ServerProtocols、Urn、State等。

4．确保在SSMS中有相同的实例。打开SSMS。

5．打开“Connect | Database Engine”。

6．在“Server Name”下拉列表，单击“Browse for More”。

7．选择“Network Servers”窗口，查看列出的实例。你的屏幕可能看起来类似于。

Windows操作系统中的所有服务都可以通过Windows管理规范（Windows Management Instrumentation，WMI）来访问。WMI是微软的框架，用于列出、设置和配置微软相关的资源。这个框架遵循基于Web的企业管理（Web-based Enterprise Management，WBEM）。分布式管理任务组定义WBEM为（http://www.dmtf.org/standards/wbem）：

一个管理和网络标准计数集合开发用于同一分布式计算环境的管理。WBEM为行业提供了标准管理工具的很好集成的集合，促进了完全不同的技术和平台的数据交换。

为了访问SQL Server WMI相关的对象，你可以创建一个WMI ManagedComputer实例。

$managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.
ManagedComputer' $instanceName

ManagedComputer对象访问ServerInstance属性，循环列出本地网络中所有可用的实例。只有在SQL Server Browser服务运行的情况下才能识别这些实例。

SQL Server Browser是一个Windows服务，提供了关于已安装的实例的信息。如果你想列出SQL Server相关的服务，需要启动该服务。

另一个使用ManagedComputer的备选方案是使用System.Data.Sql.SqlDataSource Enumerator类列出本地网络中的所有SQL Server实例。

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | 
Select ServerName, InstanceName, Version | 
Format-Table –AutoSize

执行后，你的结果类似于下面的截屏。

另一个得到SQL Server WMI对象的句柄的方法是使用Get-WmiObject cmdlet。然后，这将不会通过Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer对象展示相同的属性。

为了做到这点，你需要首先发现在你的环境中哪些命名空间是可用的。

$hostname = "KERRIGAN" 
$namespace = Get-WMIObject -ComputerName $hostName -NameSpace root\
Microsoft\SQLServer -Class "__NAMESPACE" | 
　　　　　　　　 Where Name -Like "ComputerManagement*"

对于SQL Server 2012，值为：

ROOT\Microsoft\SQLServer\ComputerManagement11

一旦你有这个命名空间，你可以通过Get-WmiObject使用这个值去获取实例。可以用于过滤的属性为SqlServiceType。

根据MSDN（http://msdn.microsoft.com/en-us/library/ms179591.aspx），下面是SqlServiceType的值。

SqlServiceType

描述

1

SQL Server服务

2

SQL Server Agent 服务

3

Full-text Search Engine服务

4

Integration Services服务

5

Analysis Services服务

6

Reporting Services服务

7

SQL Server Browser服务

因此，为了获得SQL Server实例，你需要过滤SQL Server服务，或者SQLServiceType=1。

Get-WmiObject -ComputerName $hostname `
-Namespace "$($namespace.__NAMESPACE)\$($namespace.Name)" `
-Class SqlService |
Where SQLServiceType -eq 1 |
Select ServiceName, DisplayName, SQLServiceType |
Format-Table –AutoSize

你的结果可能类似于如下截屏。

在这个方案中，我们列举所有的SQL Server服务，并列出它们的状态。

检查哪个SQL Server服务安装在你的实例上。打开“Start | Run”并输入“services.msc”。你将会看到类似于如下屏幕。

让我们确保你在服务器上运行这个脚本。

1．打开“Start | Accessories | Windows PowerShell | Winodws PowerShell ISE”，进入PowerShell控制台。

2．添加下面的代码并执行。

Import-Module SQLPS
#replace KERRIGAN with your instance　name
$instanceName = "KERRIGAN"　
$managedComputer = New-Object
'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' $instanceName
#list services　　　　
$managedComputer.Services | 
Select Name, Type, Status, DisplayName | 
Format-Table –AutoSize

你的结果类似于下面的截屏。

屏幕中列出的条目将根据你实例中安装和运行的属性而不同。

3．确保这些服务存在于你的服务器中。检查你的服务窗口。

安装在系统中的服务可以使用WMI查询。SQL Server的特定服务通过SMO的WMI ManagedComputer对象来展示。一些展示的属性包括：

另一个备选方案是通过使用Get-WMIObject获取SQL Server相关的服务。我们需要传入主机名，以及计算机管理命名空间的SQL Server WMI provider。对于SQL Server 2012，值为：

ROOT\Microsoft\SQLServer\ComputerManagement11

下面提供的代码用于获取服务。注意，在这里动态展示了WMI的命名空间。

$hostName = "KERRIGAN"
$namespace = Get-WMIObject -ComputerName $hostName -NameSpace root\
Microsoft\SQLServer -Class "__NAMESPACE" |
　　　　　　　　 Where Name -Like "ComputerManagement*" 
Get-WmiObject -ComputerName $hostname -Namespace "$($namespace. _
NAMESPACE)\$($namespace.Name)" -Class SqlService |
Select ServiceName

另一个不太准确地列出SQL Server相关服务的备选方案，如下面的代码片段：

#alterative - but less accurate
Get-Service *SQL*

它使用Get-Servcie cmdlet和基于服务名的过滤条件。它不太准确，因为这个cmdlet筛选所有的名字中含有SQL的进程，但不一定跟SQL Server相关。例如，如果你安装了MySQL，它将会被筛选出来。相反，这个cmdlet不会筛选出名字中不含有SQL的SQL Server相关服务，例如ReportServer。

这个方案描述了如何启动和停止SQL Server服务。

检查你的机器上安装了哪些SQL服务。打开“Start | Run”并输入services.msc。你将会看到类似如下屏幕。

让我们按照以下步骤来切换SQL Server服务的状态。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．添加如下代码。注意，这个代码在PowerShell V2和V3中都起作用。

$Verbosepreference = "Continue"
$services = @("SQLBrowser", "ReportServer")
$hostName = "KERRIGAN"
$services | ForEach-Object {
　　 $service = Get-Service -Name $_
　　 if($service.Status -eq "Stopped")
　　 {
　　　　　Write-Verbose "Starting $($service.Name) ...."
　　　　　Start-Service -Name $service.Name
　　 }
　　 else 
　　 {
　　　　　Write-Verbose "Stopping $($service.Name) ...."
　　　　　Stop-Service -Name $service.Name
　　 }
}
$VerbosePreference = "SilentlyContinue"

3．执行并确认服务状态相应改变了。打开“Start | Run”并输入services.msc。

例如，在之前的示例中，SQL Browser和ReportServer初始在运行状态。一旦这个脚本执行后，两个服务都停止了。

在这个方案中，我们选取了两个服务SQLBrowser和ReportServer，我们操作并将它们保存到数组中。

$services = @("SQLBrowser","ReportServer")

我们通过管道将数组内容传入到Foreach-Object cmdlet，我们可以决定怎么操作每个服务。对于我们来说，如果该服务停止了，我们希望启动它。否则，停止它。注意，这个脚本在PowerShell V2和V3下都起作用。

$services | ForEach-Object {
　　 $service = Get-Service -Name $_
　　 if($service.Status -eq "Stopped")
　　 {
　　　　　 Write-Verbose "Starting $($service.Name) ...."
　　　　　 Start-Service -Name $service.Name
　　 }
　　 else 
　　 {
　　　　　 Write-Verbose "Stopping $($service.Name) ...."
　　　　　 Stop-Service -Name $service.Name
　　 }
}

你可能也想决定依赖的服务，或者依赖于某个特定服务的服务。你会考虑同步启动/停止这些依赖于主服务的服务。

为了识别依赖的服务，你可以使用System.ServiceProcess.ServiceController类的DependentServices属性。

$services | ForEach-Object {
　　 $service = Get-Service -Name $_
　　 Write-Verbose "Services Dependent on $($service.Name)" 
　　 $service.DependentServices | Select Name
}

下面的列表显示了System.ServiceProcess.ServiceController类的属性和方法，通过Get-Service cmdlet产生。

另一个备选方案是使用Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer类来操作SQL Server服务。注意，以下代码在PowerShell V2和V3中都起作用。

Import-Module SQLPS -DisableNameChecking
#list services you want to start/stop here
$services = @("SQLBrowser", "ReportServer")
$instanceName = "KERRIGAN"　
$managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.
ManagedComputer' $instanceName　　　
#go through each service and toggle the state
$services | ForEach-Object {
　　 $service = $managedComputer.Services[$_]
　　 switch($service.ServiceState)
　　 {
　　　　　"Running" 
{ 
　　　　　　　Write-Verbose "Stopping $($service.Name)"　
　　　　　　　$service.Stop() 
　　　　　}
　　　　　"Stopped" 
　　　　　{ 
　　　　　　　Write-Verbose "Starting $($service.Name)"　
　　　　　　　$service.Start() 
　　　　　}
　　 }
}

使用Smo.Wmi.ManagedComputer对象时，你可以简单使用类的Stop方法和Start方法来停止和启动服务。

下面的列表显示了Smo.Wmi.ManagedComputer类的属性和方法。

浏览可用的cmdlets可以帮助管理和维护服务，使用如下命令：

Get-Command -Name *Service* -CommandType Cmdlet -ModuleName 
*PowerShell*

下面列举出了名字中含有服务的cmdlets。

所有的这些cmdlets都与Windows服务相关，除了WebServiceProxy，它在MSDN中描述为一个用于创建Web服务代理对象的cmdlet，你可以在Windows PowerShell中使用和管理Web服务。

这里有一个主要的对比，关于Microsoft.SqlServer.Management.Smo.Wmi.Managed Computer对象的服务相关的cmdlets和可用方法，这些服务在本方案中被讨论。

服务方法

服务相关cmdlets

Start()

Start-Service

Stop()

Stop-Service

Continue()

Resume-Service

Pause()

Suspend-Service

Refresh()

Restart-Service

注意服务类的方法和服务相关的cmdlets不一定是一一对应的关系。例如，Restart-Service cmdlet，就没有Restart方法。

尽管这没必要敲响警钟。可能一些方法或者cmdlets会丢失，但请注意，PowerShell是一个丰富的脚本平台和语言。除了它自己的cmdlets，它涵盖了整个.NET平台。你可以在.NET平台做的，你也可以用PowerShell实现。即便当你看到一些特别的类和对象，你认为不可行，很有可能已经有了能做相同任务的cmdlet，反之亦然。如果你仍找不到理想的解决方案，你可以创建自己的类、模块、cmdlet或函数。

本方案讲述如何使用PowerShell列出SQL Server可配置和不可配置的实例设置。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell ISE。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

为了浏览在SMO服务器下的成员和方法，在PowerShell V3中使用如下代码片段。

#Explore: get all properties available for a server object
#http://msdn.microsoft.com/en-us/library/ms212724.aspx
$server | Get-Member | Where MemberType -eq "Property"

在PowerShell V2中，你需要稍微修改下语法。

$server | Get-Member | Where {$_.MemberType -eq "Property"}
#The Information class lists nonconfigrable instance settings, 
#like BuildNumber, OSVersion, ProductLevel etc
#Also includes settings specified during install
$server.Information.Properties | 
Select Name, Value | 
Format-Table –AutoSize

3．接下来，让我们看看Settings类。

#The Settings lists some instance level configurable settings, 
#like LoginMode, BackupDirectory etc
$server.Settings.Properties | 
Select Name, Value | 
Format-Table –AutoSize

4．UserOption类列出用户特定选项。

#The UserOptions include options that can be set for user 
#connections, for example
#AnsiPadding, AnsiNulls, NoCount, QuotedIdentifier
$server.UserOptions.Properties | 
Select Name, Value | 
Format-Table –AutoSize

5．Configuration类包含实例的特定设置，类似于你运行sp_configure所看到的。

#The Configuration class contains instance specific settings, 
#like AgentXPs, clr enabled, xp_cmdshell
#You will normally see this when you run 
#the stored procedure sp_configure
$server.Configuration.Properties | 
Select DisplayName, Description, RunValue, ConfigValue |　
Format-Table –AutoSize

大多数SQL Server设置和配置都可以通过SMO或WMI来展示，可以通过编程方式来获得这些值。

访问配置详细信息的核心是SMO服务器类。这个类展示了SQL Server实例的属性，一些是可配置的，而一些不可配置。

为了创建SMO服务器类，你需要知道你的实例名，并传递给以下变量。

#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.
Server -ArgumentList $instanceName

以下是四个主要的属性，用于存储在本方案中所看到的设置或配置。

服务器属性

描述

Information

包括不可配置的实例设置，例如BuildNumber、Edition、OSVersion和ProductLevel， 也包含特别在安装期间的设置，例如Collation、MasterDBPath和MasterDBLogPath。

Settings

列出实例级别的可配置设置，录入LoginMode和BackupDirectory。

UserOptions

包含用于用户连接的可设置选项，例如AnsiWarnings、AnsiNulls、AnsiPadding和NoCount。

Configuration

实例的特定设置，包括AgentXPs、remote access、clr enabled和xp_cmdshell，这些可以通过运行系统存储过程sp_configure看到并设置。

 
http://msdn.microsoft.com/en-us/library/ms212724.aspx

本方案讲述如何使用PowerShell修改实例配置设置。

在本方案，你将会：

让我们用PowerShell修改一些SQL Server设置。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下脚本并运行。

<#
run value vs config value
config_value," is what the setting has been set to (but may or 
may not be what SQL Server is actually running now. Some settings 
don't go into effect until SQL Server has been restarted, or 
until the RECONFIGURE WITH OVERRIDE option has been run, as 
appropriate.) And the last column, "run_value," is the value of 
the setting currently in effect.
#>
#change FillFactor
$server.Configuration.FillFactor.ConfigValue = 60
#enable SQL Server Agent extended stored procedures
$server.Configuration.AgentXPsEnabled.ConfigValue = 1
#change minimum server memory to 500MB; MB is default
$server.Configuration.MinServerMemory.ConfigValue = 500
$server.Configuration.Alter()
#confirm changes
$server.Configuration.Properties | 
Select DisplayName, ConfigValue | 
Format-Table –AutoSize
#change authentication mode
$server.Settings.LoginMode
[Microsoft.SqlServer.Management.Smo.ServerLoginMode]::Mixed
$server.Alter()
#confirm changes
$server.settings.LoginMode

4．确认修改。

验证fill factor。

（1）打开SSMS。

（2）连接到实例。

（3）右键实例并选择“Proerties”。

（4）打开“Database Settings”，检查“fill factor”是否已经修改。

启用SQL Server Agent扩展存储过程的方法是激活SQL Server Agnet。确认SQL Server Agent被启用。

（1）打开SSMS。

（2）连接到实例。

（3）检查你修改的实例的SQL Server Agent是否正在运行。

验证Minimum server memory。

（1）打开SSMS。

（2）右键实例选择“Properties”。

（3）打开“Memory”菜单，查看是否修改为你设置的值。

验证authentication mode。

（1）打开SSMS。

（2）连接到实例。

（3）右键实例并选择“Properties”。

（4）打开“Security”，检查实例是否设置为“SQL Server and Windows Authentication mode”。

根据你需要修改的服务器属性，你需要决定去访问哪些类：Settings、UserOptions或Configuration。

一旦你决定了需要修改的类和属性，可以通过调用Alter方法修改值。

#to make Configuration changes permanent
$server.Configuration.Alter()
#to make Settings changes permanent
$server.Alter()

当你运行sp_configure时，可以看到像下面的run_value和config_value的结果。

run_value和config_value常常容易混淆。Config_value是设置的值。Run_value是SQL Server当前正使用的值。通常，一个新值被设置（config_value），但是直到实例重启才会被SQL Server所使用。

本方案讲述如何基于查找字符串通过PowerShell查找数据库对象。

在这个练习中，我们将使用AdventureWorks2008R2查找名称中含有“Product”的SQL Server数据库对象。

为了清楚知道获得什么结果，在SSMS中运行如下脚本。

USE AdventureWorks2008R2
GO
SELECT 
 * 
FROM 
 sys.objects
WHERE 
 name LIKE '%Product%'
 -- filter table level objects only
 AND [type] NOT IN ('C', 'D', 'PK', 'F') 
ORDER BY 
 [type]

将会得到23行结果，记住这个数字。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下脚本并运行。注意，下面的脚本将只在PowerShell V3下起作用，因为使用的是简化的Where cmdlet。如果你想在PowerShell V2中使用，用V2的写法替代Where语法。

$databaseName = "AdventureWorks2008R2"
$db = $server.Databases[$databaseName]
#what keyword are we looking for?
$searchString = "Product"
#create empty array, we will store results here
$results = @()
#now we will loop through all database SMO 
#properties and look of objects that match
#the search string
#note we are explicitly excluding Federations, because
#this throws an error
$db | 
Get-Member -MemberType Property |
Where Definition -Like "*Smo*" | 
Where Definition -NotLike "*Federation*" | 
ForEach-Object {
　　 $type = $_.Name
　　 $db.$type | 
　　 Where Name -Like "*$searchstring*" |
　　 ForEach-Object {
　　　　　$result = New-Object -Type PSObject -Prop @{ 

"ObjectType"=$type.Replace("Microsoft.SqlServer.Management.Smo.", "")
　　　　　　　　　　　　　"ObjectName"=$_.Name　
　　　　　　　　}
　　　　　 $results += $result　　　　　　
　　　}
}
#display results
$results
#export results to csv file
$file = "C:\Temp\SearchResults.csv"
$results | Export-Csv -Path $file –NoTypeInformation
#display file contents
notepad $file

结果如下：

创建SMO服务器对象后，我们需要创建一个AdventureWorks2008R2数据库的SMO数据库句柄。

$db | 
Get-Member -MemberType Property |
Where Definition -Like "*Smo*" | 
Where Definition -NotLike "*Federation*" | 
ForEach-Object {
　　 $type = $_.Name
　　 $db.$type | 
　　 Where Name -Like "*$searchstring*" |
　　 ForEach-Object {
　　　　　$result = New-Object -Type PSObject -Prop @{ 
　　　　　　　　　　　　"ObjectType"=$type.Replace("Microsoft.SqlServer.
Management.Smo.", "")
　　　　　　　　　　　　"ObjectName"=$_.Name　 
　　　　　　　　　　} 
　　　　 $results += $result　　　　　　 
　　　}
}
$databasename = "AdventureWorks2008R2"
$db = $server.Databases[$databasename]
#export results to csv file
$file = "C:\Temp\SearchResults.csv"
$results | Export-Csv -Path $file -NoTypeInformation
#display file contents
notepad $file

我们也定义了查找字符串。我们的目的是获得名字中含有“Product”的所有数据库对象。

#what keyword are we looking for?
$searchString = "Product"

我们也创建一个空的数据，用于存储查询结果的记录。完成后，我们将以表格的形式显示最后的结果。

$results = @()

我们将会浏览所有的数据库相关的SMO属性，查找包含了该关键字的对象。注意，下面的脚本将只会在PowerShell V3中起作用，因为使用了简化的Where cmdlet。如果你想使用PowerShell V2，使用V2的写法替代Where语法。

#now we will loop through all database SMO 
#properties and look of objects that match
#the search string
#note we are explicitly excluding Federations, because
#this throws an error
$db | 
Get-Member -MemberType Property |
Where Definition -Like "*Smo*" | 
Where Definition -NotLike "*Federation*" | 
ForEach-Object {
　　 $type = $_.Name
　　 $db.$type | 
　　 Where Name -Like "*$searchstring*" |
　　 ForEach-Object {
　　　　　 $result = New-Object -Type PSObject -Prop @{ 
　　　　　　　　　　　　　　"ObjectType"=$type.Replace("Microsoft.SqlServer.
Management.Smo.", "")
　　　　　　　　　　　　　　"ObjectName"=$_.Name　
　　　　　　　　　　} 
　　　　$results += $result　　　　　 
　　 }
}

在循环中，我们解析并创建结果。

第一部分探测每一个属性，检查名字中是否包含我们的查询字符串。

$db | 
Get-Member -MemberType Property |
Where Definition -Like "*Smo*" | 
Where Definition -NotLike "*Federation*" | 
ForEach-Object {
　　 $type = $_.Name
　　 $db.$type | 
　　 Where Name -Like "*$searchstring*" |
　　 ForEach-Object {
　　　　　$result = New-Object -Type PSObject -Prop @{ 
　　　　　　　　　　　　"ObjectType"=$type.Replace("Microsoft.SqlServer.
Management.Smo.", "")
　　　　　　　　　　　　"ObjectName"=$_.Name　 
　　　　　　　　　　} 
　　　　　$results += $result　　　　　
　　　}
}

注意，传递给外层的Where-Object cmdlets（这里简化为Where，只在PowerShell V3中支持）有两个条件，如下所示。

另一种方法是使用SMO数据库变量$db的EnumObjects方法来列举对象。

$searchString = "Product"
$db.EnumObjects() |
Where Name -Like "*$searchString*" |
Select DatabaseObjectTypes, Name |
Format-Table –AutoSize

是的，还有另外一种备选方案。这个更长且没有那么灵活，但仍能获得所要的结果。你可以通过逐一遍历$db对象属性来查找匹配字符串的对象。

#long version is to enumerate explicitly each object type
$db.Tables | Where Name -Like "*$searchstring*"
$db.StoredProcedures　| Where Name -Like "*$searchstring*" 
$db.Triggers　| Where Name -Like "*$searchstring*"
$db.UserDefinedFunctions　| Where Name -Like "*$searchstring*"
#etc

这是有用的，如果你准确知道你要查找的对象类型，将会更快。

本方案将讲述使用PowerShell创建带有默认属性的数据库。

在这个例子中，我们将会创建一个叫做TestDB的数据库，确保该数据库不在当前实例中。

作为参照，与该任务等价的T-SQL代码如下所示。

CREATE DATABASE TestDB

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下脚本并运行。

#database TestDB with default settings
#assumption is that this database does not yet exist
$dbName = "TestDB"
$db = New-Object –TypeName Microsoft.SqlServer.Management.Smo.Database($server,
$dbName)
$db.Create()
#to confirm, list databases in your instance
$server.Databases | 
Select Name, Status, Owner, CreateDate

使用SMO和PowerShell创建数据库有两个关键步骤：创建一个SMO服务器对象和创建一个SMO数据库对象。

$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.
Server -ArgumentList $instanceName
$dbName = "TestDB"
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.
Database($server, $dbName)

SMO Database构造器需要SMO服务器句柄和一个数据库对象。最后的操作是调用数据库对象的Create方法。

$db.Create()

很多SMO对象适用这个方法。你将会在本章的很多方案中再次看到Create方法。

本方案将讲述如何使用SMO和PowerShell修改数据库属性。

根据创建数据库方案中的步骤创建一个名为TestDB的数据库。

使用TestDB，我们将：

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加以下脚本并运行。

#database
$dbName = "TestDB"
#we are going to assume db exists
$db = $server.Databases[$dbName]
#DatabaseOptions
#change ANSI NULLS and ANSI PADDING
$db.DatabaseOptions.AnsiNullsEnabled = $false
$db.DatabaseOptions.AnsiPaddingEnabled = $false
#Change database access
#DatabaseUserAccess enum values: multiple, restricted, single
$db.DatabaseOptions.UserAccess
[Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]::Restricted
$db.Alter()
#some options are not available through the 
#DatabaseOptions property
#so we will need to access the database object directly
#change compatiblity level to SQL Server 2005
#available CompatibilityLevel values are from 
#Version 6.5 ('Version65') all the way to SQL 
#Server 2012 ('Version110')
#however Version80 is not a valid compatibility option 
#for SQL Server 2012
$db.AutoUpdateStatisticsEnabled = $true
$db.CompatibilityLevel = [Microsoft.SqlServer.Management.Smo.
CompatibilityLevel]::Version90
$db.Alter()
#set to readonly
$db.DatabaseOptions.ReadOnly = $true
$db.Alter()

4．确认修改。

开始确认。

（1）打开SSMS。

（2）连接到实例。

你立刻会注意到在“Object Explorer”中你的数据库变为灰色，并且它的状态修改为“（Restricted User/Read-Only）”。

确认ANSI NULLS、ANSI PADDING和Compatibility Level。

（1）右键“TestDB”数据库，并选择“Properties”。

（2）打开“Options”窗口，检查对应的选项是否被修改。

为了修改数据库属性，我们需要创建该数据库的SMO句柄。

#we are going to assume db exists
$db = $server.Databases[$dbName]

之后，你需要调查哪个属性包含了你需要修改的设置。例如，ANSI NULLS、ANSI WARNINGS、数据库访问限制选项和Read Only在你的数据库对象的DatabaseOptions属性中是可用的。

6ly = $true

AutoUpdateStatisticsEnabled和CompatibilityLevel是它们自己的属性，可以直接从$db对象访问。

$db.AutoUpdateStatisticsEnabled = $true
$db.CompatibilityLevel = [Microsoft.SqlServer.Management.Smo.
CompatibilityLevel]::Version90

注意，对于SQL Server 2012，你可以设置兼容级别的最早版本是SQL Server 2005（Version 90）。

一旦你设置了新值，可以通过调用数据库对象的Alter方法来完成修改。

$db.Alter()

准确找出你要查找的设置位于哪个属性，就完成了工作的一半，所以，要熟悉你要修改的对象的属性。Technet和MSDN是不错的资源，有大量的书籍、文章和博客。然而，记住你手边有一个助手。Get-Member就是你需要的朋友。你可以像这样调用Get-Member cmdlet。

$db | Get-Member

本方案将讲述如何使用SMO和PowerShell删除数据库。

本任务假设你创建TestDB数据库。如果没有，按照创建数据库方案中的步骤创建一个。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下代码并运行。

$dbName = "TestDB"
#need to check if database exists, and if it does, drop it
$db = $server.Databases[$dbName]
if ($db)
{
　　　　　#we will use KillDatabase instead of Drop
　　　　　#Kill database will drop active connections before 
　　　　　#dropping the database
　　　　　$server.KillDatabase($dbName)
}

为了删除一个SMO服务器或数据库对象，你可以简单调用Drop方法。然而，如果你之前尝试过删除数据库，你可能已经经历过被活动的连接所阻塞。因此，我们选择了KillDatabase方法，在删除数据库之前将会杀掉活动连接。当你从“Object Explorer”中删除数据库时，这个选项在SSMS中也是可用的。右击一个数据库，将出现“Delete Object”窗口。在窗口底部有一个“Close existing connections”的复选框，选中复选框。

本方案展示了如何用编程方式修改SQL Server数据库所有者。

在这个任务中，假设你已经创建了TestDB数据库，已经在你的测试虚拟机VM中创建了Windows帐号QUERYWORKS\aterra。

如果还没有，按照创建数据库方案中的步骤创建一个TestDB数据库。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下脚本并运行。

#create database handle
$dbName = "TestDB"
$db = $server.Databases[$dbName]
#display current owner 
$db.Owner
#change owner
#SetOwner requires two parameters:
#loginName and overrideIfAlreadyUser
$db.SetOwner("QUERYWORKS\aterra", $true) 
#refresh db
$db.Refresh()
#check Owner value
$db.Owner

4．做好检查。

（1）打开SSMS。

（2）展开到TestDB数据库。

（3）右键选择“Properties”。

（4）选择“Options”。

用PowerShell修改数据库所有者是一个简短而直接的任务。首先，创建一个数据库句柄。

唯一的其他操作是调用Microsoft.SqlServer.Management.Smo.Database类的SetOwner方法，它需要两个参数：

OverrideIfAlreadyUser选项可被设置为true或false。如果设置为true，意味着当前登录的用户已经在目标数据库中存在了，该用户将被删除和重建作为所有者。如果设置为false，登录用户已经匹配到了该数据库，那SetOwner方法将输出错误。

本方案展示了如何用PowerShell和SMO创建表。

我们将在AdventureWorks2008R2数据库创建一个表Student，包含5列。为了更好地了解我们要完成什么，下面给出了等价的创建表的T-SQL脚本。

USE AdventureWorks2008R2
GO
CREATE TABLE [dbo].[Student](
[StudentID] [INT] IDENTITY(1,1) NOT NULL,
[FName] [VARCHAR](50) NULL,
[LName] [VARCHAR](50) NOT NULL,
[DateOfBirth] [DATETIME] NULL,
[Age]　AS (DATEPART(YEAR,GETDATE())-DATEPART(YEAR,[DateOfBirth])),
CONSTRAINT [PK_Student_StudentID] PRIMARY KEY CLUSTERED 
(
 [StudentID] ASC
)
)
GO

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．接下来，添加如下代码配置数据库和表名，如果存在，则删除表。

$dbName = "AdventureWorks2008R2"
$tableName = "Student"
$db = $server.Databases[$dbName]
$table = $db.Tables[$tableName]
#if table exists drop
if($table)
{
　　　$table.Drop()
}

4．添加下面的脚本创建表，并运行。

#table class on MSDN
#http://msdn.microsoft.com/en-us/library/ms220470.aspx
$table = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Table-ArgumentList
$db, $tableName
#column class on MSDN
#http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management smo.column.as
px
#column 1
$col1Name = "StudentID"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::Int;
$col1 =　New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column
-ArgumentList $table, $col1Name, $type
$col1.Nullable = $false
$col1.Identity = $true
$col1.IdentitySeed = 1
$col1.IdentityIncrement = 1
$table.Columns.Add($col1)
#column 2 – nullable
$col2Name = "FName"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::VarChar(50)
$col2 =　New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column
-ArgumentList　$table, $col2Name, $type
$col2.Nullable = $true
$table.Columns.Add($col2)
#column 3 - not nullable, with default value
$col3Name = "LName"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::VarChar(50)
$col3 =　New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column
-ArgumentList　$table, $col3Name, $type
$col3.Nullable = $false
$col3.AddDefaultConstraint("DF_Student_LName").Text = "'Doe'"
$table.Columns.Add($col3)
#column 4 - nullable, with default value
$col4Name = "DateOfBirth"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime;
$col4 =　New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column
-ArgumentList　$table, $col4Name, $type
$col4.Nullable = $true
$col4.AddDefaultConstraint("DF_Student_DateOfBirth").Text = "'1800-00-00'"
$table.Columns.Add($col4)
#column 5
$col5Name = "Age"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::Int;
$col5 =　New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column
-ArgumentList　$table, $col5Name, $type
$col5.Nullable = $false
$col5.Computed = $true
$col5.ComputedText = "YEAR(GETDATE()) - YEAR(DateOfBirth)";
$table.Columns.Add($col5)
$table.Create()

5．让StudentID作为主键。

#########################################
#make StudentID a clustered PK
#########################################
#note this is just a "placeholder" right now for PK
#no columns are added in this step
$PK=New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index –ArgumentList
$table,"PK_Student_StudentID"
$PK.IsClustered =$true
$PK.IndexKeyType =[Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriPrimaryKey
#identify columns part of the PK
$PKcol=New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn 
-ArgumentList $PK,$col1Name
$PK.IndexedColumns.Add($PKcol)
$PK.Create()

6．检查表是否被创建，有正确的列和约束。

（1）打开SSMS。

（2）展开AdventureWorks2008R2数据库的“Tables”。

（3）展开“dbo.Student”表的“Columns”、“Keys”、“Constraints”和“Indexes”。

创建表的第一步是创建一个SMO表对象。

$table = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Table -ArgumentList $db,
$tableName

之后，在调用Microsoft.SqlServer.Management.SMO.Table类的方法之前，把所有将要被定义的列逐一添加到表中。

让我们一步步来。创建列时，我们首先需要识别存储在列的数据类型和列属性。

在SMO中列数据类型定义为Microsoft.SqlServer.Management.SMO.DataType。在枚举中每个T-SQL数据类型是相当有代表性的。如下格式显示了如何使用数据类型。

[Microsoft.SqlServer.Management.SMO.DataType]::DataType

为了创建列，你需要定制表变量、数据类型和列名。

$col1Name = "StudentID"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::Int
$col1 =　New-Object -TypeName Microsoft.SqlServer.Management.SMO.
Column -ArgumentList $table, $col1Name, $type

通常列属性对列变量是可访问的。常用的属性包括：

例如：

#column 4 - nullable, with default value
$col4Name = "DateOfBirth"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime;
$col4 =　New-Object -TypeName Microsoft.SqlServer.Management.SMO.
Column -ArgumentList　$table, $col4Name, $type
$col4.Nullable = $true
$col4.AddDefaultConstraint("DF_Student_DateOfBirth").Text = "'1800-00-00'"

有些额外的属性会根据你选择的数据类型显示出来。例如，[Microsoft.SqlServer. Management.SMO.DataType]::Int将允许你确认是否标识列并让你设置种子和增量。[Microsoft.SqlServer. Management.SMO.DataType]::Varchar 允许你设置长度。

一旦你设置了属性，你可以将列添加到表。

$table.Columns.Add($col4)

当一切都设置好后，你可以调用表的Create方法。

$table.Create()

现在，创建一个主键时，你需要创建另外两个SMO对象。第一个是Index对象。对于这个对象，你需要指定索引的类型，是聚集索引还是非聚集索引。

$PK = New-Object -TypeName Microsoft.SqlServer.Management.SMO. 
Index -ArgumentList $table, "PK_Student_StudentID"　　　　
$PK.IsClustered = $true
$PK.IndexKeyType = [Microsoft.SqlServer.Management.SMO.
IndexKeyType]::DriPrimaryKey

第二个对象，IndexedColumn，指定索引中的列。

#identify columns part of the PK
$PKcol = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn 
-ArgumentList $PK, $col1Name

如果列为包含列，只需将IndexedColumn对象的IsIncluded属性设置为true。

一旦你创建了所有的索引列，你可以将它们添加到Index，并调用Index对象的Create方法：

$PK.IndexedColumns.Add($PKcol)
$PK.Create()

你可能会想我们刚才创建表的操作是一个非常长的方式。你想的没错，这个创建表的方式太繁琐。然而，注意，这只是另一种方式而已。如果你想创建表，并且T-SQL是一种更快的方法，那就用T-SQL。然而，知道如何使用PowerShell和SMO去完成工作可能只是多了一个工具，你可能需要动态创建表或更有扩展性—例如，你需要从多用户导入存储在Excel、CSV或XML文件中的定义。

本方案展示了如何用PowerShell和SMO创建视图。

我们将会使用AdventureWorks2008R2数据库的Person.Person表。

为了知道我们将会创建什么，我们给出了等价的T-SQL。

CREATE VIEW dbo.vwVCPerson
AS
SELECT 
　　TOP 100
 BusinessEntityID,
 LastName,
 FirstName
FROM 
 Person.Person
WHERE 
　　PersonType = 'IN'
ORDER BY
　　LastName
GO

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加下面的脚本并运行。

$dbName = "AdventureWorks2008R2"
$db = $server.Databases[$dbName]
$viewName = "vwVCPerson"
$view = $db.Views[$viewName]
#if view exists, drop it
if ($view)
{
　　　$view.Drop()
}
$view = New-Object -TypeName Microsoft.SqlServer.Management.SMO.View –ArgumentList
$db, $viewName, "dbo"
#TextMode = false meaning we are not
#going to explicitly write the CREATE VIEW header
$view.TextMode = $false
$view.TextBody = @"
SELECT 
　　 TOP 100
 BusinessEntityID,
 LastName,
 FirstName 
FROM 
 Person.Person
WHERE 
　　PersonType = 'IN'
ORDER BY
　　LastName
"@
$view.Create()

4．通过在PowerShell中运行下面的代码测试视图。

$result = Invoke-Sqlcmd `
-Query "SELECT * FROM vwVCPerson" `
-ServerInstance "$instanceName" `
-Database $dbName 
$result | Format-Table –AutoSize

5．检查是否已创建视图。打开SSMS，展开AdventureWorks2008R2数据库的Views。

为了使用SMO和PowerShell创建视图，你首先需要创建一个SMO View变量，它需要3个参数：database handle、view name和schema。

$view = New-Object -TypeName Microsoft.SqlServer.Management.SMO.View 
-ArgumentList $db, $viewName, "dbo"

可选的设置视图的所有者。

$view.Owner = "QUERYWORKS\aterra"

视图创建的核心是视图的定义。我们将TextMode属性设置为true或false。

$view.TextMode = $false
$view.TextBody = @"
SELECT 
　　 TOP 100
 BusinessEntityID,
 LastName,
 FirstName 
FROM 
 Person.Person
WHERE 
　　PersonType = 'IN'
ORDER BY
　　LastName
"@

如果将TextMode属性设置为false，意味着让SMO构造视图头部。

$view.TextMode = $false

如果将TextMode属性设置为true，意味着你需要定义视图的TextHeader属性。

$view.TextMode = $true
$view.TextHeader = "CREATE VIEW dbo.vwVCPerson AS "

当都准备好后，调用视图的Create方法。

$view.Create()

当创建数据库对象如视图、存储过程或者函数，你需要为对象的定义写代码。尽管技术上允许你将代码放在一行，但是将它们放在多行格式更具可读性。

为了嵌入代码块到PowerShell，你需要创建一个字符串。该字符串以@”开始并什么都不跟，并且以”@结束，且必须是该行开头的前两个字符。

$view.TextBody = @"
SELECT 
　　 TOP 100
 BusinessEntityID,
 LastName,
 FirstName 
FROM 
 Person.Person
WHERE 
　　PersonType = 'IN'
ORDER BY
　　LastName
"@

这个构造可能让你会向导C类型注释，以/*开头以*/结束，只是使用不同的字符。

本方案展示了如何用PowerShell和SMO创建加密的存储过程。

我们要用PowerShell创建的加密存储过程的等价T-SQL代码如下：

CREATE PROCEDURE [dbo].[uspGetPersonByLastName] @LastName [varchar]
(50)
WITH ENCRYPTION
AS
SELECT 
　　TOP 10 
　　BusinessEntityID,
　　LastName
FROM 
　　Person.Person
WHERE 
　　LastName = @LastName

按照如下步骤使用PowerShell创建存储过程uspGetPersonByLastName。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下脚本并运行。

$dbName = "AdventureWorks2008R2"
$db = $server.Databases[$dbName]
#storedProcedure class on MSDN: 
#http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.storedproc 
edure.aspx
$sprocName = "uspGetPersonByLastName"
$sproc = $db.StoredProcedures[$sprocName]
#if stored procedure exists, drop it
if ($sproc)
{
　　　$sproc.Drop()
}
$sproc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure
-ArgumentList $db, $sprocName
#TextMode = false means stored procedure header 
#is not editable as text
#otherwise our text will contain the CREATE PROC block
$sproc.TextMode = $false
$sproc.IsEncrypted = $true
$paramtype = [Microsoft.SqlServer.Management.SMO.Datatype]::VarChar(50);
$param = New-Object –TypeName
Microsoft.SqlServer.Management.SMO.StoredProcedureParameter –ArgumentList
$sproc,"@LastName",$paramtype
$sproc.Parameters.Add($param)
#Set the TextBody property to define the stored procedure. 
$sproc.TextBody =　@" 
SELECT 
　　TOP 10 
　　BusinessEntityID,
　　LastName
FROM 
　　Person.Person
WHERE 
　　LastName = @LastName
"@

# Create the stored procedure on the instance of SQL Server. 
$sproc.Create()
#if later on you need to change properties, can use the Alter method

4．检查是否存储过程已创建。

（1）打开SSMS。

（2）展开AdventureWorks2008R2数据库。

（3）展开“Programmability | Stored Procedures”。

（4）查看存储过程是否在这里。

5．在PowerShell中测试存储过程。在同一个会话中，输入如下代码并运行。

$lastName = "Abercrombie"
$result = Invoke-Sqlcmd `
-Query "EXEC uspGetPersonByLastName @LastName=`'$LastName`'" `
-ServerInstance "$instanceName" `
-Database $dbName 
$result | Format-Table –AutoSize

为了创建存储过程，首先需要初始化SMO StoredProcedure对象。创建这个对象时，需要传递数据库句柄和存储过程名作为参数。

$sproc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure
-ArgumentList $db, $sprocName

你可以设置存储过程对象的一些属性，如是否加密。

$sproc.IsEncrypted = $true

如果定制了TextMode = $true，你需要自己创建存储过程的头部。如果你有参数，这些将会在你的文本头部被定义，例如：

$sproc.TextMode = $true
$sproc.TextHeader = @"
CREATE PROCEDURE [dbo].[uspGetPersonByLastName]
 @LastName [varchar](50)
AS
"@

否则，如果TextMode = $false，技术上允许PowerShell自动生成头部，基于你给定的其他属性和参数设置。你也需要逐一创建参数对象并将它们添加到存储过程中。

$sproc.TextMode = $false
$paramtype = [Microsoft.SqlServer.Management.SMO.
Datatype]::VarChar(50);
$param = New-Object -TypeName Microsoft.SqlServer.Management.SMO.
StoredProcedureParameter -ArgumentList $sproc,"@LastName",$paramtype
$sproc.Parameters.Add($param)

当创建存储过程时，使用字符串设置存储过程对象的TextBody属性定义。

$sproc.TextBody =　@" 
SELECT 
　　TOP 10 
　　BusinessEntityID,
　　LastName
FROM 
　　Person.Person
WHERE 
　　LastName = @LastName
"@

一旦头部信息、定义和存储过程属性就位，你可以调用Create方法，将CREATEPROC语句发送到SQL Server，并创建存储过程。

# Create the stored procedure on the instance of SQL Server. 
$sproc.Create()

本方案描述了如何以编程方式用PowerShell和SMO在SQL Server中创建触发器。

在本方案中，我们将在AdventureWorks2008R2数据库中使用Person.Person表。我们将创建一个AFTER触发器，只显示插入和删除的记录。

下面是等价的完成该任务的T-SQL脚本。

CREATE TRIGGER [Person].[tr_u_Person] 
ON [Person].[Person] 
AFTER　UPDATE 
AS 
　SELECT 
　　　GETDATE() AS UpdatedOn,
　　　SYSTEM_USER AS UpdatedBy,
　　　i.LastName AS NewLastName,
　　　i.FirstName AS NewFirstName,
　　　d.LastName AS OldLastName,
　　　d.FirstName AS OldFirstName
　FROM 
　　　inserted i
　　　INNER JOIN deleted d
　　　ON i.BusinessEntityID = d.BusinessEntityID

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下代码并运行。

$dbName = "AdventureWorks2008R2"
$db = $server.Databases[$dbName]
$tableName = "Person"
$schemaName = "Person" 
#get a handle to the Person.Person table
$table = $db.Tables | 
　　　　　　　Where Schema -Like "$schemaName" |　
　　　　　　　Where Name -Like "$tableName"
$triggerName = "tr_u_Person";
#note here we need to check triggers attached to table
$trigger = $table.Triggers[$triggerName]
#if trigger exists, drop it
if ($trigger)
{
　　　$trigger.Drop()
}
$trigger = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Trigger
-ArgumentList $table, $triggerName 
$trigger.TextMode = $false
#this is just an update trigger
$trigger.Insert = $false
$trigger.Update = $true
$trigger.Delete = $false
#3 options for ActivationOrder: First, Last, None
$trigger.InsertOrder = [Microsoft.SqlServer.Management.SMO.Agent.ActivationOrder]::None
$trigger.ImplementationType = 
[Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql
#simple example
$trigger.TextBody = @"
　　SELECT 
　　　　GETDATE() AS UpdatedOn,
　　　　SYSTEM_USER AS UpdatedBy,
　　　　i.LastName AS NewLastName,
　　　　i.FirstName AS NewFirstName,
　　　　d.LastName AS OldLastName,
　　　　d.FirstName AS OldFirstName
　　FROM 
　　　　inserted i
　　　　INNER JOIN deleted d
　　　　ON i.BusinessEntityID = d.BusinessEntityID
"@
$trigger.Create()

4．检查是否触发器已经创建。打开SSMS。

5．使用PowerShell测试触发器。

$firstName = "Frankk"
$result = Invoke-Sqlcmd `
-Query "UPDATE Person.Person　SET FirstName = `'$firstName`' WHERE 
BusinessEntityID = 2081 " `
-ServerInstance "$instanceName" `
-Database $dbName 
$result | Format-Table -AutoSize

类似结果如下：

这个代码相当长，所以我们将在这里拆分它。

为了创建触发器，首先要创建一个实例和数据库的参照。就像我们在本章的大多数方案中所做的一样，假设你跳过了之前的方案。

触发器是绑定到表或视图的。你需要创建一个标量，指向你想要触发的表。

$tableName = "Person"
$schemaName = "Person" 
$table = $db.Tables | 
　　　　　　　Where Schema -Like "$schemaName" |　
　　　　　　　Where Name -Like "$tableName"

对于本方案，如果触发器已存在，我们将删除它。

$trigger = $table.Triggers[$triggerName]
#if trigger exists, drop it
if ($trigger)
{
　　　$trigger.Drop()
}

接下来，我们需要创建一个SMO trigger对象。

$trigger = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Trigger –ArgumentList
$table, $triggerName

接下来，设置TextMode属性。如果设置为true，意味着你必须自己定义触发器的头部信息。否则，SMO将为你自动生成触发器的头部信息。

$trigger.TextMode = $false

你也需要定义DML触发器的类型：insert、update或delete触发器。我们这里是一个update触发器。

#this is just an update trigger
$trigger.Insert = $false
$trigger.Update = $true
$trigger.Delete = $false

你也可以定义触发器顺序。默认情况下，不能保证触发器将会以什么顺序被SQL Server运行，但是可以设置为First或Last。在我们的例子中，我们保留默认值，但仍明确将它定义为可读。

#3 options for ActivationOrder: First, Last, None
$trigger.InsertOrder = [Microsoft.SqlServer.Management.SMO.Agent.ActivationOrder]::None

我们的触发器是T-SQL触发器。SQL Server SMO也支持SQLCLR触发器。

$trigger.ImplementationType =
[Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql

为了明确触发器的定义，我们将会设置触发器的TextBody属性值。你可以使用字符串为TextBody属性分配触发器的代码块。

#simple example
$trigger.TextBody = @"
　　SELECT 
　　　　GETDATE() AS UpdatedOn,
　　　　SYSTEM_USER AS UpdatedBy,
　　　　i.LastName AS NewLastName,
　　　　i.FirstName AS NewFirstName,
　　　　d.LastName AS OldLastName,
　　　　d.FirstName AS OldFirstName
　　FROM 
　　　　inserted i
　　　　INNER JOIN deleted d
　　　　ON i.BusinessEntityID = d.BusinessEntityID
"@

当就绪后，调用触发器的Create()方法。

$trigger.Create()

本方案描述了如何用PowerShell和SMO创建一个包含列的非聚集索引。

我们将使用AdventureWorks2008R2数据库的Person.Person表。我们将在FirstName和LastName上创建一个非聚集索引，包含MiddleName列。这个任务的等价T-SQL语句为：

CREATE NONCLUSTERED INDEX [idxLastNameFirstName] 
ON [Person].[Person]
(
 [LastName] ASC,
 [FirstName] ASC
)
INCLUDE ( [MiddleName]) 
GO

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下脚本并运行。

$dbName = "AdventureWorks2008R2"
$db = $server.Databases[$dbName]
$tableName = "Person"
$schemaName = "Person" 
$table = $db.Tables | 
　　　　　　 Where Schema -Like "$schemaName" |
Where Name -Like "$tableName"
$indexName = "idxLastNameFirstName"
$index = $table.Indexes[$indexName]
#if stored procedure exists, drop it
if ($index)
{
　　　$index.Drop()
}
$index = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index
-ArgumentList $table, $indexName
#first index column, by default sorted ascending
$idxCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn
-ArgumentList $index, "LastName", $false 
$index.IndexedColumns.Add($idxCol1)
#second index column, by default sorted ascending
$idxCol2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn
-ArgumentList $index, "FirstName", $false 
$index.IndexedColumns.Add($idxCol2)
#included column
$inclCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn 
-ArgumentList $index, "MiddleName"
$inclCol1.IsIncluded = $true
$index.IndexedColumns.Add($inclCol1)
#Set the index properties. 
<#
None　　　　　　　　　 - no constraint
DriPrimaryKey - primary key
DriUniqueKey - unique constraint
#>
$index.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::None 
$index.IsClustered = $false
$index.FillFactor = 70
#Create the index on the instance of SQL Server. 
$index.Create()

4．检查这个索引已经创建。打开SSMS。

创建索引的第一步是创建一个SMO index对象，它需要表/视图的句柄和索引名称。

$index.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::None 
$index.IsClustered = $false
$index.FillFactor = 70
$index = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index –ArgumentList
$table, $indexName
#Create the index on the instance of SQL Server. 
$index.Create()

下一步是使用Microsoft.SqlServer.Management.SMO.Index类的IndexedColumn属性设置所有的索引列。

#first index column
$idxCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn
-ArgumentList $index, "LastName", $false; #sort asc
$index.IndexedColumns.Add($idxCol1)
#second index column
$idxCol2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn
-ArgumentList $index, "FirstName", $false; #sort asc
$index.IndexedColumns.Add($idxCol2)

你也可以添加包含列，换句话说，包含列打上了索引的“标签”，但不是索引列的一部分：

#included column
$inclCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn
-ArgumentList $index, "MiddleName"
$inclCol1.IsIncluded = $true
$index.IndexedColumns.Add($inclCol1)

索引的类型可以指定为Microsoft.SqlServer.Management.SMO.IndexedColumn类的IndexKey Type属性，它接受3个可能的值：

SMO索引对象也支持不同类型的索引。

索引类型

设定值

Filtered

HasFilter FilterDefinition

FullText

IsFullTextKey = $true

XML

IsXMLIndex = $true

Spatial

IsSpatialIndex = $true

关于索引选项的更多信息，查看MSDN文档关于SMO索引部分。

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.index.aspx

本方案描述了如何用PowerShell执行手写的查询语句或SQL脚本。

在C:\Temp目录下创建一个文件SampleScript.sql。它包含：

SELECT * 
FROM Person.Person

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下脚本并运行。

$dbName = "AdventureWorks2008R2"
$db = $server.Databases[$dbName]
#execute a passthrough query, and export to a CSV file
Invoke-Sqlcmd `
-Query "SELECT * FROM Person.Person" `
-ServerInstance "$instanceName" `
-Database $dbName | 
Export-Csv -LiteralPath "C:\Temp\ResultsFromPassThrough.csv" `
-NoTypeInformation
#execute the SampleScript.sql, and display results to screen 
Invoke-SqlCmd `
-InputFile "C:\Temp\SampleScript.sql" `
-ServerInstance "$instanceName" `
-Database $dbName | 
Select FirstName, LastName, ModifiedDate | 
Format-Table

开始学习Invoke-Sqlcmd cmdlet。我们将会在本书中大量使用。

你可以使用这个cmdlet运行T-SQL代码或脚本，以及被SQLCMD工具集支持的命令，你也可以运行XQuery代码。Invoke-Sqlcmd是你的全能SQL工具集cmdlet。

获得更多关于Invoke-Sqlcmd的信息，请使用Get-Help cmdlet。

Get-Help Invoke-Sqlcmd –Full

在本方案中，我们看到两种使用Invoke-Sqlcmd的方法。第一种是定义一个执行的查询语句。对此，你可以使用-Query选项。

Get-Help Invoke-Sqlcmd -Full
In this recipe, we looked at two ways of using Invoke-Sqlcmd. The first is by specifying a 
query to run. For this, you should use the –Query option:
#execute a passthrough query, and export to a CSV file
Invoke-Sqlcmd `
-Query "SELECT * FROM Person.Person" `
-ServerInstance "$instanceName" `
-Database $dbName | Export-Csv -LiteralPath "C:\Temp\ResultsFromPassThrough.csv" `
-NoTypeInformation

第二种方法，您需要运行一个SQL脚本，将参数切换为-InputFile。

#execute the SampleScript.sql, and display results to screen 
Invoke-SqlCmd `
-InputFile "C:\Temp\SampleScript.sql" `
-ServerInstance "$instanceName" `
-Database $dbName | Select FirstName, LastName, ModifiedDate | Format-Table

本方案描述了如何用PowerShell和Invoke-Sqlcmd将表内容导出到CSV文件。

访问AdventureWorks2008R2数据库，我们将使用Person.Person表。

如果你的系统中没有C:\Temp目录，创建C:\Temp目录。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下脚本并运行。

#database handle
$dbName = "AdventureWorks2008R2"
$db = $server.Databases[$dbName]
#export file name
$exportfile = "C:\Temp\Person_Person.csv"
$query = @"
SELECT 
　 * 
FROM 
　 Person.Person
"@
Invoke-Sqlcmd -Query $query -ServerInstance "$instanceName" -Database $dbName |
Export-Csv -LiteralPath $exportfile –NoTypeInformation

在这个方案中，我们将结果导出到CSV文件。本方案的导出方法中有两个核心部分。

第一部分是使用Invoke-Sqlcmd cmdlet执行查询。我们指定实例和数据库，并通过该cmdlet发送查询语句给SQL Server。

Invoke-Sqlcmd -Query $query -ServerInstance "$instanceName" -Database $dbName |
Export-Csv -LiteralPath $exportfile -NoTypeInformation

第二部分是通过管道将结果传输到Export-Csv cmdlet，并指定存储结果的文件名。我们也指定了-NoTypeInformation，所以在文件的第一行我们将忽略#TYPE .NET信息类型。

Invoke-Sqlcmd -Query $query -ServerInstance "$instanceName" -Database $dbName |
Export-Csv -LiteralPath $exportfile –NoTypeInformation

本方案描述了如何用PowerShell和bcp将表内容导出到CSV文件。

确保您可以访问AdventureWorks2008R2数据库。我们将Person.Person表导出到一个时间戳标记的文本文件中，通过管道符号（|）分割。

创建C:\Temp\Exports目录，如果还没有在你的系统中创建。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．添加如下脚本，并运行。

$server = "KERRIGAN"
$table = "AdventureWorks2008R2.Person.Person"
$curdate = Get-Date -Format "yyyy-MM-dd_hmmtt"
$foldername = "C:\Temp\Exports\"
#format file name
$formatfilename = "$($table)_$($curdate).fmt"
#export file name
$exportfilename = "$($table)_$($curdate).csv"
$destination_exportfilename = "$($foldername)$($exportfilename)"
$destination_formatfilename = "$($foldername)$($formatfilename)"
#command to generate format file
$cmdformatfile = "bcp $table format nul -T -c -t `"|`" -r `"\n`" 
-f `"$($destination_formatfilename)`" -S$($server)"
#command to generate the export file
$cmdexport = "bcp $($table) out `"$($destination_exportfilename)`" 
-S$($server) -T -f `"$destination_formatfilename`""
<#
$cmdformatfile gives you something like this:
bcp AdventureWorks2008R2.Person.Person format nul -T -c -t "|" -r "\n" –f
"C:\Temp\Exports\AdventureWorks2008R2.Person.Person_2011-12-27_913PM.fmt" –S
KERRIGAN　
$cmdexport gives you something like this:
bcp AdventureWorks2008R2.Person.Person out
"C:\Temp\Exports\AdventureWorks20008R2.Person.Person_2011-12-27_913PM.csv" –S
KERRIGAN -T -c –f
"C:\Temp\Exports\AdventureWorks2008R2.Person.Person_2011-12-27_913PM.fmt"
#>
#run the format file command
Invoke-Expression $cmdformatfile
#delay 1 sec, give server some time to generate the format file
#sleep helps us avoid race conditions
Start-Sleep -s 1
#run the export command
Invoke-Expression $cmdexport　
#check the folder for generated file
explorer.exe $foldername

使用SQL Server的bcp命令从SQL Server导出记录可能更快。它的应用更广泛，因为bcp提供了更可扩展的导出格式。

bcp的默认导出格式是使用跳格键（\t）作为域分割符，使用回车换行（\r\n）作为换行分隔符。如果你想修改，需要创建并使用一个格式化文件来指定你想导出的格式。

在我们的方案中，我们首先用时间戳命名格式化文件和导出的文件。

$curdate = Get-Date -Format "yyyy-MM-dd_hmmtt"
$foldername = "C:\Temp\Exports\"
#format file name
$formatfilename = "$($table)_$($curdate).fmt"
#export file name
$exportfilename = "$($table)_$($curdate).csv"
$destination_exportfilename = "$($foldername)$($exportfilename)"
$destination_formatfilename = "$($foldername)$($formatfilename)"

然后，我们创建产生格式化文件的字符串。

#command to generate format file
$cmdformatfile = "bcp $table format nul -T -c -t `"|`" -r `"\n`" 
-f `"$($destination_formatfilename)`" -S$($server)"

注意，因为实际的命令需要使用双引号（”），当我们创建命令时，需要通过反引号（`）转义双引号。

创建的命令如下所示。

bcp AdventureWorks2008R2.Person.Person format nul -T -c -t "|" -r 
"\n" -f "C:\Temp\Exports\AdventureWorks2008R2.Person.Person_2011-12-27_913PM.fmt"
-SKERRIGAN

我们使用刚刚创建的格式化文件创建了导出记录的命令。

#command to generate the export file
$cmdexport = "bcp $($table) out `"$($destination_exportfilename)`" 
-S$($server) -T -f `"$destination_formatfilename`""

我们创建的命令如下。

bcp AdventureWorks2008R2.Person.Person out
"C:\Temp\Exports\AdventureWorks2008R2.Person.Person_2011-12-27_913PM.csv" -SKERRIGAN 
-T -f "C:\Temp\Exports\AdventureWorks2008R2.Person.Person_2011-12-27_913PM.fmt"

当包含命令的字符串完成之后，我们能使用Invoke-Expression cmdlet执行这个命令。我们首先运行格式化文件创建命令，然后使用Start-Sleep cmdlet停止1秒钟，确保已经创建了格式化文件，然后调用命令进行真正的导出工作。

#run the format file command
Invoke-Expression $cmdformatfile
#delay 1 sec, give server some time to generate 
#the format file
#sleep helps us avoid race conditions
Start-Sleep -s 1
#run the export command
Invoke-Expression $cmdexport

如果我们不等待，所有的命令有可能执行得更快，并且导出命令有可能在格式化文件产生好之前执行。这将导致错误，因为bcp命令不能发现格式化文件。

最后，我们打开“Windows Explorer”，可以看到生成的文件。

#check the folder for generated file
explorer.exe $foldername
http://msdn.microsoft.com/en-us/library/ms191516.aspx

本方案描述了如何用PowerShell和BULK INSERT将CSV文件导入SQL Server。

为了做导入测试，我们首先需要创建一个Person表，类似于AdventureWorks2008R2数据库的Person.Person表，简单地修改一下。

我们将在Test架构下创建，并移除一些约束，保持表简单且独立。

为了创建本练习中需要的表，我们打开SSMS，运行如下代码。

CREATE SCHEMA [Test]
GO
CREATE TABLE [Test].[Person](
 [BusinessEntityID] [int] NOT NULL PRIMARY KEY,
 [PersonType] [nchar](2) NOT NULL,
 [NameStyle] [dbo].[NameStyle] NOT NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [dbo].[Name] NOT NULL,
 [MiddleName] [dbo].[Name] NULL,
 [LastName] [dbo].[Name] NOT NULL,
 [Suffix] [nvarchar](10) NULL,
 [EmailPromotion] [int] NOT NULL,
 [AdditionalContactInfo] [xml] NULL,
 [Demographics] [xml] NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL　NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
)
GO

在本方案中，我们将导入AdventureWorks2008R2.Person.Person.csv文件，Packt网站提供了可下载资源。保存在目录C:\Temp\Exports下。

或者，创建一个CSV文件，像在使用bcp实施批量导出方案中所提到的，替换文件名。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．首先添加一些辅助函数。输入如下并执行。

Import-Module SQLPS -DisableNameChecking
function Import-Person {
<# 
.SYNOPSIS 
　　 Very simple function to get number 
　　 of records in Test.Person 
.NOTES 
　　 Author　　 : Donabel Santos 
.LINK 
　　 http://www.sqlmusings.com
#> 
param([string]$instanceName,[string]$dbName)
$query = @"
TRUNCATE TABLE Test.Person
GO
BULK INSERT AdventureWorks2008R2.Test.Person
　　FROM 'C:\Temp\Exports\AdventureWorks2008R2.Person.Person.csv'
　　WITH 
　　　 (
　　　　　 FIELDTERMINATOR ='|',
　　　　　 ROWTERMINATOR ='\n'
　　　 )
SELECT COUNT(*) AS NumRecords
FROM AdventureWorks2008R2.Test.Person
"@;
#check number of records
Invoke-Sqlcmd -Query $query `
-ServerInstance "$instanceName" `
-Database $dbName
}

3．现在在同一个会话中调用该函数。

$instanceName = "KERRIGAN"
$dbName = "AdventureWorks2008R2"
Import-Person $instanceName $dbName

使用BULK INSERT命令，从CSV或文本文件中将记录导入到SQL Server表，需要创建BULK INSERT T-SQL语句，并使用Invoke-Sqlcmd执行以下语句。

Invoke-Sqlcmd -Query $query `
-ServerInstance "$instanceName" `
-Database $dbName

然而，我们的做法不同于之前的方案。在本方案中，我们首先创建了一个函数，将所有核心导入任务括起来。

创建函数时，我们首先需要创建函数头部。

function Import-Person {

函数头部以function开始，然后跟随着函数名，以动词-名词的形式。函数体通过一对大括号括起来。

在函数头部后面，创建了注释信息，对头部进行注释。

<# 
.SYNOPSIS 
　　 Very simple function to get number　　　of records in Test.Person 
.NOTES 
　　 Author　　 : Donabel Santos 
.LINK 
　　 http://www.sqlmusings.com
#>

在PowerShell中，块注释以<#开始，以#>结束。此外，这是一个特别类型的块注释，可以在Get-Help中显示函数的注释。现在我们输入：

Get-Help Import-Person

你得到的结果与从其他的cmdlet中获得的帮助类似。

在函数头部和注释后面是参数。Import-Person函数接受两个参数：instance name和database name。

param([string]$instanceName,[string]$dbName)

参数定义之后是函数定义。我们创建一个字符串，保存T-SQL语句。

$query = @"
TRUNCATE TABLE Test.Person
GO
BULK INSERT AdventureWorks2008R2.Test.Person
　　FROM 'C:\Temp\Exports\AdventureWorks2008R2.Person.Person.csv'
　　WITH 
　　　 (
　　　　　 FIELDTERMINATOR ='|',
　　　　　 ROWTERMINATOR ='\n'
　　　 )
SELECT COUNT(*) AS NumRecords
FROM AdventureWorks2008R2.Test.Person
"@;

在创建查询后，我们将其传递给Invoke-Sqlcmd cmdlet，让它在SQL Server实例中执行。

Invoke-Sqlcmd -Query $query `
-ServerInstance "$instanceName" `
-Database $dbName

在PowerShell中，函数默认是本地域范围，但是当通过ISE运行时，将保持一个全局域范围。在我们的方案中，一旦你运行第一部分含有函数定义的脚本，你可以在当前会话中的任何时候调用函数。我们可以看到该函数简化了导入记录，我们只需要明确实例名、数据库名和Import-Person函数。

$instanceName = "KERRIGAN"
$dbName = "AdventureWorks2008R2"
Import-Person $instanceName $dbName

如果你使用Shell，想要函数能在全局域范围内访问，保存该脚本为.ps1文件，并点号加载它。另一个方法是添加函数名和global。

function global:Import-Person {

本方案描述了如何用PowerShell和bcp将CSV文件导入SQL Server。

为了测试导入，首先创建一个类似于AdventureWorks2008R2数据库的Person.Person表Person，简单修改下。我们创建Test架构，并移除一些约束，保持该表尽可能简单和独立。

如果Test.Person不存在你的环境中，让我们创建它。打开SSMS，运行如下代码。

CREATE SCHEMA [Test]
GO
CREATE TABLE [Test].[Person](
 [BusinessEntityID] [int] NOT NULL PRIMARY KEY,
 [PersonType] [nchar](2) NOT NULL,
 [NameStyle] [dbo].[NameStyle] NOT NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [dbo].[Name] NOT NULL,
 [MiddleName] [dbo].[Name] NULL,
 [LastName] [dbo].[Name] NOT NULL,
 [Suffix] [nvarchar](10) NULL,
 [EmailPromotion] [int] NOT NULL,
 [AdditionalContactInfo] [xml] NULL,
 [Demographics] [xml] NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL　NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
)
GO

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．首先添加一些辅助函数。输入如下并执行。

Import-Module SQLPS -DisableNameChecking
$instanceName = "KERRIGAN"
$dbName = "AdventureWorks2008R2"
function Truncate-Table {
<# 
.SYNOPSIS 
　　 Very simple function to truncate 
　　 records from Test.Person 
.NOTES 
　　 Author　　 : Donabel Santos 
.LINK 
　　 http://www.sqlmusings.com
#>
param([string]$instanceName,[string]$dbName)
$query = @"
TRUNCATE TABLE Test.Person
"@
#check number of records
Invoke-Sqlcmd -Query $query `
-ServerInstance $instanceName `
-Database $dbName
}
function Get-PersonCount {
<# 
.SYNOPSIS 
　　 Very simple function to get number 
　　 of records in Test.Person 
.NOTES 
　　 Author　　 : Donabel Santos 
.LINK 
　　 http://www.sqlmusings.com
#> 
param([string]$instanceName,[string]$dbName)
$query = @"
SELECT COUNT(*) AS NumRecords
FROM Test.Person
"@
#check number of records
Invoke-Sqlcmd -Query $query `
-ServerInstance $instanceName `
-Database $dbName
}

3．添加如下脚本并运行。

#let's clean up the Test.Person table first
Truncate-Table $instanceName $dbName
$server = "KERRIGAN"
$table = "AdventureWorks2008R2.Test.Person"
$importfile = "C:\Temp\Exports\AdventureWorks2008R2.Person.Person.csv"
#command to import from csv
$cmdimport = "bcp $($table) in `"$($importfile)`" -S$server -T -c -t `"|`" -r `"\n`" " 
<#
$cmdimport gives you something like this:
bcp AdventureWorks2008R2.Test.Person in
"C:\Temp\Exports\AdventureWorks2008R2.Person.Person.csv" -SKERRIGAN -T -c -t "|" -r 
"\n" 
#>
#run the import command
Invoke-Expression $cmdimport 
#delay 1 sec, give server some time to import records
#sleep helps us avoid race conditions
Start-Sleep -s 2
Get-PersonCount $instanceName $dbName

使用bcp实施批量导入是一个直接的任务——我们需要使用Invoke-Expression cmdlet并传入bcp命令。然而，在本方案中，我们整理了脚本，并以一对辅助函数开头。

第一个辅助函数Truncate-Table，是一个简单的用于清空Test.Person表的辅助函数，该表用于导入记录。该函数通过使用Invoke-Sqlcmd cmdlet将TRUNCATE TABLE命令传递给SQL Server。使用该函数时，只需调用。

Truncate-Table $instanceName $dbName

第二个辅助函数Get-PersonCount，只需返回导入到Test.Person表的记录行数。这也使用了Invoke-Sqlcmd cmdlet。调用该函数时，使用如下代码。

Get-PersonCount $instanceName $dbName

本方案的核心是根据创建的bcp导入命令。

$server = "KERRIGAN"
$table = "AdventureWorks2008R2.Test.Person"
$importfile = "C:\Temp\Exports\AdventureWorks2008R2.Person.Person.csv"
#command to import from csv
$cmdimport = "bcp " + $table + " in " + '"' + $importfile + '"' + " -S $server
-T -c -t `"|`" -r `"\n`" "

该bcp命令指向导入文件，它指定管道符号作为域分隔符，换行符为行分隔符。

bcp AdventureWorks2008R2.Test.Person in
"C:\Temp\Exports\AdventureWorks2008R2.Person.Person.csv" -T -c -t "|" -r "\n"

一旦命令被创建，我们需要传递给Invoke-Expression。

Invoke-Expression $cmdimport

我们也使用Start-Sleep cmdlet添加延迟，延迟间隔为2秒，在我们计数前可以INSERT。这是避免竞争的一种间单方式，但对于本方案的目的来说，已经足够了。


相关图书

SQL Server完全自学教程
SQL Server完全自学教程
SQL Server On Linux运维实战
SQL Server On Linux运维实战
Oracle PL/SQL程序设计（第6版）（上下册）
Oracle PL/SQL程序设计（第6版）（上下册）

相关文章

相关课程

异步社区-致力于优质IT知识的出版和分享

京ICP备05035396号-6

京公网安备11010602100305号

增值电信业务经营许可证（京B2-20201559）Powered by 人民邮电出版社▪出版云