将PowerShell连接到SQL Server

介绍 (Introduction)

PowerShell (aka Posh or just PS) is becoming more and more of a tool for operational support and some deployment scenarios. If you need to pull or place data into SQL Server, PS can be a handy way of doing it in both one-off and automated work.

PowerShell（又名Posh或PS）正越来越成为一种用于操作支持和某些部署方案的工具。 如果您需要将数据提取或放置到SQL Server中，则PS可以一次性完成，也可以自动完成。

There are a number of ways to connect to SQL Server via PS. In this article I wanted to go over the options that are available to you. If you have used PS for any number of months or years, you know there tends to be multiple ways of performing a task. So it goes without saying if you are building out scripts to use in production to test, test, and test…then test it one more time.

有多种方法可以通过PS连接到SQL Server。 在本文中，我想介绍一些可用的选项。 如果您使用PS已有数月或数年之久，那么您就会知道执行任务的方式有多种。 因此，不用说您是否要构建用于生产环境的脚本来进行测试，测试和测试……然后再对其进行一次测试。

选项清单 (List of Options)

As an overview the following is the list of options I will go over in this article:

作为概述，以下是我将在本文中介绍的选项的列表：

• SQL Server PowerShell (SQLPS)
  SQL Server PowerShell（SQLPS）
• SQL Server Management Objects (SMO)
  SQL Server管理对象（SMO）
• .NET (System.Data.SqlClient)
  .NET（System.Data.SqlClient）

SQL Server PowerShell (SQL Server PowerShell)

SQLPS is a utility that was first released with SQL Server 2008, you may see this referenced in various ways. It exists as a (1) utility and (2) as a PS module. The utility and module are installed with the SQL Server Management tools from SQL Server 2008 and up. There are a few ways of connecting to SQL Server using this utility, and each one has strengths and weaknesses.

SQLPS是随SQL Server 2008一起首次发布的实用程序，您可能会看到以多种方式引用了此实用程序。 它以（1）实用程序和（2）作为PS模块的形式存在。 该实用程序和模块与SQL Server 2008及更高版本中SQL Server管理工具一起安装。 使用此实用程序有几种连接到SQL Server的方法，每种方法都有其优点和缺点。

SQLPS.exe (SQLPS.exe)

This is a utility that you should be able to open by typing it in the run prompt (Start > Run). A second option, right-click a node under Object Explorer, within SQL Server Management Studio (SSMS), and select “Start PowerShell”. The SQLPS utility’s main access point is using the provider “SQLSERVER:\” to browse SQL Server like a file directory. With that, based on the node you open SQLPS from will place you within that path of the provider. Under each “folder” you are in for the provider offers properties to read or set, and some methods to use for administration.

这是一个实用程序，您应该可以通过在运行提示符（开始>运行）中键入它来打开它。 第二个选项是，右键单击SQL Server Management Studio（SSMS）中“对象资源管理器”下的节点，然后选择“启动PowerShell”。 SQLPS实用程序的主要访问点是使用提供程序“ SQLSERVER：\”来浏览SQL Server，就像文件目录一样。 这样，基于您打开SQLPS的节点，您将进入提供程序的路径。 在提供程序所在的每个“文件夹”下，提供程序都提供了要读取或设置的属性，以及用于管理的一些方法。

One thing you must remember when using this utility is the version of PS you operate under is 2.0, an obvious weakness. Example: I use on Windows 10 Operating System (OS) with PS version 5.0 and SQL Server 2012:

使用此实用程序时，您必须记住的一件事是您使用的PS版本是2.0，这是一个明显的弱点。 示例：我在带有PS版本5.0和SQL Server 2012的Windows 10操作系统（OS）上使用：

It is very important distinction to remember because a cmdlet (pronounced command-let) under version 5.0 may not show up at all in SQLPS.exe, or be missing new features that were added.

记住这是非常重要的区别，因为版本5.0下的cmdlet（发音为command-let ）可能根本不会出现在SQLPS.exe中，或者缺少所添加的新功能。

This method to accessing a SQL Server instance is for those one-liner situations, but will admit that SQLPS is rather slow to work with at times, so don’t get in too big of a rush. As an example, a common one-liner I use SQLPS utility for is when I am refreshing development environment and just want to set all the databases to SIMPLE recovery mode:

