使用 PowerShell 生成 SQL 数据库报告

为了使其工作，您需要 SQLPS 模块。或者您应该能够通过 Invoke-Command 使用 PowerShell 远程处理。在我的 Windows 8.1 桌面上，我有免费的 SQL 2012 Express 版本，主要是为了我可以访问 PowerShell 模块，特别是 Invoke-SQLCMD cmdlet，它允许我从桌面运行 T-SQL 命令到任何 SQL 服务器。但这不是今天的任务。

相反，我们将利用 SQL Server 提供程序。当您导入 SQLPS 模块时，您将获得一个名为 SQLSERVER: 的新 PSDrive。

我现在可以像浏览文件系统一样浏览我的 SQL Server 实例。

cd .\SQL\jh-win81-ent\default\databases

我更改为 SQL“文件夹”，然后更改为服务器（jh-win81-ent-01 是我的本地主机），然后更改为命名实例，在本例中为默认实例，最后更改为数据库容器。

现在我知道在哪里查看，我只显示我想要的信息。

dir sqlserver:\sql\jh-win81-ent\default\databases | 
Select Name,Size,SpaceAvailable,PrimaryFilePath

我通过使用 Get-Member 和管道来选择 * 发现了这些属性。

dir sqlserver:\sql\jh-win81-ent\default\databases | 
Select Name,Size,SpaceAvailable,PrimaryFilePath,
@{Name="Path";Expression={$_.Filegroups.files.filename}},
@{Name="LogPath";Expression={$_.LogFiles.filename}},
@{Name="LogSize";Expression={$_.LogFiles.Size}}

顺便说一句，大小以 MB 为单位。结果是像 PowerShell 中其他任何内容一样的对象，因此您可以对这些信息进行排序、过滤、导出、转换或执行任何您需要执行的操作。我想我会创建一个 HTML 报告。

$head = @"
<Title>SQL Database Report</Title>
<style>
body { background-color:#FFFFFF;
       font-family:Tahoma;
       font-size:10pt; }
td, th { border:1px solid black; 
         border-collapse:collapse; }
th { color:white;
     background-color:black; }
table, tr, td, th { padding: 2px; margin: 0px }
tr:nth-child(odd) {background-color: lightgray}
tr:nth-child(odd) {background-color: lightgray}
table { width:95%;margin-left:5px; margin-bottom:20px;}
</style>
<br>
<H1>SQL Database Report</H1>
"@

dir sqlserver:\sql\jh-win81-ent\default\databases | 
Select Name,Size,SpaceAvailable,PrimaryFilePath,
@{Name="Path";Expression={$_.Filegroups.files.filename}},
@{Name="LogPath";Expression={$_.LogFiles.filename}},
@{Name="LogSize";Expression={$_.LogFiles.Size}} | 
Convertto-html -Head $head  -PreContent "<H2>$env:Computername</H2>" -PostContent "All sizes are in MB.<br><I>Report run $(Get-Date)</I>" |
Out-File c:\work\sqldbreport.htm

假设当前凭据在远程 SQL Server 上具有正确的权限，您也可以远程执行此操作。只需更改路径中的机器名称即可。这是我修改后的 HTML 报告代码。

$Computername = "Win81-ent-01"

$head = @"
<Title>SQL Database Report - $($computername.ToUpper())</Title>
<style>
body { background-color:#FFFFFF;
       font-family:Tahoma;
       font-size:10pt; }
td, th { border:1px solid black; 
         border-collapse:collapse; }
th { color:white;
     background-color:black; }
table, tr, td, th { padding: 2px; margin: 0px }
tr:nth-child(odd) {background-color: lightgray}
tr:nth-child(odd) {background-color: lightgray}
table { width:95%;margin-left:5px; margin-bottom:20px;}
</style>
<br>
<H1>SQL Database Report</H1>
"@

dir sqlserver:\sql$computername\default\databases | 
Select Name,Size,SpaceAvailable,PrimaryFilePath,
@{Name="Path";Expression={$_.Filegroups.files.filename}},
@{Name="LogPath";Expression={$_.LogFiles.filename}},
@{Name="LogSize";Expression={$_.LogFiles.Size}} | 
Convertto-html -Head $head  -PreContent "<H2>$Computername</H2>" -PostContent "All sizes are in MB.<br><I>Report run $(Get-Date)</I>" |
Out-File "c:\work\sqldbreport-$computername.htm"

如果遇到性能或计时问题，只需使用远程处理即可。

$sb = {
Import-module SQLPS
dir sqlserver:\sql$env:computername\default\databases |
Select Name,Size,SpaceAvailable,PrimaryFilePath,
@{Name="Path";Expression={$_.Filegroups.files.filename}},
@{Name="LogPath";Expression={$_.LogFiles.filename}},
@{Name="LogSize";Expression={$_.LogFiles.Size}} 
}

invoke-command -ScriptBlock $sb -ComputerName $computername -HideComputerName | 
Select Name,Size,SpaceAvailable,PrimaryFilePath,Path,LogPath,Logsize |
Convertto-html -Head $head  -PreContent "<H2>$Computername</H2>" -PostContent "All sizes are in MB.<br><I>Report run $(Get-Date)</I>" |
Out-File "c:\work\sqldbreport-$computername.htm"

如果您需要使用备用凭据或者想要同时处理多个 SQL Server，远程处理是一个不错的选择。

无论您查看的是进程、事件日志还是 SQL Server 数据库，PowerShell 的工作方式都是相同的。一旦掌握了基础知识，您就能实现无穷无尽的成就。