This is just a quick post. As is frequent with these they are as much for me to refer to in the future and also because the very act of writing it down will aid me in remembering. I encourage you to do the same. Share what you learn because it will help you as well as helping others.
Anyway, I was writing some Pester tests for a module that I was writing when I needed some sample data. I have written before about using Json for this purpose This function required some data from a database so I wrote the query to get the data and used dbatools to run the query against the database using Get-DbaDatabase
Which meant that I could easily separate it for mocking in my test. I ran the code and investigated the $variable variable to ensure it had what I wanted for my test and then decided to convert it into JSON using ConvertTo-Json
$query = @"
SELECT Top 10 CityName, StateProvinceName, sp.LatestRecordedPopulation, CountryName
FROM Application.Cities AS city
JOIN Application.StateProvinces AS sp on
city.StateProvinceID = sp.StateProvinceID
JOIN Application.Countries AS ctry on
sp.CountryID=ctry.CountryID
WHERE sp.StateProvinceName = N'Virginia'
ORDER BY CityName
"@
$db = Get-DbaDatabase -SqlInstance rob-xps -Database WideWorldImporters
$variable = $db.Query($Query)
If I investigate the $variable variable I get
The results were just what I wanted so I thought I will just convert them to JSON and save them in a file and bingo I have some test data in a mock to ensure my code is doing what I expect. However, when I run
$variable | ConvertTo-Json
I get
and thats just for one row!
The way to resolve this is to only select the data that we need. The easiest way to do this is to exclude the properties that we don’t need
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
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
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>
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
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