The need for this script came about when I took a new job as a DBA. One of my responsibilities was to make sure all databases were available after the maintenance window which ran during the weekend. Rather than log into each database server and check that the databases were online, I had a script do the work for me and shoot me an email.
It evolved overtime, at first it was a simple PowerShell script that gave the output in the PowerShell console. Eventually I learned how to pipe the information into a table and then into HTML, and THEN send an email. After that I learned how to color code it so that if any database was offline, it would color the cell in red.
I prefer to do my PowerShell development in the windows provided Windows PowerShell ISE. I especially like the fact that it has a scripting environment with easily accessible tools and autocomplete, as well as the PowerShellPowerShell console/output window.
In this post I’ll take the time to go over some key parts of the script so that you can manipulate as you wish in order to implement it into your environment. I will provide the finished script at the bottom of the post.
First thing we have to do is import the sql PowerShell module in PowerShell.
|
1 2 3 4 |
#Import SQL Module Import-Module SQLPS -DisableNameChecking |
Next let’s quickly design the html output using a simple CSS style. Keep in mind I have very little experience with web development. I got this information from SQL Server 2012 with PowerShell V3 Cookbook. I highly suggest all Database Administrators and Database Developers pick this up. It contains so many useful tips. I also believe that a new one for 2014 has recently been released.
The part of this that defines .offline is a property that will set the cell color red (#E01B1B) for any database that is seen as offline.
|
1 2 3 4 5 6 7 8 9 |
#simple CSS Style $style = @" <style type='text/css'> td {border:1px solid gray;} .offline{background-color: #E01B1B;} </style> "@ |
Also, for a more in depth beginner’s tutorial on CSS please visit this link.
Now let’s define the function. I like to use functions to encompass my PowerShell script. It allows for easier parameterization and you can also import it as a module into your personal PowerShell profile and just call it from the console whenever necessary. I called it “Get-DbStatus” as it made sense to me and would be easy to remember.
|
1 2 3 |
function Get-DBStatus { |
Now we need to import a previously created list of servers we’re pulling this information from. It’s just a simple text file with a list of servernames and ports. I store the contents of that list into a variable called $ServerList and use the Get-Content command to pull the data out of the list.
|
1 2 3 4 5 6 7 8 |
$ServerList = Get-Content C:\PowerShell\PD.txt Foreach ($serverName in $ServerList){ #Create SMO Object $SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName |
Then I use Foreach to loop through each item in $serverlist and store it in $servername. I then use the data stored in $servername to create an SMO object for each server. This is so we can use PowerShell commands to get the information we need.
Next, I use a nested foreach loop in order to loop through each database in each sql server (SMO object).
|
1 2 3 4 5 6 7 8 9 |
foreach ($db in $SQLServer.Databases){ #Change text to read Online/Offline as opposed to "True" or "False" Switch ($db.IsAccessible) { "True" {$dbstatus = "Online"} "False" {$dbstatus = "Offline"} } |
The “IsAccessible” property will determine whether the database is available or not. The default output is “True/False” however I used the switch command to return “online/offline”. You can leave it, but I much prefer this method.
Next I define properties of the function. This will make it easy to call the function with specific information.
|
1 2 3 4 5 6 7 |
#Properties of function $props = @{ 'Server' = $ServerName 'DbName' = $db.Name 'Status' = $dbstatus} New-Object -TypeName PSObject -Property $props |
You will see me use the “Server, DBName, and Status” properties in the next portion of the script.
We would then close out the function with the necessary closing brackets (“}”) and then call the function within the script.
Follow this next step closely. I’m taking the output of the function and piping it into an html, and piping that into a foreach loop so I can color code the cells if it’s offline by referencing the css style sheet we created at the beginning of the script. This should give you an idea of how powerful the piping feature in PowerShell is.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
#let's get content from Get-Service and output this to styled HTML Get-DBStatus | ConvertTo-Html -Property Server, DBName, Status -Title "Database Status" -Head $style | Foreach { #if database is offline use red background if ($_ -like "*<td>Offline</td>*") { $_ -replace "<tr>", "<tr class='offline'>" } else { #display normally $_ } } | Out-File "C:\PowerShell\db_status.html" -force |
Finally, we can take this output and email an embedded HTML file to us every morning using the Send-MailMessage feature in PowerShell. This will make your daily tasks easier. I’ve provided a generic template below, be sure to confirm the proper settings for the SMTP server, and the “to” and “from” addresses. You may have to work with your mail administrator in order to get this information.
|
1 2 3 4 |
Send-MailMessage -to "<recipient@mail.com” -from "SQL Admin <Sqladmin@mail.com>" -Subject "DB Status" -SmtpServer smtp.mail.com -Attachments "C:\powershell\db_status.html" -BodyAsHtml (Get-Content C:\PowerShell\db_status.html | Out-String) |
As promised, here is the script in its entirety. Remember that you must modify and customize it so that it will work in your environment. You can get really creative with using functions and modular programming. You can even turn this into a module, import it into your profile and call it as a regular command. I suggest modifying it so that the output comes out in the PowerShell console. Otherwise you’d have to dig through your file system in order to open the html file. But this is entirely up to you. PowerShell is so versatile that it can automate most of your day-to-day tasks. Or use a simple command to return a plethora of information almost instantly. Saving a lot of people a lot of time. You can pass on the scripts to your juniors or team and increase productivity.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
#simple CSS Style $style = @" <style type='text/css'> td {border:1px solid gray;} .offline{background-color: #E01B1B;} </style> "@ #Import SQL Module Import-Module SQLPS -DisableNameChecking function Get-DBStatus { <# [CmdletBinding()] param ( [Parameter(Mandatory=$True)][string]$Servername )#> $ServerList = Get-Content C:\PowerShell\PD.txt Foreach ($serverName in $ServerList){ #Create SMO Object $SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName foreach ($db in $SQLServer.Databases){ #Change text to read Online/Offline as opposed to "True" or "False" Switch ($db.IsAccessible) { "True" {$dbstatus = "Online"} "False" {$dbstatus = "Offline"} } #Properties of function $props = @{ 'Server' = $ServerName 'DbName' = $db.Name 'Status' = $dbstatus} New-Object -TypeName PSObject -Property $props } } } #let's get content from Get-Service and output this to styled HTML Get-DBStatus | ConvertTo-Html -Property Server, DBName, Status -Title "Database Status" -PreContent "<h1>Database Status</h1>" -Head $style | Foreach { #if db is offline, use red background if ($_ -like "*<td>Offline</td>*") { $_ -replace "<tr>", "<tr class='offline'>" } else { #display normally $_ } } | Out-File "C:\PowerShell\db_status.html" -force #Set-Alias ie "$env:programfiles\Internet Explorer\iexplore.exe" #ie "C:\PowerShell\db_status.html" Send-MailMessage -to "<insert address here>" -from "<insert address here>" -Subject "DB Status" -SmtpServer mail.smtp.com -Attachments "C:\powershell\db_status.html" -BodyAsHtml (Get-Content C:\PowerShell\db_status.html | Out-String) |
Here is an example of the output. (I’ve obscured server and database names for obvious reasons, but you should get the idea).
And what the offline databases would look like:
I hope some of you find this script useful. And highly encourage everyone who works in a windows environment to learn PowerShell. It’s such a powerful and versatile tool.
If you’re looking for a place to start, this is a great book to get you primed. Learn Windows PowerShell in a Month of Lunches
Also be sure to checkout Microsoft’s own PowerShell Script Center
Also, please visit a more in depth beginner’s tutorial on CSS
I highly suggest all Database Administrators and Database Developers pick this up: SQL Server 2012 with PowerShell V3 Cookbook
He started his IT career in helpdesk world and eventually moved into the networking/systems administrator side of things. After some time, he developed an affection for working with Databases (mainly SQL Server) and has focused his career on that for the past eight years. He has worked for various industries in both large and small environments all with different needs.
SQL Shack has provided him with an opportunity to contribute to a community that has given him so much throughout the years.
View all posts by Aamir Syed
- Is SQL Server Always Encrypted, for sensitive data encryption, right for your environment - July 27, 2018
- Use cases for Query Store in SQL Server - July 18, 2018
- When to Use SQL Temp Tables vs. Table Variables - February 21, 2017