这种访问SQL Server实例的方法仅适用于那些单行的情况，但是会承认SQLPS有时工作起来相当慢，因此不要着急。 例如，当我刷新开发环境并只想将所有数据库设置为SIMPLE恢复模式时，我使用SQLPS实用程序的一种常见的方法：

Get-ChildItem SQLSERVER:\SQL\LOCALHOST\SQL12\Databases | foreach { $_.RecoveryModel = “SIMPLE”; $_.Alter() }

SQLPS模块 (SQLPS Module)

Importing the SQLPS module into a PS session provides the same access using the utility does, but allows you to operate in the PS version of the OS you operate under. In SQL Server 2008 and 2008 R2 you will load the SQLPS as a snap-in (Add-PSSnapin), then with SQL Server 2012 and up it is imported (Import-Module).

将SQLPS模块导入PS会话可使用该实用程序提供相同的访问权限，但允许您在所使用的OS的PS版本中进行操作。 在SQL Server 2008和2008 R2中，您将作为管理单元（Add-PSSnapin）加载SQLPS，然后与SQL Server 2012一起导入（导入模块）。

When you import the module it will load the SQLSERVER:\ provider and change your location to that path. You will also have access to the cmdlets offered in the module, which these are also accessible via the utility. You can get a list of those cmdlets using the Get-Command:

导入模块时，它将加载SQLSERVER：\ provider并将您的位置更改为该路径。 您还可以访问模块中提供的cmdlet，也可以通过实用程序对其进行访问。 您可以使用Get-Command获得这些cmdlet的列表：

The most commonly known cmdlet out of this module is, Invoke-Sqlcmd. This is generally thought of as a PS replacement for the old sqlcmd command-line utility, that to date is still available in currently supported versions of SQL Server. You utilize this cmdlet to execute any T-SQL query that you want against one or multiple instances. The advantage you get using Invoke-Sqlcmd over the command-line utility is the power of handling output in PS. The output from the cmdlet is created as a DataTable (System.Data.DataRow is the exact type).

该模块中最广为人知的cmdlet是Invoke-Sqlcmd。 通常认为这是旧sqlcmd命令行实用程序的PS替代，到目前为止，在SQL Server的当前受支持版本中仍然可用。 您可以利用此cmdlet对一个或多个实例执行所需的任何T-SQL查询。 与命令行实用程序相比，使用Invoke-Sqlcmd的优势在于可以处理PS中的输出。 cmdlet的输出将创建为DataTable （准确的类型为System.Data.DataRow ）。

Just passing the full command to Get-Member will show the TypeName:

只需将完整命令传递给Get-Member即可显示TypeName：

You will also see in the output that if you properly named your columns in your SELECT statement they show up as properties in that array. You can leverage the data conversions available in PS such as ConvertTo-Csv, or even ConvertTo-HTML if you needed to build an HTML report.

您还将在输出中看到，如果您在SELECT语句中正确命名了列，它们将显示为该数组中的属性。 您可以利用PS中可用的数据转换，例如ConvertTo-Csv，如果需要构建HTML报告，甚至可以利用ConvertTo-HTML。

SQL Server管理对象（SMO） (SQL Server Management Objects (SMO))

SMO is a pain to some, but once you learn how to research the SMO namespace in MSDN your eyes can be opened to the possibilities. SMO is most commonly seen used to perform administration task against SQL Server instance(s). You can use this to do a check of the configuration instance where you may do a comparison of the settings to your standard configuration or for audit situations. It offers some flexibility over executing the equivalent T-SQL via Invoke-Sqlcmd, if there is an option via T-SQL.

SMO对于某些人来说是很痛苦的，但是一旦您学习了如何在MSDN中研究SMO名称空间，您就可以大开眼界。 SMO最常用于对SQL Server实例执行管理任务。 您可以使用它来检查配置实例，在此您可以将设置与标准配置进行比较或用于审计情况。 如果可以通过T-SQL进行选择，则与通过Invoke-Sqlcmd执行等效的T-SQL相比，它提供了一些灵活性。

