SQL Database Reports with PowerShell

Last year I wrote an article for SQL Server Pro called PowerShell in SQL Server. In the article I provided an introduction to using PowerShell and the SQL Server module to do perform some typical management tasks. I think this type of information is especially important for those of you who have to manage a SQL Server but perhaps aren't really a DBA. The other day I received an email about a way to list all databases and their associated files. While I go over that somewhat in the original article, I thought I'd revisit and expand a bit on the topic here.

In order for this to work you need the SQLPS module. Or you should be able to use PowerShell remoting with Invoke-Command. On my Windows 8.1 desktop I have the free SQL 2012 Express edition, primarily so that I can have access to the PowerShell module and specifically the Invoke-SQLCMD cmdlet which allows me to run a T-SQL command from my desktop to any SQL server. But that's not on the plate today.

Instead we're going to take advantage of the SQL Server provider. When you import the SQLPS module, you will get a new PSDrive called SQLSERVER:.

I can now navigate my SQL server instance like a file system.

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

I changed to the SQL "folder" then to the server (jh-win81-ent-01 is my local host) and then to the named instance, in this case Default and finally the Databases container.

Now that I know where to look I display just the information I want.

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

I discovered these properties by using Get-Member and piping to Select *.

Once I know the properties, it is pretty easy to display the required information. The challenge was to display databases and their files. The file information is buried a little bit but not that really difficult to access.

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}}

By the way, the sizes are in MB. The results are objects like anything else in PowerShell so you could sort, filter, export, convert or whatever you need to do with this information. I think I'll create an HTML report.

$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

Assuming the current credentials have the right permissions on a remote SQL Server, you can also do this remotely. Simply change the machine name in the path. Here's my modified HTML report code.

$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"

If you run into performance or timing issues, simply use remoting.

$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"

Remoting is a great option if you need to use alternate credentials or if you want to process several SQL Servers at once.

PowerShell works the same whether you are looking at a process, event log or SQL Server database. Once you master the fundamentals there's no end to what you can accomplish.

---

---