33. PowerShell -- SQL Server的使用

q4561231

• 　　PowerShell --  SQL Server的使用 （1）
  

　　一、概述
　　SQL Server 2012 提供了一个名为 sqlps 的 Windows PowerShell 模块，该模块用于将 SQL Server 组件导入到 Windows PowerShell 2.0 环境或脚本中。sqlps 模块提供一组 SQL Server cmdlet ，支持各种操作，如运行包含 Transact-SQL 或 XQuery 语句的 sqlcmd 脚本。
　　二、加载模块
　　1、SQL Server 2008 R2 插件
　　从 SQL Server 2005 开始，就提供了一个 Invoke-Sqlcmd 的 Cmdlet，功能类似于早期的SQLCMD，可以直接运行。SQL Server 2008 默认的安装路径为C:\Program Files\Microsoft SQL Server\100\Tools\Binn。运行SQLPS.exe，直接进入名为“SQLSERVER:”的命名空间。

　　也可以在 Windows PowerShell 中手动加载插件。
　　PS C:\Users\Administrator> Get-PSSnapin -Registered
　　Name        : SqlServerCmdletSnapin100
　　PSVersion   : 1.0
　　Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.
　　Name        : SqlServerProviderSnapin100
　　PSVersion   : 1.0
　　Description : SQL Server Provider
　　PS C:\Users\Administrator> Add-PSSnapin sqlserverprovidersnapin100
　　PS C:\Users\Administrator> Add-PSSnapin sqlservercmdletsnapin100
　　PS C:\Users\Administrator> Get-Help Invoke-Sqlcmd -Detailed
　　2、SQL Server 2012
　　SQL Server 2012 仍然保留SQLPS.exe，默认安装路径为 C:\Program Files\Microsoft SQL Server\110\Tools\Binn ，版本为11.0.2100.60 。
　　SQL Server 2012 以模块的形式存在，可以在PowerShell 手动加载SQLPS模块，实现在PowerShell 访问 SQL 组件和功能。
　　PS C:\Users\Administrator> Get-Module -ListAvailable
　　ModuleType Name                      ExportedCommands
　　---------- ----                      ----------------
　　Manifest   BitsTransfer              {}
　　Manifest   PSDiagnostics             {}
　　Manifest   SQLASCMDLETS              {}
　　Manifest   SQLPS                     {}
　　PS C:\Users\Administrator> Set-ExecutionPolicy RemoteSigned
　　执行策略更改
　　执行策略可以防止您执行不信任的脚本。更改执行策略可能会使您面临 about_Execution_Policies
　　帮助主题中所述的安全风险。是否要更改执行策略?
　　[Y] 是(Y)  [N] 否(N)  [S] 挂起(S)  [?] 帮助 (默认值为“Y”): y
　　PS C:\Users\Administrator> Import-Module SQLPS
　　警告: 本地计算机没有 Analysis Services 的活动实例。
　　警告: 有些导入的命令名包含未批准的动词，这些动词可能会导致命令名不易被发现。请使用 Verbose
　　参数了解详细信息，或键入 Get-Verb 查看已批准的动词列表。
　　PS SQLSERVER:\>
　　注：加载了模块之后，提示符显示已经切换到名为“SQLSERVER:”的命名空间。
　　在导入模块时可以指定参数，隐藏警告信息。例如：
　　PS C:\Users\Administrator> Import-Module SQLPS -DisableNameChecking
　　三、执行一个查询
　　1、SQL Server 2008 R2
　　下面的例子是执行一个简单的查询，数据库实例为本机（localhost）的默认实例，使用Windows集成身份验证，访问的数据库是master。
　　PS C:\Users\Administrator> Invoke-Sqlcmd -query "select * from sys.databases" -Database master -ServerInstance localhost | Format-Table
　　name        database_id source_data owner_sid   create_date compatibili collation_n user_access user_access is_read_onl
　　base_id                                ty_level ame                     _desc                 y
　　----        ----------- ----------- ---------   ----------- ----------- ----------- ----------- ----------- -----------
　　master                1             {1, 0, 0... 2003/4/8...         100 SQL_Lati...           0 MULTI_USER        False
　　tempdb                2             {1, 0, 0... 2013/4/2...         100 SQL_Lati...           0 MULTI_USER        False
　　model                 3             {1, 0, 0... 2003/4/8...         100 SQL_Lati...           0 MULTI_USER        False
　　msdb                  4             {1, 0, 0... 2010/4/2...         100 SQL_Lati...           0 MULTI_USER        False
　　ReportSe...           5             {1, 5, 0... 2013/4/2...         100 Latin1_G...           0 MULTI_USER        False
　　ReportSe...           6             {1, 5, 0... 2013/4/2...         100 Latin1_G...           0 MULTI_USER        False
　　2、SQL Server 2012
　　SQL Server 2012仍然可以在PowerShell中执行Invoke-Sqlcmd。也可以使用模块中可用的命令和功能。以下命令可以列出所有SQLPS模块中的命令和功能。
　　PS SQLSERVER:\> Get-Command -Module SQLPS
　　CommandType     Name                                                Definition
　　-----------     ----                                                ----------
　　Cmdlet          Add-SqlAvailabilityDatabase                         Add-SqlAvailabilityDatabase [[-Path] ]...
　　Cmdlet          Add-SqlAvailabilityGroupListenerStaticIp            Add-SqlAvailabilityGroupListenerStaticIp [[-Path...
　　Cmdlet          Backup-SqlDatabase                                  Backup-SqlDatabase [-Database]  [[-Backu...
　　Cmdlet          Convert-UrnToPath                                   Convert-UrnToPath [-Urn]  [-Verbose] [-D...
　　Cmdlet          Decode-SqlName                                      Decode-SqlName [-SqlName]  [-Verbose] [-...
　　Cmdlet          Disable-SqlAlwaysOn                                 Disable-SqlAlwaysOn [[-Path] ] [-NoServi...
　　Cmdlet          Enable-SqlAlwaysOn                                  Enable-SqlAlwaysOn [[-Path] ] [-NoServic...
　　Cmdlet          Encode-SqlName                                      Encode-SqlName [-SqlName]  [-Verbose] [-...
　　Cmdlet          Invoke-PolicyEvaluation                             Invoke-PolicyEvaluation [-Policy]  [-A...
　　Cmdlet          Invoke-Sqlcmd                                       Invoke-Sqlcmd [[-Query] ] [-ServerInstan...
　　Cmdlet          Join-SqlAvailabilityGroup                           Join-SqlAvailabilityGroup [-Name]  [[-Pa...
　　Cmdlet          New-SqlAvailabilityGroup                            New-SqlAvailabilityGroup [-Name]  [[-Pat...
　　Cmdlet          New-SqlAvailabilityGroupListener                    New-SqlAvailabilityGroupListener [-Name]  installutil -i "C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.dll"
　　Microsoft (R) .NET Framework 安装实用工具版本 2.0.50727.4927
　　版权所有(C) Microsoft Corporation。保留所有权利。
　　正在运行事务处理安装。
　　正在开始安装的“安装”阶段。
　　查看日志文件的内容以获得 C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.dll 程序集的进度。
　　该文件位于 C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.InstallLog。
　　正在安装程序集“C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.dll”。
　　受影响的参数是:
　　logtoconsole =
　　assemblypath = C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.dll
　　i =
　　logfile = C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.InstallLog
　　“安装”阶段已成功完成，正在开始“提交”阶段。
　　查看日志文件的内容以获得 C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.dll 程序集的进度。
　　该文件位于 C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.InstallLog。
　　正在提交程序集“C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.dll”。
　　受影响的参数是:
　　logtoconsole =
　　assemblypath = C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.dll
　　i =
　　logfile = C:\sql2008\Microsoft.SqlServer.Management.PSSnapins.InstallLog
　　“提交”阶段已成功完成。
　　已完成事务处理安装。
　　PS C:\Windows\system32> Get-PSSnapin -Registered
　　Name        : SqlServerCmdletSnapin100
　　PSVersion   : 2.0
　　Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.
　　Name        : SqlServerProviderSnapin100
　　PSVersion   : 2.0
　　Description : SQL Server Provider
　　PS C:\Windows\system32>
　　二、PowerShell 访问 SQL Server 2008
　　1、加载插件
　　PS C:\Windows\system32> Add-PSSnapin -Name SqlServerCmdletSnapin100
　　PS C:\Windows\system32> Add-PSSnapin -Name SqlServerProviderSnapin100
　　2、执行查询
　　PS C:\Windows\system32> Invoke-Sqlcmd -Query " Select GETDATE() AS ServerTime " -ServerInstance "192.168.159.130" -Database master -Username sa -Password SaPassword123
　　ServerTime
　　----------
　　2013/5/6 13:38:24
　　三、使用计划任务
　　在本例中，将添加一个计划任务。实现的功能：每隔15分钟访问一次远程的192.168.159.130 服务器上的 SQL Server 默认实例。然后将访问的结果输出到一个文本文件monitor.txt 中。
　　1、验证插件
　　PS C:\Windows\system32> Get-PSSnapin -Registered
　　Name        : SqlServerCmdletSnapin100
　　PSVersion   : 2.0
　　Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.
　　Name        : SqlServerProviderSnapin100
　　PSVersion   : 2.0
　　Description : SQL Server Provider
　　2、创建任务
　　打开PowerShell ICE，创建一个名为monitordb.ps1文件。内容如下：
　　try
　　{
　　Add-PSSnapin -Name SqlServerCmdletSnapin100
　　}
　　catch
　　{
　　}
　　try
　　{
　　Add-PSSnapin -Name SqlServerProviderSnapin100
　　}
　　catch
　　{
　　}
　　Invoke-Sqlcmd -Query " Select GETDATE() AS ServerTime " -ServerInstance "192.168.159.130" -Database master -Username sa -Password SaPassword123 | Out-File -FilePath C:\sql2008\monitor.txt -Append
　　3、创建计划
　　以Windows 7为例，打开“管理工具”中的“任务计划程序”。创建一个任务。

　　输入任务的名称，并设定使用哪个帐户运行该任务。如要需要“以管理员身份运行”，则请勾选“使用最高权限运行”选项。

　　新建一个触发器，设置为每隔15分钟运行一次。

　　新建一个操作。例如： powershell -File "C:\sql2008\monitordb.ps1" 。
　　可以将程序与参数都写在同一个输入框，系统会自动识别并分割程序与参数。

　　在“新建任务”对话窗口点“确定”，这时候会要求输入运行该任务的帐号所对应的密码。

　　4、验证计划任务
　　在“活动任务”列表中检查刚才创建的任务。

　　等候2、3个运行周期之后，再打开 C:\SQL2008文件夹，检查该任务写入的文本文件。

　　四、SQL Server 2012
　　参考 http://technet.microsoft.com/zh-cn/magazine/hh855069.aspx
　　例如：
　　PS SQLSERVER:\> Get-DatabaseData -verbose -connectionString 'Server=localhost;Database=db01;Trusted_Connection=True;' -isSQLServer-query "SELECT GETDATE() as ServerTime"

• 　　PowerShell -- SQL Server的使用之连接SQL Server （3）
  

　　PowerShell相比其他脚本语言来说最大优点在于可以直接使用.Net提供的类库, 这样可以大大减轻程序员学习PowerShell的难度.
　　下面是一个简单的连接SQL Server 2005的例子:
　　# 创建数据库连接串

# Data Source 表示要连接的数据库, tcp表示tcp协议, (local)表示连接本地数据库

# Integrated Security 表示连接数据库的认证方法, 我使用的是推荐的Windows Account Credential.

$SqlConn = [System.Data.SqlClient.SqlConnection] "Data Source=tcp:(local);Integrated Security=SSPI;"

# 打开数据库连接

$SqlConn.Open();

# 确认状态

$SqlConn.State;

# 切换到数据库 tma

$SqlConn.ChangeDatabase('tma');

# 确认切换到指定的数据库

$SqlConn.Database;

# 创建一个绑定到此连接的SqlCommand对象, 你可以简单想象SqlCommand就是你平时用的SQL企业管理器或任何可以执行SQL语句的工具

$CC = $SqlConn.CreateCommand();

# 设置我们要进行的查询

$CC.CommandText = "SELECT 1"

# 因为这是一个标量结果, 因此调用ExecuteScalar即可

$CC.ExecuteScalar();

　　参考：
　　http://jimshu.blog.51cto.com/3171847/1193985