You can also execute T-SQL through SMO if you wish and there is one benefit using this method over the .NET method. The benefit you get is executing T-SQL statements that may include the “GO” batch separator. Using the .NET method errors when it hits the first “GO” in a script, because it is not true SQL syntax. This can be useful in deployments where someone may have scripted out the objects via SSMS, which will put in the “GO” statement. If you happen to have gotten a long script, or a zipped file that contains hundreds of scripts, utilizing SMO in this situation saves a good bit of headache.

如果愿意，您也可以通过SMO执行T-SQL，与.NET方法相比，使用此方法有一个好处。 您获得的好处是执行可能包含“ GO”批处理分隔符的T-SQL语句。 当它到达脚本中的第一个“ GO”时，使用.NET方法错误，因为它不是真正SQL语法。 这在有人通过SSMS脚本化对象的部署中很有用，该对象将放入“ GO”语句中。 如果您碰巧遇到了一个长脚本或包含数百个脚本的压缩文件，则在这种情况下使用SMO可以省去很多麻烦。

加载SMO (Loading SMO)

To use SMO the first thing that has to be done is loading the SMO assembly into your PowerShell session. You can actually do this by simply loading the full SQLPS module, as that will automatically load the assembly. The other method, and more commonly used in online articles, is to add the assembly using System.Reflection.Assembly::LoadWithPartialName(). A more proper method to load SMO, as of PS 3.0, is to use the cmdlet Add-Type.

要使用SMO，首先要做的就是将SMO程序集加载到PowerShell会话中。 实际上，您可以通过简单地加载完整SQLPS模块来执行此操作，因为这将自动加载程序集。 在线文章中更常用的另一种方法是使用System.Reflection.Assembly :: LoadWithPartialName （）添加程序集。 从PS 3.0开始，更合适的加载SMO的方法是使用cmdlet Add-Type。

Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"

与SMO连接 (Connecting with SMO)

Once you have the assembly loaded you create an object for the SQL Server instance you want to work with:

加载程序集后，将为要使用SQL Server实例创建一个对象：

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server “localhost\sql12”
$srv.Databases | select name

Now from there you will need to work out which task you want to perform, and find the class of objects and methods that you need. It, obviously, is not the method used for quickly doing a task (at least until you get familiar with how SMO works).

现在，从那里您将需要确定要执行的任务，并找到所需的对象和方法的类。 显然，它不是用于快速完成任务的方法（至少在您熟悉SMO的工作原理之前）。

.NET Framework (.NET Framework)

PS is built on top of .NET so you have access to that framework that can be used to perform work against SQL Server. This is more commonly used to work with data itself over performing administrative task. I will also tell you it takes a good bit more typing to use, so not an option for one-liners.

PS建立在.NET之上，因此您可以访问该框架，该框架可用于对SQL Server进行工作。 这通常用于在执行管理任务时处理数据本身。 我还将告诉您，要使用它要花很多时间，所以单行不是一个选择。

There is one advantage you can achieve using this process over the ones we already went over and that is, portability. You will find .NET on any Windows OS so there is no dependency on SQL Server tools being installed, at least on the machine the script is running on. The version of .NET however can be dependent on the version of the OS you run, so just ensure you test before moving your script to production.

与我们已经讲过的过程相比，使用此过程可以实现一个优势，那就是可移植性。 您将在任何Windows操作系统上找到.NET，因此至少在运行脚本的计算机上没有依赖于所安装SQL Server工具。 但是，.NET的版本可能取决于您所运行的操作系统的版本，因此只需确保在将脚本移至生产环境之前进行测试即可。

Now there are various ways of doing this but, on average, you will see most examples create the following objects to get connected to an SQL Server instance:

现在有多种方法可以执行此操作，但是平均而言，您将看到大多数示例创建以下对象以连接到SQL Server实例：

1. Create a connection
   建立连接
2. Create your command (the T-SQL that will be executed)
   创建命令（将执行的T-SQL）
3. Create your data adapter (if you want to retrieve data)
   创建数据适配器（如果要检索数据）
4. Create your dataset (the adapter fills this object)
   创建您的数据集（适配器填充此对象）

建立连接 (Create a Connection)

You simply create an object of System.Data.SqlClient.SqlConnection and pass the connection string that will be used to connect to the given SQL Server instance…don’t forget to open it.

您只需创建一个System.Data.SqlClient.SqlConnection对象，然后传递将用于连接到给定SQL Server实例的连接字符串……别忘了打开它。

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = “Server=localhost\sql12;Integrated Security=true;Initial Catalog=master”
$sqlConn.Open()

创建命令 (Create Your Command)

You have a few options here because the SqlConnection actually contains a method that you can use to create your command object, or you can create a separate object all together. I have seen both options used so it is for the most part a preference.

这里有几个选项，因为SqlConnection实际上包含一种可用于创建命令对象的方法，或者可以一起创建一个单独的对象。 我已经看到了两个选项的使用，因此在大多数情况下它是一个首选项。

$sqlcmd = $sqlConn.CreateCommand()
<# or #>
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlConn
$query = “SELECT name, database_id FROM sys.databases”
$sqlcmd.CommandText = $query

创建您的数据适配器 (Create Your Data Adapter)

By definition this object “represents a set of data commands and a database connection that are used to fill the DataSet”. You create the SqlDataAdapter and pass it the previous command object created, $sqlcmd.

根据定义，此对象“代表用于填充数据集的一组数据命令和一个数据库连接”。 您创建SqlDataAdapter并将其传递给先前创建的命令对象$ sqlcmd 。

$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd

创建您的数据集（并填写） (Create Your DataSet (and fill it))

This object will be the type System.Data.DataSet and as defined is simply “an in-memory cache of data”. Which this is something to take note of that the query you are running has to be loaded into memory, so the larger the dataset the more memory needed.

该对象将是System.Data.DataSet类型，并且定义为“内存中的数据缓存”。 需要注意的是，您正在运行的查询必须加载到内存中，因此数据集越大，所需的内存就越多。

$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null

This is just my preference but when I use the “Fill” method I pipe this to Out-Null simply because this method will output the number of records it filled. If you want that output just remove the Out-Null.

这只是我的偏爱，但是当我使用“填充”方法时，将其通过管道传递到Out-Null只是因为此方法将输出其已填充的记录数。 如果您想要该输出，只需删除Out-Null即可 。

检索数据 (Retrieving Your Data)

After you do all that you are probably wondering how you output that data so you can see it? The data itself resides in a collection of tables within the Table property of your DataSet object. Now depending on the version of .NET you are working with you might actually need to specify the index of the collection (e.g. Tables[0]), but this is generally only required in older versions below .NET 4.0.

完成所有操作后，您可能想知道如何输出该数据以便看到它？ 数据本身位于DataSet对象的Table属性内的表集合中。 现在，根据您正在使用的.NET版本，您可能实际上需要指定集合的​​索引（例如Tables [ 0] ），但这通常仅在.NET 4.0以下的旧版本中才需要。

$data.Tables
<# or #>
$data.Tables[0]

If the procedure or T-SQL script you are executing contains more than one dataset this collection will only contain the first result set returned by SQL Server.

如果您正在执行的过程或T-SQL脚本包含多个数据集，则此集合将仅包含SQL Server返回的第一个结果集。

A visual of the whole command:

整个命令的视觉效果：

The last thing you will want to remember for just proper coding practice is to close the connection once you are done. Just call the Close() method to close out your connection to the SQL Server instance.

对于正确的编码实践，您要记住的最后一件事是一旦完成就关闭连接。 只需调用Close（ ）方法即可关闭与SQL Server实例的连接。

结语 (Wrap Up)

Going through the above options, I hope you have a better understanding on the options to access SQL Server via PowerShell. Which method you chose really depends on the action or processing required. You can be looking to script out a repeatable process or just need a one-liner for a one-off process.

通过以上选项，希望您对通过PowerShell访问SQL Server的选项有更好的了解。 您选择哪种方法实际上取决于所需的操作或处理。 您可能希望编写出可重复过程的脚本，或者只需要一次性处理即可。

The next article in this series:

本系列的下一篇文章：

• Connecting PowerShell to SQL Server – Using a Different Account将PowerShell连接到SQL Server –使用其他帐户

翻译自: https://www.sqlshack.com/connecting-powershell-to-sql-server/