Taking dbatools Test-DbaLastBackup a little further

Posted on March 22, 2017 by RobSewell

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

CSV

Or maybe you need a CSV

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

JSON

Maybe you want some json

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

HTML

Or an HTML page

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

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>

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

Install-Module dbatools

Then you can use

Update-dbatools

Rationalisation of Database with Powershell and T-SQL part one

Posted on February 25, 2014 by RobSewell

I have recently been involved in a project to rationalise databases. It is easy in a large organisation for database numbers to rapidly increase and sometimes the DBA may not be aware of or be able to control the rise if they don’t have knowledge of all of the database servers on the estate.

There are lots of benefits of rationalisation to the business. Reduced cpu usage = reduced heat released = lower air-con bill for the server room and less storage used = quicker backups and less tapes used or better still less requirement for that expensive new SAN. You may be able to consolidate data and provide one version of the truth for the business as well. Removing servers can release licensing costs which could then be diverted elsewhere or pay for other improvements.

William Durkin b | t presented to the SQL South West User Group about this and will be doing the session at SQL Saturday in Exeter in March 2014 Please check out his session for a more detailed view

I needed to be able to identify databases that could possibly be deleted and realised that an easy way to achieve this would be to use a script to check for usage of the database.

No need to recreate the wheel so I went to Aaron Bertrands blog http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx and used his script. Instead of using an audit file I decided to use Powershell so that I could output the results to Excel and colour code them. This made it easier to check the results and also easier to show to Managers and Service Owners

#################################################################################
# NAME: lastdbusage.ps1
# AUTHOR: Rob Sewell
# http://sqldbawithabeard.com
# DATE:19/10/2013
#
# COMMENTS: Fill Excel WorkBook with details fo last access times for each database
#
# NOTES : Does NOT work with SQL 2000 boxes
$FileName = '' # Set a filename for the output
# Get List of sql servers to check
$sqlservers = Get-Content '' # serverlist, database query whatever

# Set SQL Query
$query = "WITH agg AS
(
SELECT
max(last_user_seek) last_user_seek,
max(last_user_scan) last_user_scan,
max(last_user_lookup) last_user_lookup,
max(last_user_update) last_user_update,
sd.name dbname
FROM
sys.dm_db_index_usage_stats, master..sysdatabases sd
WHERE
sd.name not in('master','tempdb','model','msdb')
AND
database_id = sd.dbid group by sd.name
)
SELECT
dbname,
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT dbname, last_user_seek, NULL FROM agg
UNION ALL
SELECT dbname, last_user_scan, NULL FROM agg
UNION ALL
SELECT dbname, last_user_lookup, NULL FROM agg
UNION ALL
SELECT dbname, NULL, last_user_update FROM agg
) AS x (dbname, last_read, last_write)
GROUP BY
dbname
ORDER BY 1;
"
#Open Excel
$xl = new-object -comobject excel.application
$wb = $xl.Workbooks.Add()

# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

# Loop through each sql server from sqlservers.txt
foreach ($sqlserver in $sqlservers) {
    # Get the time SQL was restarted
    $svr = New-Object 'Microsoft.SQLServer.Management.Smo.Server' $SQLServer
    $db = $svr.Databases['TempDB']
    $CreateDate = $db.CreateDate

    #Run Query against SQL Server
    $Results = Invoke-Sqlcmd -ServerInstance $sqlServer -Query $query -Database master
    # Add a new sheet
    $ws = $wb.Worksheets.Add()
    $name = "$sqlserver"
    # Name the Sheet
    $ws.name = $Name
    $cells = $ws.Cells
    $xl.Visible = $true
    #define some variables to control navigation
    $row = 2
    $col = 2
    $cells.item($row, $col) = $SQLServer + ' Was Rebooted at ' + $CreateDate
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $row = 3
    $col = 2
    # Set some titles
    $cells.item($row, $col) = "Server"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $col++
    $cells.item($row, $col) = "Database"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 40
    $col++
    $cells.item($row, $col) = "Last Read"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 20
    $col++
    $cells.item($row, $col) = "Last Write"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 20
    $col++

    foreach ($result in $results) {
        # Check if value is NULL
        $DBNull = [System.DBNull]::Value
        $LastRead = $Result.last_read
        $LastWrite = $Result.last_write

        $row++
        $col = 2
        $cells.item($Row, $col) = $sqlserver
        $col++
        $cells.item($Row, $col) = $Result.dbname
        $col++
        if ($LastRead -eq $DBNull) {
            $LastRead = "Not Since Last Reboot"
            $colour = "46"
            $cells.item($Row, $col).Interior.ColorIndex = $colour
            $cells.item($Row, $col) = $LastRead
        }
        else {
            $cells.item($Row, $col) = $LastRead
        }
        $col++
        if ($LastWrite -eq $DBNull) {
            $LastWrite = "Not Since Last Reboot"
            $colour = "46"
            $cells.item($Row, $col).Interior.ColorIndex = $colour
            $cells.item($Row, $col) = $LastWrite
        }
        else {
            $cells.item($Row, $col) = $LastWrite
        }
    }
}

$xl.DisplayAlerts = $false
$wb.Saveas($FileName)
$xl.quit()
Stop-Process -Name *excel*

What it does is place the query in a variable. Get the contents of the SQL Server text file holding all my known SQL Servers and runs the query against each of them storing the results in a variable. It then creates an Excel Workbook and a new sheet for each server and populates the sheet including a bit of colour formatting before saving it. The results look like this

The tricky bit was understanding how to match the NULL result from the query. This was done by assigning a variable to [System.DBNull]::Value and using that.

Of course these stats are reset when SQL Server restarts so I also included the SQL server restart time using the create date property of the TempDB. I gathered these stats for a few months before starting any rationalisation.

My next post will be about the next step in the process. You can get the script here

SQL DBA with A Beard

He's a SQL DBA and he has a beard

Tag Archives: Database

Taking dbatools Test-DbaLastBackup a little further

Text File

CSV

JSON

HTML

Excel

Email

Database

Like this:

Rationalisation of Database with Powershell and T-SQL part one

Like this:

Text File

CSV

JSON

HTML

Excel

Email

Database

Share this:

Like this:

Share this:

Like this: