read命令

If you haven’t heard of , it’s an open source project out on GitHub dedicated to making life easier for Data Professionals. If you have an idea that you don’t see a command for, you can build it and contribute it to the project. Or you can ask if someone has time to write if for you.

如果您还没有听说过 ，那么它是GitHub上的一个开源项目，致力于使Data Professionals的工作更轻松。 如果您有看不到命令​​的想法，则可以对其进行构建并将其贡献给项目。 或者，您可以询问是否有人有空为您写信。

获取dbatools (Getting dbatools)

PowerShell has come a long way over the last 10 years. You can now download the dbatools module from the PowerShell Gallery, right from inside of PowerShell itself. If you’re running PowerShell 5+, simply run this command from an elevated shell and you’ll be ready to go.

在过去的十年中，PowerShell取得了长足的进步。 现在，您可以直接从PowerShell本身内部，从PowerShell画廊下载dbatools模块。 如果您运行的是PowerShell 5+，则只需从高架外壳中运行此命令，即可开始使用。

 Install-Module dbatools 

If you’re stuck on PowerShell 3, you can use this command to download the latest release of the dbatools module from the GitHub repository.

如果您仍然使用PowerShell 3，则可以使用此命令从GitHub存储库下载dbatools模块的最新版本。

 Invoke-Expression (Invoke-WebRequest -UseBasicParsing https://dbatools.io/in) 

Once you have the dbatools module downloaded you can get a full list of the commands by running

下载dbatools模块后，可以通过运行以下命令获取命令的完整列表

使用Read-DbaBackupHeader读取备份文件 (Reading a backup file using Read-DbaBackupHeader)

Today we’re going to take a look at a new command in the dbatools module called Read-DbaBackupHeader. This command will allow you to cross-reference backup files on your SQL Server’s local drive or network share with information from your SQL Server’s backup history. The great thing about having a command like this is that you probably don’t need to look at this kind of information very frequently, so having a command that already has the relationships worked out and has demos on the kind of data you can expect to retrieve with it is a huge help in letting you to get through some infrequent task and back to what you love doing the most.

今天，我们将看看dbatools模块中名为Read-DbaBackupHeader的新命令。 使用此命令，您可以将SQL Server的备份历史记录中的信息与SQL Server本地驱动器或网络共享上的备份文件进行交叉引用。 拥有这样的命令的好处在于，您可能不需要非常频繁地查看此类信息，因此拥有已经制定出关系并具有可预期的数据演示的命令它可以帮助您完成一些不常见的任务，并返回到您最喜欢做的事情。

Let’s start with looking at a single backup file with the Read-DbaBackupHeader command. To do that we can run

让我们开始使用Read-DbaBackupHeader命令查看单个备份文件。 为此，我们可以运行

 Read-DbaBackupHeader -SqlServer localhost -Path 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak' 

This will give you in the neighborhood of 60+ different pieces of information (properties) about the backup file. Some of these properties will be useful to some people while other properties will be useful to others. Next I’d like to focus on two sets of properties I’ve found quite useful over the years.

这将为您提供有关备份文件的60多种不同的信息（属性）。 这些属性中的某些对某些人有用，而其他属性对其他人有用。 接下来，我想重点介绍我多年来发现非常有用的两组属性。

使用-FileList参数读取备份文件 (Reading a backup file using the -FileList parameter)

On a number of occasions, I have found myself trying to restore a backup that was too big for where it was trying to be restored to. Worse yet, is when you are trying to restore multiple databases and the first few succeed but then you run out of space and the last few end up failing. Wouldn’t it be great if you could know how big each data & log file inside the backup file are going to be once you restore them?

在很多情况下，我发现自己试图还原一个备份，该备份对于尝试还原到的备份太大。 更糟糕的是，当您尝试还原多个数据库时，前几个数据库成功，但是随后空间不足，最后几个数据库失败。 如果您知道还原后备份文件中每个数据和日志文件的大小，那不是很好吗？

Read-DbaBackupHeader makes retrieving this information pretty simple with the -FileList parameter. This parameter is nice because the backup file contains multiple files, so when using this parameter, the command goes ahead and does the work of unwinding that array or properties into rows for you to work with.

Read-DbaBackupHeader使用-FileList参数使检索此信息非常简单。 此参数很不错，因为备份文件包含多个文件，因此，使用此参数时，该命令将继续执行并完成将数组或属性展开为行以供您使用的工作。

 Read-DbaBackupHeader -SqlServer localhost -Path 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak' -FileList 

Using the -FileList parameter you will see another 20+ properties for each data & log file within the backup file. In this case, I’m really only interested in the file itself and its size so I will pipe the results out to the SELECT cmdlet and specify the columns I want.

使用-FileList参数，您将在备份文件中看到每个数据和日志文件的另外20多个属性。 在这种情况下，我实际上只对文件本身及其大小感兴趣，因此我会将结果通过管道传递到SELECT cmdlet并指定所需的列。

 Read-DbaBackupHeader -SqlServer localhost -Path 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak' -FileList | SELECT LogicalName, Size, PhysicalName 

By default, the “Size” is reported in bytes. If you want to convert it to MB or GB that’s easy to do by adding some formatting syntax like this.

默认情况下，“大小”以字节为单位报告。 如果您想将其转换为MB或GB，可以通过添加诸如此类的格式语法来轻松实现。

 [string]$unit= "MB"$measure = "1$unit" Read-DbaBackupHeader -SqlServer localhost -Path 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak' -FileList | SELECT LogicalName, @{Label="SizeIn$unit";Expression={"{0:n2}" -f($_.Size/$measure)}}, PhysicalName  

只查看目录中的文件名和大小 (Looking at just the file name & size in a directory)

That’s great information, but what if you needed to get this information for multiple backup files that you were restoring? It only takes a small adjustment to the code to make that happen. Instead of looking at a single file we will take the stem of that directory path and do a Dir on it.

这是非常有用的信息，但是如果您需要获取要还原的多个备份文件的信息，该怎么办？ 只需对代码进行少量调整即可实现。 除了查看单个文件之外，我们将使用该目录路径的主干并在其上执行Dir。

 DIR 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\' |Read-DbaBackupHeader -SqlServer localhost  

We then pipe those results over to the Read-DbaBackupHeader command and presto, we have a list of all the data & log files and their sizes for every backup file in the directory.

然后，我们将这些结果通过管道传递到Read-DbaBackupHeader命令并进行存储，我们获得了所有数据和日志文件的列表以及目录中每个备份文件的大小。

 DIR 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\' | Read-DbaBackupHeader -SqlServer localhost -FileList |SELECT LogicalName, Size, PhysicalName 

查看您关心的备份文件的文件名和大小 (Looking at the filename & size of just the backup files you care about)

Of course, you may not be trying to restore every backup file in the directory, but maybe a handful. In this case we can use one of my favorite features of PowerShell, the Out-GridView cmdlet and its -PassThru parameter to thin down the list to only the backup files you care about, before sending the list on down to the Read-DbaBackupHeader command.

当然，您可能没有尝试还原目录中的每个备份文件，但是可能很少。 在这种情况下，在将列表发送到Read-DbaBackupHeader命令之前，我们可以使用PowerShell最喜欢的功能之一，Out-GridView cmdlet及其-PassThru参数将列表精简为仅关心的备份文件。 。

 DIR 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\' |Out-GridView -PassThru |Read-DbaBackupHeader -SqlServer localhost -FileList |SELECT LogicalName, Size, PhysicalName 

备份，是否压缩？ (Backups, were they compressed or not?)

Finally, let’s have a look at the backup files and whether they are compressed or not. You probably have your backup compression defaulted to 1 or True but even if you do, a process could still be backing up a database without backup compression turned on, costing you space.

最后，让我们看一下备份文件以及它们是否已压缩。 您可能已将备份压缩默认设置为1或True，但是即使您这样做，在没有打开备份压缩的情况下，进程仍可能在备份数据库，这会浪费您的空间。

To inspect the backup files to see if they are compressed or not, simply take the same command we were using above (remove the -FileList parameter) and change the properties you’re SELECT’ing to DatabaseName, Compressed, BackupPath.

要检查备份文件以查看它们是否已压缩，只需执行与上面相同的命令（删除-FileList参数），然后将您选择的属性更改为DatabaseName，Compressed，BackupPath。

 DIR 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\' |Read-DbaBackupHeader -SqlServer localhost | SELECT DatabaseName, Compressed, BackupPath  

At this point you could pipe the results to the Out-GridView cmdlet to filter and analyze them further. When you do that PowerShell will pop up a grid of results, similar to the Results to Grid setting in SSMS, except a little better. At the top of the window that pops up, there is a search box with the word “Filter” ghosted in it. If you type something in here it will filter every column on every row for that search term. Alternatively, you can click on the “Add Criteria” button and choose to filter the results in the grid based on the values in only a single column.

此时，您可以将结果通过管道传输到Out-GridView cmdlet进行筛选和进一步分析。 当您执行此操作时，PowerShell将弹出一个结果网格，类似于SSMS中的“将结果网格化”设置，但效果更好一些。 在弹出的窗口顶部，有一个搜索框，其中带有“过滤器”字样。 如果您在此处输入内容，它将过滤该搜索词每一行的每一列。 或者，您可以单击“添加条件”按钮，然后选择仅基于单个列中的值在网格中过滤结果。

 DIR 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\' |Read-DbaBackupHeader -SqlServer localhost | SELECT DatabaseName, Compressed, BackupPath |OGV  

Adding the “Compressed” column and giving it a value of “0” will filter those results down to just the backup files that weren’t compressed when they were created. From here you can work on tracking down what process created those backup files and see about getting them converted to using backup compression.

添加“压缩”列并将其值设置为“ 0”会将这些结果过滤为仅创建时未压缩的备份文件。 在这里，您可以跟踪创建这些备份文件的过程，并了解如何使用备份压缩将它们转换为备份文件。

结语 (Wrap up)

Again, these are tasks that I would do very infrequently myself, so having a PowerShell command that can help me sort out the information quickly makes it so that I won’t have to remember much to get these tasks done.

同样，这些是我自己很少执行的任务，因此拥有可以帮助我快速整理信息的PowerShell命令可以使这些信息变得有用，从而使我不必花太多时间就可以完成这些任务。

Chrissy LeMaire, the leader of the dbatools project, shares many more of the backup/restore related commands in her blog post “”.  Please join a meeting if you can, we hold meetings at least once a month.  You can find more information about reading the SQL Server backup header on MSDN: . And of course, you can keep up with the latest developments/improvements the SQL PowerShell is asking for on my

dbatools项目的负责人Chrissy LeMaire在她的博客文章“ ”中分享了更多与备份/还原相关的命令。 如果可以，请加入会议的 ，我们每月至少召开一次会议。 您可以在MSDN上找到有关读取SQL Server备份标头的更多信息： 。 当然，您可以在我的上紧跟SQL PowerShell所要求的最新发展/改进

翻译自:

read命令