Automate Testing Your Backups with DBATools

TheSurfingDBA 3 months ago 5 minute read time

We’ve all heard, "You need to test your backups!" To be honest, in any of the shops I’ve worked in, we never tested our backups. It wasn’t high on the priority list, until I came across a situation where compression was added to a file system I used to store my backups. That compression corrupted all the backups in that location, and it was at that point I decided to test them. I want to walk you through a process I built, using dbatools to automate testing of backups.

My experience has mainly been in large enterprises. Trying to work smarter and not harder, we have a central repository server, our single-point-of-truth database; I guess you could call it a "CMDB" strictly for the database environment. If the instance does not exist in my central repo, it does not exist in my environment. I know some folks will use Registered Servers, but I have never been a fan—dbatools does have cmdlets to interact with Registered Servers, but my code examples will not be using those. The link at the bottom of this article will take you to my blog where you can download the entire code example.

Prerequisites

You will need a location with enough drive space to restore your databases. If you have a 5TB database, you will need 5TBs of free space to restore the database which might be a deal breaker for some folks. In this scenario, we can do a restore with verifyonly to at least check the backup set is complete and can be read. If you need more information on this, see the link below.

Restore with VerifyOnly

My drive is 1TB in size for test restores. We can use dbatools to find how much free space we have on a drive and then use conditional logic to either do a full restore or a restore with verifyonly.

Testing your Backups

In the code snippet below, I have set the $Repo variable to the name of my central repository server. $Instances will be populated by the SQL Servers I have collected in my central repo database using the Invoke-DbaQuery cmdlet. I need to find out how much free space is in my restore location using the Get-DbaDiskSpace cmdlet. Due to how DBATools returns the value, I need to do some parsing and get it into decimal form. The $LeaveSpace variable is a value I want to compare the size of the DB against to see if I have room for a full restore or restore with verifyonly. I subtract 5% in the calculation of the $LeaveSpace variable to leave some room on the drive, just in case.

$Repo = '<Name of Central Repository Server>'
$InformationPreference = "Continue"
$CollectionTime = get-date

$Instances = Invoke-DbaQuery -SqlInstance $Repo -Database <Repo DB> -query "select Servername from servers.servers where is_sql = 1 and decomm = 0"

##find amount of free space on drive where you want to test your backups
[string]$SpaceFree = Get-DbaDiskSpace -ComputerName $repo |?{$_.Name -eq '<drive location>'} | select-object Free

##Parse out the exact value removing the "GB"
$Split = ($SpaceFree.Split(' '))[0]
[decimal]$Space = ($Split.Substring($Split.Indexof('=')+1))

##Take the Free space and substract 5% from that value to be safe
$LeaveSpace = ($space * 1024) - (($space * 1024) * .05)

We can loop through each instance and make the "magic" happen, populating the $DBs variable using the Get-DbaDatabase cmdlet. I am excluding system databases.

ForEach($Instance in $Instances)
    {
    Write-Information "Checking DBSizes on $($Instance.ServerName) to see if DB Sizes are smaller than $($LeaveSpace) as we only have a limited amount of drive space to execute Full Restores"

$DBs = Get-DbaDatabase -SqlInstance "$($Instance.ServerName)" `
     -ExcludeDatabase Master,MSDB,TempDB,Model | `
     select-object name, sizemb

We check to see if the database is too large to restore on our current drive location. If the size of the database is greater than the $LeaveSpace variable, a restore with verifyonly will be executed.

I populate the $BackupTest variable from the output of Test-DbaLastBackup and write this data to a table in my Central Repository server using the Write-DbaDataTable cmdlet. You can use the -autocreate parameter if you want DBATools to create the table in SQL. DBATools will create the table using only nvarchar(max) data types.

Foreach($DB in $DBs)
{
    if( $DB.sizeMB -gt $LeaveSpace)
                    {
##execute a verify only as we don't have enough storage for these DBs
write-information "$($instance.ServerName)...$($DB.Name) verifyonly as   sizeMB is $($DB.SizeMB) which is greater than $($LeaveSpace) "

$BackupTest = Test-DbaLastBackup -Destination $Repo -SqlInstance `
$($Instance.Servername) -Database $($DB.Name) -VerifyOnly `
-IgnoreLogBackup | Select SourceServer, TestServer, Database,`
FileExists,RestoreResult, DBCCResult, RestoreStart, RestoreEnd, `
DBCCStart, DBCCEnd, BackupDates, BackupFiles, `
@{label = "CollectionTime";expression={$($CollectionTime)}}

$BackupTest | Write-DbaDataTable -SqlInstance $Repo -Database <Repo DB> `
-Schema Collector -Table LastBackup -autocreate

                   }

Here are a couple of parameters to take note of for the Test-DBaLastBackup cmdlet.

Destination ---> where the backup will be restored

SQLInstance ---> instance in which the backups will be evaluated

VerifyOnly --> will execute a verifyonly restore

IgnoreLogBackup --> Only evaluate the last full backup

Prefix ---> Give the databases you restore a prefix

DataDirectory --> Where do you want the data files restored to?

LogDirectory --> Where do you want the log files restored to?

NoCheck --> Not shown below, but skips the CheckDB

The Else block will restore databases that our drive location can accommodate. It will also execute a CheckDB by default after the restore is complete.

else
    {
    ##full blown restore and checkdb as these are smaller DBs
    write-information "$($instance.ServerName)...$($DB.Name) full blown restore and checkdb as sizeMB is $($DB.SizeMB) which is less than $($LeaveSpace) "

BackupTest = Test-DbaLastBackup -Destination $Repo -SqlInstance `
$($instance.ServerName) -Database $($DB.Name) -Prefix LastBackup_ `
-IgnoreLogBackup -DataDirectory 'S:\DailyBackups\LastBackupDestination' `
-LogDirectory 'S:\DailyBackups\LastBackupDestination' | `
Select SourceServer, TestServer, Database, FileExists, RestoreResult, `
DBCCResult, RestoreStart, RestoreEnd, DBCCStart, DBCCEnd, BackupDates, `
BackupFiles, @{label = "CollectionTime";expression={$($CollectionTime)}}

$BackupTest | Write-DbaDataTable -SqlInstance $Repo -Database DBStats `
-Schema Collector -Table LastBackup -autocreate

Here is sample of the data captured by this process:

Data captured from the Test-DbaLastBackup

Automate your testing as a SQL Agent job

Schedule this process as a SQL Agent job. Using the above data, you can create an alert email to notify you and your team of any backups not restored or if CheckDB found corruption. Here is the link to the files if you want to use them for your environment. Thanks for reading and go and test your backups.

TheSurfingDBA Blog and Files for Testing your Backups