Using Plaster To Create a New PowerShell Module

Chrissy, CK and I presented a pre-con at PASS Summit in Seattle last week

20171031_083328.jpg

Tracey Boggiano T | B came along to our pre-con and afterwards we were talking about creating PowerShell modules. In her blog post she explains how she creates modules by copying the code from another module (dbatools in this case!) and altering it to fit her needs. This is an absolutely perfect way to do things, in our pre-con we mentioned that there is no use in re-inventing the wheel, if someone else has already written the code then make use of it.

I suggested however that she used the PowerShell module Plaster to do this. We didnt have enough time to really talk about Plaster, so Tracy, this is for you (and I am looking forward to your blog about using it to 😉 )

What is Plaster?

Plaster is a template-based file and project generator written in PowerShell. Its purpose is to streamline the creation of PowerShell module projects, Pester tests, DSC configurations, and more. File generation is performed using crafted templates which allow the user to fill in details and choose from options to get their desired output.

How Do I Get Plaster?

The best way to get Plaster is also the best way to get any PowerShell module, from the PowerShell Gallery

You can just run

Install-Module Plaster

If you get a prompt about the repository not being trusted, don’t worry you can say yes.

Following PowerShell’s Security Guiding Principles, Microsoft doesn’t trust its own repository by default. The advice as always is never trust anything from the internet even if a bearded fellow from the UK recommends it!!

The PowerShell Gallery is a centralised repository where anyone can upload code to share and whilst all uploads are analyzed for viruses and malicious code by Microsoft, user discretion is always advised. If you do not want to be prompted every time that you install a module then you can run

Set-PSRepository -Name PSGallery -InstallationPolicy Trusted

if you and/or your organisation think that that is the correct way forward.

What Can We Do With Plaster?

Now that we have installed the module we can get to the nitty gritty. You can (and should) use Plaster to automate the creation of your module structure. If you are going to something more than once then automate it!

I created a repository for my Plaster Template You are welcome to take it and modify it for your own needs. I created a folder structure and some default files that I always want to have in my module folder

module framework.png

So in my template I have created all of the folders to organise the files in the way that I want to for my modules. I have also included the license file and some markdown documents for readme, contributing and installation. If we look in the tests folder

tests folder.png

There are some default test files included as well.

But Plaster is more than just a file and folder template repository, if we look in the installation markdown file,  it looks like this

# Installing <%= $PLASTER_PARAM_ModuleName %>
# You can install <%= $PLASTER_PARAM_ModuleName %> from the Powershell Gallery using
Find-Module <%= $PLASTER_PARAM_ModuleName %> | Install-Module
Import-Module <%= $PLASTER_PARAM_ModuleName %>
We can paramatarise the content of our files. This will create a very simple markdown showing how to find and install the module from the PowerShell Gallery which saves us from having to type the same thing again and again. Lets see how to do that

The Manifest XML file

The magic happens in the manifest file You can create one with the New-PlasterManifest command in the template directory – Thank you to Mustafa for notifying that the manifest file creation now requires an extra parameter of TemplateType

$manifestProperties = @{
Path = "PlasterManifest.xml"
Title = "Full Module Template"
TemplateName = 'FullModuleTemplate'
TemplateVersion = '0.0.1'
TemplateType = 'Item'
Author = 'Rob Sewell'
}
New-Item -Path FullModuleTemplate -ItemType Directory
New-PlasterManifest @manifestProperties
This will create a PlasterManifest.xml file that looks like this
<?xml version="1.0" encoding="utf-8"?>
<plasterManifest
schemaVersion="1.1"
templateType="Project" xmlns="http://www.microsoft.com/schemas/PowerShell/Plaster/v1">
<metadata>
<name>FullModuleTemplate</name>
<id>220fba73-bf86-49e3-9ec5-c4bc2719d196</id>
<version>0.0.1</version>
<title>FullModuleTemplate</title>
<description>My PLaster Template for PowerShell Modules</description>
<author>Rob Sewell</author>
<tags></tags>
</metadata>
<parameters></parameters>
<content></content>
</plasterManifest>
You can see that the parameters and content tags are empty. This is where we will define the parameters which will replace the tokens in our files and the details for how to create our module folder.

Plaster Parameters

At present my parameters tag looks like this
<parameters>
<parameter name="FullName" type="text" prompt="Module author's name" />
<parameter name="ModuleName" type="text" prompt="Name of your module" />
<parameter name="ModuleDesc" type="text" prompt="Brief description on this module" />
<parameter name="Version" type="text" prompt="Initial module version" default="0.0.1" />
<parameter name="GitHubUserName" type="text" prompt="GitHub username" default="${PLASTER_PARAM_FullName}"/>
<parameter name="GitHubRepo" type="text" prompt="Github repo name for this module" default="${PLASTER_PARAM_ModuleName}"/>
</parameters>
So we can set up various parameters with their names and data types defined and a prompt and if we want a default value.
We can then use
<%= $PLASTER_PARAM_WHATEVERTHEPAREMETERNAMEIS %>
in our files to make use of the parameters.

Plaster Content

The other part of the manifest file to create is the content. This tells Plaster what to do when it runs.

Mine is split into 3 parts

<message>
Creating folder structure
</message>
<file source='' destination='docs'/>
<file source='' destination='functions'/>
<file source='' destination='internal'/>
<file source='' destination='tests'/>
We can provide messages to the user with the message tag. I create the folders using the filesource tag
<message>
Deploying common files
</message>
<file source='appveyor.yml' destination=''/>
<file source='contributing.md' destination=''/>
<file source='LICENSE.txt' destination=''/>
<templateFile source='install.md' destination=''/>
<templateFile source='readme.md' destination=''/>
<templateFile source='tests\Project.Tests.ps1' destination=''/>
<templateFile source='tests\Help.Tests.ps1' destination=''/>
<templateFile source='tests\Feature.Tests.ps1' destination=''/>
<templateFile source='tests\Regression.Tests.ps1' destination=''/>
<templateFile source='tests\Unit.Tests.ps1' destination=''/>
<templateFile source='tests\Help.Exceptions.ps1' destination=''/>
<templateFile source='docs\ReleaseNotes.txt' destination=''/>
<file source='module.psm1' destination='${PLASTER_PARAM_ModuleName}.psm1'/>
This part creates all of the required files. You can see that the static files (those which do not require any sort of parameterisation for the contents use the same file source tag as the folders with the source defined. The files that have content which is parameterised use a tag of templateFile Source telling Plaster to look inside there for the tokens to be replaced.
The last part of the content creates the module manifest.
<message>
Creating Module Manifest
</message>
<newModuleManifest
destination='${PLASTER_PARAM_ModuleName}.psd1'
moduleVersion='$PLASTER_PARAM_Version'
rootModule='${PLASTER_PARAM_ModuleName}.psm1'
author='$PLASTER_PARAM_FullName'
description='$PLASTER_PARAM_ModuleDesc'
encoding='UTF8-NoBOM'/>
which I have filled in with the parameters for each of the values.

Creating a new module at the command line

Now you can easily create a module with all of the required folders and files that you want by creating a directory and running

Invoke-Plaster -TemplatePath TEMPLATEDIRECTORY -DestinationPath DESTINATIONDIRECTORY

which looks like this

Its that easy 🙂

Create a module without prompts

You can also create a module without needing to answer prompts. We can prefill them in our parameter splat
$plaster = @{
TemplatePath ="GIT:\PlasterTemplate"
DestinationPath = "GIT:\NewModule"
FullName = "Rob Sewell"
ModuleName = "NewModule"
ModuleDesc = "Here is a module description"
Version = "0.9.0"
GitHubUserName = "SQLDBAWithABeard"
GitHubRepo = "NewModule"
}
If (!(Test-Path $plaster.DestinationPath)) {
New-Item-ItemType Directory -Path $plaster.DestinationPath
}
Invoke-Plaster @plaster
Which will look like this

Make Your Own

Hopefully this have given you enough information and shown you how easy it is to automate creating the framework for your new PowerShell modules and parameterising them. Let me know how you get on and share your examples

Further Reading

Kevin Marquettes blog post is an excellent and detailed post on using Plaster which you should also read for reference as well as David Christians post which has some great content on adding user choice to the parameters enabling one plaster template to fulfill multiple requirements.

TSQL2sDay – Get-PostRoundup

First an apology, this round up is late!

The reason for that is an error in the PowerShell testing module Pester (That’s not completely true as you shall see!!)

I spoke in Stuttgart at the PowerShell Saturday last weekend and had intended to write this blog post whilst travelling, unfortunately I found a major error in Pester (again not strictly true but it makes a good story!!)

I explained it with this slide in my presentation

Yep, I forgot to pack my NUC with my VMs on it and had to re-write all my demos!!

But anyway, on to the TSQL2sDay posts

What a response. You wonderful people. I salute you with a Rimmer salute

There are 34 TSQL2sDay posts about dbatools, about starting with PowerShell, If you should learn PowerShell, SSAS, SSRS, Log Shipping, backups, restores, Pester, Default settings, best practices, migrations, Warnings in Agent Jobs, sqlpackage, VLFs, CMS, Disabling Named Pipes, Orphaned users, AG Status, AG Agent Jobs, logging, classes, auditing, copying files, ETL and more.

I am really pleased to see so many first timers to the TSQL2sDay blog monthly blog party. Please don’t let this be your only TSQL2sDay post. Come back next month and write a post on that topic.

Here they are below in the media of tweets, so that you can also go and follow these wonderful people who are so willing to share their knowledge. Say thank you to them, ask them questions, interact.

Learn, Share, Network

Volker wrote about testing best practices with dbatools

Dave explains why PowerShell is so useful to him in his ETL processes

Steve writes about the time he has saved using PowerShell to automate restores and audit SQL Server instances

https://twitter.com/Steve_TSQL/status/907713842943115264

Nate talks about copying large files like SQL Server backups using BITS with PowerShell

Warren talks about his experience as a beginner, the amount of things he automates and his DBReboot module

THANK YOU every single one and apologies if I have missed anyone!

 

 

Testing the Identity Column usage in SQL Server with PowerShell and dbatools

SQL Server uses identity columns to auto generate values, normally keys. When you create an identity column, it has a data type and that data type has a maximum number of values.

  • BigInt 9,223,372,036,854,775,808
  • Int 2,147,483,647
  • SmallInt 32,767
  • tinyint 255

What happens when you try to insert a value in an identity column that is greater than the maximum value? You get an error and a failed transaction. Lets do that

Using AdventureWorks, I know (I’ll show how in a minute) that the HumanResources.Shift column is a tinyint. So the highest value for the ShiftID column is 255.

If we run

USE AdventureWorks2014;
GO
INSERT INTO [HumanResources].[Shift]
([Name]
,[StartTime]
,[EndTime]
,[ModifiedDate])
VALUES
( 'Made Up SHift ' + CAST(NEWID() AS nvarchar(MAX))
,DATEADD(hour,-4, GetDate())
,'07:00:00.0000000'
,GetDate())
WAITFOR DELAY '00:00:00.050';
GO 252
Adding a number after GO says run this that many times, so we have added 252 rows to the existing 3 rows.
01 - maxx value.PNG

 

So what happens if we try to add another row?

USE AdventureWorks2014;
GO
INSERT INTO [HumanResources].[Shift]
([Name]
,[StartTime]
,[EndTime]
,[ModifiedDate])
VALUES
( 'Made Up SHift ' + CAST(NEWID() AS nvarchar(MAX))
,DATEADD(hour,-4, GetDate())
,'07:00:00.0000000'
,GetDate())
GO
02- error.PNG
We get an error
Msg 8115, Level 16, State 1, Line 4
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
If that is a table that is important to your system, a logging table or worse, an order table then there is quickly going to be phone calls, visits to your desks, arm waving etc until you get it resolved. Lets clean up our mess
USE AdventureWorks2014
GO
DELETE FROM HumanResources.Shift
WHERE ShiftId &gt; 3
GO
DBCC CHECKIDENT ('HumanResources.Shift', RESEED, 3)
GO
It would be very useful to be able to quickly see what the current values of the identity columns are and how close they are to being full so that we can plan for and be able to take action before we end up with shouty smart suits at our desk. If we could do it with just one line of code that would be even easier.
Step forward dbatools.  This PowerShell module is a community based project written by excellent, brilliant people in their own time and available to you free. To find out more and how to use and install it visit https://dbatools.io
There is a command called Test-DbaIdentityUsage This command was created by Brandon Abshire. You can find Brandon blogging at netnerds.net. Thank you Brandon
As always with a new PowerShell command you should always start with Get-Help
Get-Help Test-DbaIdentityUsage -ShowWindow
03 - get help.PNG

 

The command has a few parameters

  • SqlInstance – One or many Instances
  • SqlCredential – for SQL Authentication
  • Databases – to filter for databases ( This is a dynamic parameter and doesn’t show in the Help)
  • Threshold – define a minimum percentage for how full the identity column is
  • NoSystemDB – to ignore the system databases

So we can run the command against one instance

Test-DbaIdentityUsage -SqlInstance sql2014ser12r2

 

04 - one server.PNG

This returns an object for each identity column in each database on the instance. The object has the following properties

ComputerName   : SQL2014SER12R2
InstanceName   : MSSQLSERVER
SqlInstance    : SQL2014SER12R2
Database       : AdventureWorks2014
Schema         : HumanResources
Table          : Shift
Column         : ShiftID
SeedValue      : 1
IncrementValue : 1
LastValue      : 3
MaxNumberRows  : 254
NumberOfUses   : 3
PercentUsed    : 1.18

We can use the objects returned from this command in a number of ways, this is one of the beauties of PowerShell that we can interact with numerous systems. I have blogged about some simple ways of doing this here but your only limit is your imagination.

I love to use Out-GridView as it enables quick and easy sorting of the returned data

06 - ogv filter.gif

The databases parameter is dynamic so it will prefill the names of the databases on the instance. This is what it looks like in VS Code

07 vscode tab.gif

 

and in ISE

08 ise tab.gif

 

We can use the threshold parameter to only show results for the identity columns whose value is above a percent of the max value for the column. Lets fill the ShiftId column to above 90% and show this

USE AdventureWorks2014;
GO
INSERT INTO [HumanResources].[Shift]
([Name]
 ,[StartTime]
,[EndTime]
,[ModifiedDate])
VALUES
( 'Made Up SHift ' + CAST(NEWID() AS nvarchar(MAX))
,DATEADD(hour,-4, GetDate())
,'07:00:00.0000000'
,GetDAte())
WAITFOR DELAY '00:00:00.050';
GO 230

and now run

Test-DbaIdentityUsage -SqlInstance sql2014ser12r2&nbsp; -Threshold 90

08 - threshold.PNG

Don’t forget to use the cleanup script. You can pass a whole array of SQL instances to the command. We can pass an array of SQL servers to this command as well and check multiple servers at the same time. In this example, I am querying my Hyper-V server for all VMs with SQL in the name,except for my broken SQL2008 box ,that are running. Just to get some results I will set the threshold to 1

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
Test-DbaIdentityUsage -SqlInstance $SQLServers -Threshold 1 | Out-GridView
10 ogv thredshold.PNG
As you can see this function does not support SQL instances lower than SQL 2008 and you will get warnings for availability group databases
It’s quick too, finishing in less than 2 seconds in my lab of 10 SQL Servers and 125 databases. The WarningAction SilentlyContinue supresses the yellow warnings
11 - measure command.PNG
This is ideal for using Pester to test.
 Describe "Testing how full the Identity columns are" {
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
$testCases= @()
$SQLServers.ForEach{$testCases += @{Name = $_}}
It "<Name> databases all have identity columns less than 90% full" -TestCases $testCases {
Param($Name)
(Test-DbaIdentityUsage -SqlInstance $Name -Threshold 90 -WarningAction SilentlyContinue).PercentUsed | Should Be
}
}
12 pester test.PNG
An excellent quick test but it doesn’t show us which databases have failed. We can iterate through our servers and databases like this
Describe "Testing how full the Identity columns are" {
    $SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
    foreach($SQLServer in $SQLServers)
    {
        Context "Testing $SQLServer" {
            $dbs = (Connect-DbaSqlServer -SqlServer $SQLServer).Databases.Name
            foreach($db in $dbs)
            {
                It "$db on $SQLServer identity columns are less than 90% full" {
                    (Test-DbaIdentityUsage -SqlInstance $SQLServer -Databases $db -Threshold 90 -WarningAction SilentlyContinue).PercentUsed | Should Be
                }
            }
        }
    }
}
This is using the Connect-DbaSqlServer to create a SMO object and then gathering the databases on the server into a variable and iterating through them
It looks like this when it is running
13 - pester test.png
and at the end gives you a little overview of the number of tests that have failed
14 end of pester test.png
In a previous post I showed how you can output these results to XML or even make a HTML page showing the output
But perhaps that isn’t granular enough for you and you want a test for each column. This is how you could do that
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
foreach($SQLServer in $SQLServers)
{
    Describe "$SQLServer - Testing how full the Identity columns are" {
            $dbs = (Connect-DbaSqlServer -SqlServer $SQLServer).Databases.Name
            foreach($db in $dbs)
            {
                Context "Testing $db" {
                $Tests = Test-DbaIdentityUsage -SqlInstance $SQLServer -Databases $db -WarningAction SilentlyContinue
                foreach($test in $tests)
                {
                    It "$($test.Column) identity column in $($Test.Table) is less than 90% full" {
                        $Test.PercentUsed | Should BeLessThan 90
                    }
                }
            }
        }
    }
}
Which looks like this, a test for each identity column in each database in each server in your environment
15 every pester teest.PNG

 

The other question that we have to answer these days is – Does it work with SQL on Linux? We will have to pass a SQL authentication credential and this time I will use Format-Table for the output

 Test-DbaIdentityUsage -SqlInstance LinuxvNextCTP14 -SqlCredential (Get-Credential) | Format-Table

16 - on Linux.PNG

Happy Automating!

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

 

 

 

Using Pester with Get-DbaLastGoodCheckDb from dbatools

In my last post I showed Get-DbaLastGoodCheckDb  from dbatools. This module is a community based project written by excellent, brilliant people in their own time and available to you free. To find out more and how to use and install it visit https://dbatools.io

In a similar fashion to my post about using Pester with Test-DBALastBackup I thought I would write some Pester tests for Get-DbaLastGoodCheckDb as well

Pester provides a framework for running unit tests to execute and validate PowerShell commands from within PowerShell. Pester consists of a simple set of functions that expose a testing domain-specific language (DSL) for isolating, running, evaluating and reporting the results of PowerShell commands.

First we will use Test Cases again to quickly test a number of instances and see if any servers have a database which does not have a successful DBCC Checkdb. We will need to use the -Detailed parameter of Get-DbaLastGoddCheckDb so that we can access the status property. I have filled the $SQLServers variable with the names of my SQLServers in my lab that are running and are not my broken SQL2008 box.

The status property will contain one of three statements

  • Ok (This means that a successful test was run in the last 7 days
  • New database, not checked yet
  • CheckDb should be performed

We want to make sure that none of the results from the command have the second two statements. We can do this by adding two checks in the test and if either fails then the test will fail.

 Describe "Testing Last Known Good DBCC" {
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
$testCases= @()
$SQLServers.ForEach{$testCases += @{Name = $_}}
It "<Name> databases have all had a successful CheckDB within the last 7 days" -TestCases $testCases {
Param($Name)
$DBCC = Get-DbaLastGoodCheckDb -SqlServer $Name -Detailed
$DBCC.Status -contains 'New database, not checked yet'| Should Be $false
$DBCC.Status -contains 'CheckDb should be performed'| Should Be $false
}
}

We can save this as a .ps1 file (or we can add it to an existing Pester test file and call it will Invoke-Pester or just run it in PowerShell

05 - dbcc pester

As you can see you will still get the same warning for the availability group databases and we can see that SQL2012Ser08AG1 has a database whose status is CheckDB should be performed and SQL2012Ser08AGN2 has a database with a status of New database, not checked yet

That’s good, but what if we run our DBCC Checkdbs at a different frequency and want to test that? We can also test if the databases have had a successful DBCC CheckDb using the LastGoodCheckDb property which will not contain a Null if there was a successful DBCC CheckDb. As Pester is PowerShell we can use

($DBCC.LastGoodCheckDb -contains $null)

and we can use Measure-Object to get the maximum value of the DaysSinceLastGoodCheckdb property like this

($DBCC | Measure-Object -Property  DaysSinceLastGoodCheckdb -Maximum).Maximum
If we put those together and want to test for a successful DBCC Check DB in the last 3 days we have a test that looks like
Describe "Testing Last Known Good DBCC" {
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
$testCases= @()
$SQLServers.ForEach{$testCases += @{Name = $_}}
It "<Name> databases have all had a successful CheckDB" -TestCases $testCases {
Param($Name)
$DBCC = Get-DbaLastGoodCheckDb -SqlServer $Name -Detailed
($DBCC.LastGoodCheckDb -contains $null) | Should Be $false
}
It "<Name> databases have all had a CheckDB run in the last 3 days" -TestCases $testCases {
Param($Name)
$DBCC = Get-DbaLastGoodCheckDb -SqlServer $Name -Detailed
($DBCC | Measure-Object -Property  DaysSinceLastGoodCheckdb -Maximum).Maximum | Should BeLessThan 3
}
}
and when we call it with invoke-Pester it looks like
06 - dbcc pester.PNG
That’s good but it is only at an instance level. If we want our Pester Test to show results per database we can do that like this
Describe "Testing Last Known Good DBCC" {
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
foreach($Server in $SQLServers)
{
$DBCCTests = Get-DbaLastGoodCheckDb -SqlServer $Server -Detailed
foreach($DBCCTest in $DBCCTests)
{
It "$($DBCCTest.Server) database $($DBCCTest.Database) had a successful CheckDB"{
$DBCCTest.Status | Should Be 'Ok'
}
It "$($DBCCTest.Server) database $($DBCCTest.Database) had a CheckDB run in the last 3 days" {
$DBCCTest.DaysSinceLastGoodCheckdb | Should BeLessThan 3
}
It "$($DBCCTest.Server) database $($DBCCTest.Database) has Data Purity Enabled" {
$DBCCTest.DataPurityEnabled| Should Be $true
}
}
}
}
We gather the SQL instances into an array in the same way and this time we loop through each one, put the results of Get-DbaLastGoodCheckDb for that instance into a variable and then iterate through each result and check that the status is Ok, the DaysSinceLastGoodCheckDb is less than 3 and the DataPurityEnabled is true and we have
07 - dbcc pester.PNG

 

You can look at my previous posts on using Pester to see examples of creating XML files or HTML reports from the results of the tests.

Hopefully, as you have read this you have also thought of other ways that you can use Pester to validate the state of your environment. I would love to know how and what you do.

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

 

 

Getting SQLServers Last Known Good DBCC Checkdb with PowerShell and dbatools

As good SQL Server DBA’s we want to ensure that our databases are regularly checked for consistency by running DBCC CheckDB. This will be frequently scheduled using an Agent Job or by using Ola Hallengrens Maintenance Solution

We can check for the last known good DBCC Check using the undocumented DBCC DBINFO(DBNAME) WITH TABLERESULTS You can see the last known good DBCC Check around about row 50

00 - Using TSQL.PNG

This makes parsing the information a bit more tricky and whilst you could use sp_MSForEachDB to iterate through the databases that doesn’t always work as you expect as Aaron Bertrand explains

Of course, I am going to use PowerShell and also the dbatools module This module is a community based project written by excellent, brilliant people in their own time and available to you free. To find out more and how to use and install it visit https://dbatools.io

In the module there is a command called Get-DbaLastGoodCheckDb This command was created by Jakob Bindslet. You can find Jakob on his blog and on LinkedIn.

As always, you start with any PowerShell command by using Get-Help

get-help Get-DbaLastGoodCheckDb -ShowWindow

00a - get help.PNG

This command has three parameters Sqlserver, Credential and Detailed. Lets see what it looks like

Get-DbaLastGoodCheckDb -SqlServer SQLvNextN2

01 - One server

It returns an object with the server name, database name and the time and date of the last known good checkdb for every database on the server. What happens if we use the detailed parameter?

Get-DbaLastGoodCheckDb -SqlServer SQLvNextN2 -Detailed

 

02 - one server detailed.PNG

This time we get more information. The server name, database name, when the database was created, the last good DBCC Checkdb, how long since the database was created, how long since the last known good DBCC Checkdb, a status and a Data Purity enabled flag. If you look at the image above it shows that the DBA_Admin database has a status of “New database, not checked yet” even though it has a date for the last known good DBCC CheckDb. This is because it was restored after this server was upgrade from CTP 1.3 to CTP 1.4 and there has not yet been a DBCC CheckDb run yet. The system databases have a status of “CheckDb should be performed”. This is because the last known good DBCC CheckDb is more than 7 days ago. Lets run a DBCC CheckDb and check again

02a - one server.PNG

This time the status has changed to OK for all of the databases 🙂

We can pass an array of SQL servers to this command as well and check multiple servers at the same time. In this example, I am querying my Hyper-V server for all VMs with SQL in the name,except for my broken SQL2008 box ,that are running. I love PowerShell’s Out-GridView command for many reasons so lets use that. you can filter quickly and easily in the top bar.

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
Get-DbaLastGoodCheckDb -SqlServer $SQLServers -Detailed | Out-GridView

03 - many servers ogv.PNG

As you can see, you get a warning for secondary availability group databases. It’s quick too. In my lab of 10 servers and 125 databases ranging from SQL2005 to SQL vNext it runs in a little under  5 seconds. This command is not compatible with SQL2000 servers.

04 - measure comand.PNG

It is important to remember that as this script uses the DBCC DBINFO() WITH TABLERESULTS, there are several known weak points, including:

– DBCC DBINFO is an undocumented feature/command.
– The LastKnowGood timestamp is updated when a DBCC CHECKFILEGROUP is performed.
– The LastKnowGood timestamp is updated when a DBCC CHECKDB WITH PHYSICAL_ONLY is performed.
– The LastKnownGood timestamp does not get updated when a database in READ_ONLY.

Databases created prior to SQL2005 and then upgraded to SQL 2005 or above need to have DBCC CheckDb run once with the DATA_PURITY option to ensure that the DATA_PURITY check ,which look for column values where the value is outside the valid range of values for the column’s data type, is run by default when DBCC CheckDB is run. This is explained more fully by Paul Randal here and Ken Simmons here The Data Purity Enabled flag from the command will show false if the data purity check is not being performed. This should be resolved by running DBCC CheckDB with DATA_PURITY option as explained here

Now with one line of PowerShell code you can check the last time a DBCC CheckDb was run for each database on one or more instances. The beauty of PowerShell is that an object is returned which you can use in any number of ways as shown in a previous post

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

Get-Module dbatools

and update it using an Administrator PowerShell session with

Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

 

Test the SQL Server database collation with PowerShell and dbatools

If your server collation is different to your database collation then you may find that you get an error similar to this

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

when your queries use the tempdb

It would be useful to be able to quickly test if that may be the case and with the dbatools module you can. There is a Test-DbaDatabaseCollation command which will do just that. This page will show you how to install dbatools if you have not already got it

As always you should start with Get-Help when looking at a PowerShell command

Get-Help TestDbaDatabaseCollation -ShowWindow

 

01 - Get Help.PNG

There are only 3 parameters Sqlserver, Credential and detailed

Lets start with SQLServer

Test-DbaDatabaseCollation -SqlServer SQLvNextN2

this gives a quick and simple output showing the server name, database name and an IsEqual property

02 - test server

So in this example we can see that the WideWorldImporters database does not have the same collation as the server. If we only wanted to see information about databases with a collation that does not match the server then we could use

(Test-DbaDatabaseCollation -SqlServer SQLvNextN2).Where{$_.IsEqual -eq $false}

03 - equals false

That doesn’t give us any further information though. There is the detailed parameter as well. Lets see what that does

 Test-DbaDatabaseCollation -SqlServer SQLvNextN2 -Detailed

04 - detailed.PNG

This time we get the server name, server collation, database name , database collation and the IsEqual property. This is a collection of objects so we are not bound be just seeing them on the screen we can use them as I blogged about here

For example

 ## Output to a file
Test-DbaDatabaseCollation -SqlServer SQLvNextN2 -Detailed |Out-File C:\Temp\CollationCheck.txt
## Output to CSV
Test-DbaDatabaseCollation -SqlServer SQLvNextN2 -Detailed |Export-Csv  C:\temp\CollationCheck.csv -NoTypeInformation
<## Output to JSON
Test-DbaDatabaseCollation -SqlServer SQLvNextN2 -Detailed | ConvertTo-Json | Out-file c:\temp\CollationCheck.json
## Look at the files
notepad C:\temp\CollationCheck.json
notepad C:\temp\CollationCheck.csv
notepad C:\temp\CollationCheck.txt

Of course, you will probably want to test more than one server at a time. Lets pass an array of servers and see what happens

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
# Test Db collation
Test-DbaDatabaseCollation -SqlServer $SQLServers -Detailed 
05 - servers.PNG

In this example, I am querying my Hyper-V server for all VMs with SQL in the name,except for my broken SQL2008 box ,that are running. I love PowerShell’s Out-GridView command for many reasons. The ability to sort by columns quickly and simply is one of them. Lets add that to the code and sort by the IsEquals column

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
# Test Db collation
Test-DbaDatabaseCollation -SqlServer $SQLServers -Detailed | Out-GridView
06 - servers ogv.PNG

Excellent, that works a treat. How about Linux? Does this work if SQL is running on Linux? We will have to use the credential parameter as we need SQL Authentication. this time I have used the Format-Table command to format the output.

$cred = Get-Credential
Test-DbaDatabaseCollation -SqlServer LinuxvNextCTP14 -Credential $cred -Detailed | Format-Table -AutoSize
07 - Linux.PNG

Lets add some Pester tests. If we want to test a list of servers and see if any of their databases have an incorrect collation we can simply test if the IsEquals flag contains a false.

We can do this using TestCases. Test cases allow Pester to loop through a collection of ‘things’ The testcases parameter takes an array of hashtables. This all sounds very complicated to those unclear about PowerShell but here some code to do it.

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
$testCases= @()
$SQLServers.ForEach{$testCases += @{Name = $_}}

The first line gathers the list of SQL Servers from the Hyper-V as before. You can get this from a text file, csv, Active Directory, CMS, registered servers list. The second line initiates the TestCases array and the third line iterates through the list of servers and adds a hashtable to the TestCases array

To make use of the test cases we have to use the -TestCases parameter in our It block of our Pester Test and add a param() so that the test knows where to get the values from. To add the value from the test cases into the title of the test we need to reference it inside <>

If you want to learn more about Pester. I highly recommend The Pester Book by Don Jones and Adam Bertram

Here is the code

Describe "Testing Database Collation" {
    $SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
    $testCases= @()
    $SQLServers.ForEach{$testCases += @{Name = $_}}
    It "<Name> databases have the right collation" -TestCases $testCases {
        Param($Name)
        $Collation = Test-DbaDatabaseCollation -SqlServer $Name
        $Collation.IsEqual -contains $false | Should Be $false
    }
}

If we save that as a PowerShell file, we can call it with Invoke-Pester

08 - Servers Pester.PNG

which shows which servers do not have databases with the correct collation. This may be all that is required but what about if you want to check each database on each server with Pester?

I could not see a way to do this with TestCases so I reverted to PowerShell. Pester is just PowerShell code after all.

Describe "Testing Database Collation" {
    $SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
    foreach($Server in $SQLServers)
    {
        $CollationTests = Test-DbaDatabaseCollation -SqlServer $Server
        foreach($CollationTest in $CollationTests)
        {
            It "$($Collationtest.Server) database $($CollationTest.Database) should have the correct collation" {
                $CollationTest.IsEqual | Should Be $true
            }
        }
    }
}

In this example, we again gather the names of our SQL servers and then iterate through them. Then set the results of the Test-DBADatabaseCollation to a variable and iterate through each of the results and test the IsEquals property. We can save that as a file and call it with Invoke-Pester and this time it looks like

09 - Individual databases.PNG

Excellent we can quickly and easily see which database on which server doesnot have a matching collation. We cant see in the results of the Pester test what collation it should be though. Lets do that as well.

This time we need to use the Detailed parameter and test that the ServerCollation matches the DatabaseCollation. This will enable Pester to display that information to us. Here is the code

Describe "Testing Database Collation" {
    $SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
    foreach($Server in $SQLServers)
    {
        $CollationTests = Test-DbaDatabaseCollation -SqlServer $Server -Detailed
        foreach($CollationTest in $CollationTests)
        {
            It "$($Collationtest.Server) database $($CollationTest.Database) should have the correct collation of $($CollationTest.ServerCollation)" {
                $CollationTest.DatabaseCollation | Should Be $CollationTest.ServerCollation
            }
        }
    }
}
and if we save that as a file and call it with invoke-Pester (you can just run the code using PowerShell as well) it looks like this
10 - full test.PNG

Now Pester shows us what collation it is expecting and what the collation of the database is as well when it fails the test. (I love the little arrow showing where the difference is!)

Hopefully this post has shown you how you can use Test-DbaDatabaseCollation from the dbatools module to test your servers and combine that with Pester. If you have any questions about the dbatools module go and ask in the dbatools channel in the SQL Community Slack channel

Getting SQL Server File Sizes and Space Used with dbatools

I read a great blog post about answering the question how big is the database using T-SQL on SQL Buffet and wondered how much I could do with the dbatools module

The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present (11 March 2017 Version 0.8.938)

I know that there is a Get-DBADatabaseFreeSpace Command written by Mike Fal b | t and using Glenn Berry’s diagnostic queries

First thing as always is to look at the help

 Get-Help Get-DbaDatabaseFreespace -ShowWindow

which will show you the help for the command and some examples

Lets look at the details for a single instance

 Get-DbaDatabaseFreespace -sqlserver $server

This is what it looks like

02 - singel server.gif

and yes it really is that fast, I have not speeded this up. 232 ms to get those details for an instance with 19 databases

03 - Measure Command.PNG

What information do you get ? Lets look at the information for a single database, you get an object for each file

Server               : SQL2014SER12R2
Database             : DBA-Admin
FileName             : DBA-Admin_System
FileGroup            : PRIMARY
PhysicalName         : F:\DBA-Admin_System.MDF
FileType             : ROWS
UsedSpaceMB          : 3
FreeSpaceMB          : 253
FileSizeMB           : 256
PercentUsed          : 1
AutoGrowth           : 0
AutoGrowType         : MB
SpaceUntilMaxSizeMB  : 16777213
AutoGrowthPossibleMB : 0
UnusableSpaceMB      : 16777213
Server               : SQL2014SER12R2
Database             : DBA-Admin
FileName             : DBA-Admin_Log
FileGroup            :
PhysicalName         : G:\DBA-Admin_Log.LDF
FileType             : LOG
UsedSpaceMB          : 32
FreeSpaceMB          : 224
FileSizeMB           : 256
PercentUsed          : 12
AutoGrowth           : 256
AutoGrowType         : MB
SpaceUntilMaxSizeMB  : 2528
AutoGrowthPossibleMB : 2304
UnusableSpaceMB      : 0
Server               : SQL2014SER12R2
Database             : DBA-Admin
FileName             : DBA-Admin_User
FileGroup            : UserFG
PhysicalName         : F:\DBA-Admin_User.NDF
FileType             : ROWS
UsedSpaceMB          : 1
FreeSpaceMB          : 255
FileSizeMB           : 256
PercentUsed          : 0
AutoGrowth           : 256
AutoGrowType         : MB
SpaceUntilMaxSizeMB  : 5119
AutoGrowthPossibleMB : 4864
UnusableSpaceMB      : 0
There is a lot of useful information returned for each file. Its better if you use Out-GridView as then you can order by columns and filter in the top bar.
04 - single server ogv.gif

 

As always, PowerShell uses the permissions of the account running the sessions to connect to the SQL Server unless you provide a separate credential for SQL Authentication. If you need to connect with a different windows account you will need to hold Shift down and right click on the PowerShell icon and click run as a different user.

Lets get the information for a single database. The command has dynamic parameters which populate the database names to save you time and keystrokes

05 dynamic parameters.gif

But you may want to gather information about more than one server. lets take a list of servers and place them into a variable. You can add your servers to this variable in a number of ways, maybe by querying your CMDB or using your registered servers or central management server

$SQLServers = 'SQL2005Ser2003','SQL2012Ser08AG3','SQL2012Ser08AG1','SQL2012Ser08AG2','SQL2014Ser12R2','SQL2016N1','SQL2016N2','SQL2016N3','SQLVnextN1','SQLvNextN2'

and then

Get-DbaDatabaseFreespace -SqlInstance $SQLServers | Out-GridView

 

06 - Many servers ogv.PNG

As you can see, you get a warning quite correctly, that the information for the asynchronous secondary node of the availability group databases is not available and I did not have all of my servers running so there are a couple of could not connect warnings as well. You can still filter very quickly. dbatools is tested from SQL2000 to SQL vNext as you can see below (although I don’t have a SQL2000 instance)

07 - filter ogv

 

Not only on Windows, this command will work against SQL running on Linux as well

08 - linux.PNG

So if we want to know the total size of the files on disk for  the database we need to look at the FileSizeMB property

$server = 'SQL2014Ser12R2'
$dbName = 'AdventureWorksDW2014'
Get-DbaDatabaseFreespace -SqlServer $server -database $dbName |
Select Database,FileName,FileSizeMB

Of course that’s an easy calculation here

08a - filesize.PNG

but if we have numerous files then it may be tougher. we can use the Measure-Object command to sum the properties. We need to do a bit of preparation here and set a couple of calculated properties to make it more readable

$server = 'SQL2014Ser12R2'
$dbName = 'AdventureWorksDW2014'
$database = @{Name = 'Database'; Expression = {$dbname}}
$FileSize = @{Name = 'FileSize'; Expression = {$_.Sum}}
Get-DbaDatabaseFreespace -SqlServer $server -database $dbName |
Select Database,FileSizeMB |
Measure-Object FileSizeMB -Sum |
Select $database ,Property, $filesize

09 - filessize

Maybe we want to look at all of the databases on an instance. Again, we have to do a little more work here

$server = 'SQL2014Ser12R2'
$srv = Connect-DbaSqlServer $server
$SizeonDisk = @()
$srv.Databases |ForEach-Object {
$dbName = $_.Name
$database = @{Name = 'Database'; Expression = {$dbname}}
$FileSize = @{Name = 'FileSize'; Expression = {$_.Sum}}
$SizeOnDisk += Get-DbaDatabaseFreespace -SqlServer $server -database $dbName | Select Database,FileSizeMB |  Measure-Object FileSizeMb -Sum | Select $database ,Property, $Filesize
}
$SizeOnDisk

10 - size on disk

If we wanted the databases ordered by the size of their files we could do this

$SizeOnDisk |Sort-Object Filesize -Descending

11 - size sorted.PNG

As it is PowerShell we have an object and we can use it any way we like. Maybe we want that information in a text file or a csv or an excel file or in an email, PowerShell can do that

 ## In a text file
$SizeonDisk | Out-file C:\temp\Sizeondisk.txt
Invoke-Item C:\temp\Sizeondisk.txt
## In a CSV
$SizeonDisk | Export-Csv C:\temp\Sizeondisk.csv -NoTypeInformation
notepad C:\temp\Sizeondisk.csv
## Email
Send-MailMessage -SmtpServer $smtp -From DBATeam@TheBeard.local -To JuniorDBA-Smurf@TheBeard.Local `
-Subject "Smurf this needs looking At" -Body $SizeonDisk
## Email as Attachment
Send-MailMessage -SmtpServer $smtp -From DBATeam@TheBeard.local -To JuniorDBA-Smurf@TheBeard.Local `
-Subject "Smurf this needs looking At" -Body "Smurf" -Attachments C:\temp\Sizeondisk.csv

I had a little play with Boe Prox PoshCharts (you have to use the dev branch) to see if I could get some nice charts and unfortunately the bar charts did not come out so well but luckily the donut and pie charts did. (I’m a DBA I love donuts!)

$SizeonDisk| Out-PieChart -XField Database -YField FileSize -Title "UsedSpaceMB per Database on $Server" -IncludeLegend -Enable3D
$SizeonDisk| Out-DoughnutChart -XField Database -YField FileSize -Title "UsedSpaceMB per Database on $Server" -IncludeLegend -Enable3D

12 - donuts.PNG

So the point is, whatever you or your process requires you can pretty much bet that PowerShell can enable it for you to automate.

You can make use of all of the properties exposed by the command. If you want to only see the files with less than 20% space free

 Get-DbaDatabaseFreespace -SqlServer $server | Where-Object {$_.PercentUsed -gt 80}

13 - percent used.PNG

you can also use the command to check for file growth settings as well

 Get-DbaDatabaseFreespace -SqlServer $server | Where-Object {$_.AutoGrowType  -ne 'Mb'}

14 - autogrowth.PNG

Or maybe you want to know the FileSize, Used and Free Space per database

 $server = 'SQL2014Ser12R2'
$srv = Connect-DbaSqlServer $server
$SizeonDisk = @()
$srv.Databases |ForEach-Object {
$dbName = $_.Name
$database = @{Name = 'Database'; Expression = {$dbname}}
$MB = @{Name = 'Mbs'; Expression = {$_.Sum}}
$SizeOnDisk += Get-DbaDatabaseFreespace -SqlServer $server -database $dbName | Select Database,FileSizeMB, UsedSpaceMB, FreeSpaceMb |  Measure-Object FileSizeMb , UsedSpaceMB, FreeSpaceMb -Sum  | Select $database ,Property, $Mb
}
$SizeOnDisk 

15 totals.PNG

Hopefully that has given you a quick insight into another one of the fabulous dbatools commands. Any questions, comment below or head over to the SQL Server Community Slack via https://sqlps.io/slack

Happy Automating

 

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

 Install-Module dbatools

Then you can use

 Update-dbatools

 

Using Pester with dbatools Test-DbaLastBackup

In previous posts I have shown how to use Test-DbaLastBackup from dbatools and how you can make use of the results. Today we will look at using  Pester with the results

Pester provides a framework for running unit tests to execute and validate PowerShell commands from within PowerShell. Pester consists of a simple set of functions that expose a testing domain-specific language (DSL) for isolating, running, evaluating and reporting the results of PowerShell commands.

we shall use it to validate our results. First we need to gather our results as we have seen before, In this example I have set the MaxMb to 5 so change that if you are playing along

Import-Module dbatools
$TestServer = 'SQL2016N1'
$Server = 'SQL2016N2'
$servers = 'SQL2016N1','SQL2016N2'
$Results = $servers.ForEach{Test-DbaLastBackup -SqlServer $_ -Destination $TestServer -MaxMB 5}
Then we need to write some Pester Tests. I tried to use Test Cases which are the correct method to iterate through collections as Mike Robbins shows here but Pester does not accept the type of object that is returned from this command for that. It’s ok though, because Pester is just PowerShell we can use a foreach loop.
In this scenario, we are testing for failures rather than when the backup test has skipped due to the file path not being a network share or the size being greater than our max size, so our checks are using the Should Not assertion. I have also added a test for the time the backup was taken.
Describe "Last Backup Test results - NOTE THIS IGNORES Skipped restores,DBCC and BackupFiles" {
foreach($result in $results)
{
It "$($Result.Database) on $($Result.SourceServer) File Should Exist" {
$Result.FileExists| Should Not Be 'False'
}
It "$($Result.Database) on $($Result.SourceServer) Restore should be Success" {
$Result.RestoreResult| Should Not Be 'False'
}
It "$($Result.Database) on $($Result.SourceServer) DBCC should be Success" {
$Result.DBCCResult| Should Not Be 'False'
}
It "$($Result.Database) on $($Result.SourceServer) Backup Should be less than a week old" {
$Result.BackupTaken| Should BeGreaterThan (Get-Date).AddDays(-7)
}
}
If we run that we get an output like this. Green is Good Red is Bad 🙂
01 - pester script.PNG
We can save the script to a file and use the Invoke-Pester to call it like this.
Invoke-Pester C:\temp\BackupPester.ps1
(Some Restore Frames removed for brevity)
02 -invoke pester.gif
invoke-Pester can output results to a file so we can output to XML which can be consumed by many things
$Date = Get-Date -Format ddMMyyyHHmmss
$tempFolder = 'c:\temp\BackupTests\'
Push-Location $tempFolder
$XML = $tempFolder + "BackupTestResults_$Date.xml"
$script = 'C:\temp\BackupPester.ps1'
Invoke-Pester -Script $Script -OutputFile $xml -OutputFormat NUnitXml
will provide an XML file like this
04 - XML output.PNG
We can also make use of the reportunit.exe from http://relevantcodes.com/ to create pretty HTML files from the XML files we created
This piece of code will download and extract the file if it does not exist in the directory
#download and extract ReportUnit.exe
$url = 'http://relevantcodes.com/Tools/ReportUnit/reportunit-1.2.zip'
$fullPath = Join-Path $tempFolder $url.Split("/")[-1]
$reportunit = $tempFolder + '\reportunit.exe'
if((Test-Path $reportunit) -eq $false)
{
(New-Object Net.WebClient).DownloadFile($url,$fullPath)
Expand-Archive -Path $fullPath -DestinationPath $tempFolder
}
and this will run it against the XML and open the file
##run reportunit against report.xml and display result in browser
$HTML = $tempFolder  + 'index.html'
& .\reportunit.exe $tempFolder
Invoke-Item $HTML
which will look  like
03 - pretty html file.gif
Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

Taking dbatools Test-DbaLastBackup a little further

In a previous post I showed how easy it is to test your backups using Test-DbaLastBackup

Today I thought I would take it a little further and show you how PowerShell can be used to transmit or store this information in the manner you require

Test-DBALastBackup returns an object of information

SourceServer  : SQL2016N2
TestServer    : SQL2016N2
Database      : FadetoBlack
FileExists    : True
RestoreResult : Success
DbccResult    : Success
SizeMB        : 1243.26
BackupTaken   : 3/18/2017 12:36:07 PM
BackupFiles   : Z:\SQL2016N2\FadetoBlack\FULL_COPY_ONLY\SQL2016N2_FadetoBlack_FULL_COPY_ONLY_20170318_123607.bak

which shows the server, the database name, if the file exists, the restore result, the DBCC result, the size of the backup file, when the backup was taken and the path used

Text File

As it is an object we can make use of that in PowerShell. We can output the results to a file

Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1 -MaxMB 5 | Out-File C:\temp\Test-Restore.txt
notepad C:\temp\Test-Restore.txt

01 - out file.PNG

CSV

Or maybe you need a CSV

Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1 -MaxMB 5 | Export-Csv C:\temp\Test-Restore.csv -NoTypeInformation
02 - csv file.PNG

JSON

Maybe you want some json

Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1| ConvertTo-Json | Out-file c:\temp\test-results.json
06 - json results.PNG

HTML

Or an HTML page

$Results = Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1
$Results | ConvertTo-Html | Out-File c:\temp\test-results.html
03 - html.PNG

Excel

or perhaps you want a nice colour coded Excel sheet to show your manager or the auditors

Import-Module dbatools
$TestServer = 'SQL2016N1'
$Server = 'SQL2016N2'
## Run the test and save to a variable
$Results = Test-DbaLastBackup -SqlServer $server -Destination $TestServer
# Set the filename
$TestDate = Get-Date
$Date = Get-Date -Format ddMMyyy_HHmmss
$filename = 'C:\Temp\TestResults_' + $Date + '.xlsx'
# Create a .com object for Excel
$xl = new-object -comobject excel.application
$xl.Visible = $true # Set this to False when you run in production
$wb = $xl.Workbooks.Add() # Add a workbook
$ws = $wb.Worksheets.Item(1) # Add a worksheet
$cells = $ws.Cells
$col = 1
$row = 3
## Create a legenc
$cells.item($row, $col) = "Legend"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$row ++
$cells.item($row, $col) = "True or Success"
$cells.item($row, $col).font.size = 12
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 10
$row ++
$cells.item($row, $col) = "False or Failed"
$cells.item($row, $col).font.size = 12
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 3
$row ++
$cells.item($row, $col) = "Skipped"
$cells.item($row, $col).font.size = 12
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 16
$row ++
$cells.item($row, $col) = "Backup Under 7 days old"
$cells.item($row, $col).font.size = 12
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 4
$row ++
$cells.item($row, $col) = "Backup Over 7 days old"
$cells.item($row, $col).font.size = 12
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 3
## Create a header
$col ++
$row = 3
$cells.item($row, $col) = "Source Server"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Test Server"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Database"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "File Exists"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Restore Result"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "DBCC Result"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Size Mb"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Backup Date"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Backup Files"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col = 2
$row = 4
foreach ($result in $results) {
    $col = 2
    $cells.item($row, $col) = $Result.SourceServer
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $col ++
    $cells.item($row, $col) = $Result.TestServer
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $col++
    $cells.item($row, $col) = $Result.Database
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $col++
    $cells.item($row, $col) = $Result.FileExists
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    if ($result.FileExists -eq 'True') {
        $Cells.item($row, $col).Interior.ColorIndex = 10
    }
    elseif ($result.FileExists -eq 'False') {
        $Cells.item($row, $col).Interior.ColorIndex = 3
    }
    else {
        $Cells.item($row, $col).Interior.ColorIndex = 16
    }
    $col++
    $cells.item($row, $col) = $Result.RestoreResult
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    if ($result.RestoreResult -eq 'Success') {
        $Cells.item($row, $col).Interior.ColorIndex = 10
    }
    elseif ($result.RestoreResult -eq 'Failed') {
        $Cells.item($row, $col).Interior.ColorIndex = 3
    }
    else {
        $Cells.item($row, $col).Interior.ColorIndex = 16
    }
    $col++
    $cells.item($row, $col) = $Result.DBCCResult
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    if ($result.DBCCResult -eq 'Success') {
        $Cells.item($row, $col).Interior.ColorIndex = 10
    }
    elseif ($result.DBCCResult -eq 'Failed') {
        $Cells.item($row, $col).Interior.ColorIndex = 3
    }
    else {
        $Cells.item($row, $col).Interior.ColorIndex = 16
    }
    $col++
    $cells.item($row, $col) = $Result.SizeMb
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $col++
    $cells.item($row, $col) = $Result.BackupTaken
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    if ($result.BackupTaken -gt (Get-Date).AddDays(-7)) {
        $Cells.item($row, $col).Interior.ColorIndex = 4
    }
    else {
        $Cells.item($row, $col).Interior.ColorIndex = 3
    }
    $col++
    $cells.item($row, $col) = $Result.BackupFiles
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $row++
}
[void]$ws.cells.entireColumn.Autofit()
## Add the title after the autofit
$col = 2
$row = 1
$cells.item($row, $col) = "This report shows the results of the test backups performed on $TestServer for $Server on $TestDate"
$cells.item($row, $col).font.size = 18
$Cells.item($row, $col).Columnwidth = 10
$wb.Saveas($filename)
$xl.quit()

It looks like this. Green is Good, Red is Bad, Grey is don’t care!

Email

You might need to email the results, here I am using GMail as an example. With 2 factor authentication you need to use an app password in the credential

Import-Module dbatools
$TestServer = 'SQL2016N1'$Server = 'SQL2016N2'
## Run the test and save to a variable
$Results = Test-DbaLastBackup -SqlServer $server -Destination $TestServer -MaxMB 5
$to = ''
$smtp = 'smtp.gmail.com'
$port = 587
$cred = Get-Credential
$from = 'Beard@TheBeard.Local'
$subject = 'The Beard Reports on Backup Testing'
$Body = $Results | Format-Table | Out-String
Send-MailMessage -To $to -From $from -Body $Body -Subject $subject -SmtpServer $smtp -Priority High -UseSsl -Port $port -Credential $cred</pre>
07 -email

You can of course attach any of the above files as an attachment using the -attachment parameter in Send-MailMessage

Database

Of course, as good data professionals we probably want to put the data into a database where we can ensure that it is kept safe and secure

dbatools has a couple of commands to help with that too. We can use Out-DbaDataTable to create a datatable object and Write-DbaDatatable to write it to a database

Create a table

USE [TestResults]
GO
CREATE TABLE [dbo].[backuptest](
[SourceServer] [nvarchar](250) NULL,
[TestServer] [nvarchar](250) NULL,
[Database] [nvarchar](250) NULL,
[FileExists] [nvarchar](10) NULL,
[RestoreResult] [nvarchar](200) NULL,
[DBCCResult] [nvarchar](200) NULL,
[SizeMB] [int] NULL,
[Backuptaken] [datetime] NULL,
[BackupFiles] [nvarchar](300) NULL
) ON [PRIMARY]
GO

then add the data

Import-Module dbatools
$TestServer = 'SQL2016N1'
$Server = 'SQL2016N2'
$servers = 'SQL2005Ser2003','SQL2012Ser08AG1','SQL2012Ser08AG2','SQL2012Ser08AG3','SQL2014Ser12R2','SQL2016N1','SQL2016N2','SQL2016N3'
## Run the test for each server and save to a variable (This uses PowerShell v4 or above code)
$Results = $servers.ForEach{Test-DbaLastBackup -SqlServer $_ -Destination $TestServer -MaxMB 5}
## Convert to a daatatable.
$DataTable = Out-DbaDataTable -InputObject $Results
## Write to the database
Write-DbaDataTable -SqlServer $Server -Database TestResults -Schema dbo -Table backuptest -KeepNulls -InputObject $DataTable

and query it

08 - Database.PNG

Hopefully that has given you some ideas of how you can make use of this great command and also one of the benefits of PowerShell and the ability to use objects for different purposes

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

Testing Your SQL Server Backups the Easy Way with PowerShell & dbatools

In a previous post I wrote about how easy it was to restore a whole SQL Servers user databases from a  directory using the dbatools module. Maybe it is a good idea to look at for disaster recovery scenarios but even PowerShell is going to be useless if your backups don’t work

But setting up a solution to test your backups (technically test your restores) is difficult isn’t it?

Lets use the dbatools module and see how easy it is

The dbatools module has a command called Test-DbaLastBackup if you look at the page or at the help using

 

Get-Help Test-DbaLastBackup -ShowWindow

 

you will see that this command

Restores all or some of the latest backups and performs a consistency check

1. Gathers information about the last full backups
2. Restores the backups to the Destination with a new name. If no Destination is specified, the originating SqlServer will be used.
3. The database is restored as “dbatools-testrestore-$databaseName” by default, but you can change dbatools-testrestore to whatever you would like using -Prefix
4. The internal file names are also renamed to prevent conflicts with original database
5. A consistency check is then performed
6. And the test database is finally dropped

So, if you only have one SQL Server but want to ensure that you are testing your backup files then as along as you have the diskspace you can simply run

Test-DbaLastBackup -SqlServer sql2016n2

and the latest backups that have been taken will be restored using a different name with different filenames, checked for consistency and then dropped

01 - simple test backups.PNG

and as you can see an object is returned
SourceServer  : SQL2016N2
TestServer    : SQL2016N2
Database      : FadetoBlack
FileExists    : True
RestoreResult : Success
DbccResult    : Success
SizeMB        : 1243.26
BackupTaken   : 3/18/2017 12:36:07 PM
BackupFiles   : Z:\SQL2016N2\FadetoBlack\FULL_COPY_ONLY\SQL2016N2_FadetoBlack_FULL_COPY_ONLY_20170318_123607.bak

which shows the Server, the database, if the file exists, the restore result, the DBCC result, the size of the backup file, when it was taken and the path used

You don’t have to use the same server and in many shops you would not want to. You can specify a destination server and you can also pipe the results to Out-GridView to enable easy filtering.

Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1 | OGV

Note you need to be backing up to a shared location ie a path that starts \\ I have fudged this a little in the demo for the keen eyed

03 - with OGV.PNG

Maybe you only want to test the backups for the important databases or some backups are restored using other means and you don’t need to test them this way. There is a databases parameter which you can tab through the database names

04 - choosing databases.gif

In the ISE you can see the drop down of database names

05 - in ISE.PNG

If you have limited space you might not want to test the largest databases so you can use the MaxMb parameter to only restore databases under this size. In the example below, you can see that Fadetoblack was skipped and the system databases were skipped as they are not backing up to a shared location

06 - max mb.PNG

The databases are restored onto the server using a different name and the files are also named differently to avoid any conflicts. The default prefix is dbatools-testrestore- but you can change this using the prefix switch if you wish

02 data files.PNG

You may not want to use your special, super quick storage for performing your test restores. If you have separate data drives that you would like to use for the restores, you can specify those with the -DataDirectory and -LogDirectory. If you do not use these switches then the command will use the default data and log locations.

Its possible to reduce the amount of checks that are done. If you only want to do a Verify Only on the backup then you can use the -VerifyOnly switch, you can skip the DBCC check by using the -NoCheck switch and you can leave the test restore databases on the server using the -NoDrop switch

Happy Automating

 

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools