building a dedicated backup test server

So, there’s a World Backup Day, but what about World Restore Day? We’ve all been told over and over, backups are useless if they can’t be restored and verified. Grant Fritchey has even yelled it at us a couple times.

Using dbatools to automate tests

dbatools makes it crazy easy to automate your backup testing, as demonstrated by Sander Stad, Rob Sewell and Anthony Nocentino. It even works on Linux!

In my environment, I have a server dedicated for testing SQL Server backups and use Test-DbaLastBackup. Here’s how you can, too.

First, centralize your SQL Server backups

I use and recommend Ola Hallengren’s SQL Server Maintenance Solution to centralize database backups. Every SQL Server in my estate backs up its databases to a write-only UNC share.

If you’re required to backup locally, you can centralize your backups by automating the copy process from the SQL Servers’ local disks to a network share using PowerShell’s Copy-Item or robocopy. The reason this is required is because your dedicated test server must have access to the backups.

Next, build your server

Even though I built my server in a pretty simplified manner, I still learned a couple things along the way.

Edition

The edition (Enterprise, Standard, Express, etc) should match the highest of edition in your estate. My initial test server was Standard edition and a number of my Enterprise backups failed to restore because they were using Enterprise features.

• Licensing
• With regards to licensing, I recently spoke to Data Platform MVP Joey D’Antoni to get better clarification about required licensing. It appears that if you’ve got Software Assurance, creating a dedicated testing server for your backups would not require additional licenses, HOWEVER, you will have to skip the DBCC CHECKDB by specifying the -NoCheck parameter because CHECKDB is considered a “production offload process.”
• Alternatively, the SQL Server 2016 licensing model basically says that “anything that isn’t prod is free.” This means that, even without Software Assurance, you can use SQL Server 2016 as your test restore server without additional license requirements. But again, CHECKDB is considered production and is not covered under this licensing model.
• Ultimately, licensing this type of server is a “murky gray” area and you should consult with your organization’s licensing rep.

If you end up needing a higher edition of SQL Server, are properly licensed and the configuration is supported, you can always change your edition.

Features

Remember if you’ve got a database with FIELSTREAM enabled, you must have FILESTREAM enabled on your test sever. Same goes for FULLTEXT indexes and other things I can’t recall right now.

Service account

The SQL Server service account must have access to the centralized network share.

Disks

This was a lot of fun! I used Get-DbaDatabase to search all of my servers to easily find the largest database so that I could properly size my dedicated restore disk. I always keep a list of my SQL Servers as a variable in my $profile, so I just piped that. Check it:

$servers | Get-DbaDatabase | Select SqlInstance, Name, Size, Owner | Out-GridView

(Out-GridView, or OGV, is a powerful PowerShell cmdlet that allows for easy sorting, searching and filtering.)

Searching 15 SQL Servers and 315 databases took less than 4 seconds! The size is in MB, and in this example screenshot, the largest database is BDTlog at 16GB. This includes both data and log files. In reality, my largest database was 500GB, so I created a 700GB expandable restore disk.

In my environment, I setup just one additional disk and placed both the data and logs on that disk. Test-DbaLastBackup allows you to specify different destination data and log drives, however, so you can create two additional drives instead of one if you prefer.

tempdb

If you’re running the checks, you must also ensure you have enough space for tempdb to grow. It’s possible to estimate how much space you’ll need with DBCC CHECKDB(0) WITH ESTIMATEONLY (read more at sqlskills.com), but honestly, the estimation wasn’t even close to accurate for me and I ran out of space. I ended up throwing an extra 50GB at tempdb, which sufficed.

Update dbatools

So first, I literally updated Test-DbaLastBackup today, so make sure you are at least at version 0.8.946. This version is better in a variety of ways, but most importantly, it tests the entire available backup chain back to FULL and allows us to pipe in servers. Update/Edit It also fixes a bug from 0.8.946 that didn’t calculate all the logs.

So first, run either Update-Module -Name dbatools (if you’ve installed dbatools from the Gallery) or Update-dbatools if you’ve used the installer script.

Execute a one-liner

Next, it’s time to create the script that can

1. Collect all of your SQL Server database names
2. Test your entire SQL Server estate
3. Convert your results to a .NET DataTable
4. Write the results to a database

For my lab, I just keep a manual list of SQL Servers and all of them work with Windows authentication, so it greatly simplifies the process. Here’s how I test every backup and log it to the database, all in one fell swoop.

$servers | Test-DbaLastBackup -Destination localhost -DataDirectory R:\ -LogDirectory R:\ | ConvertTo-DbaDataTable | Write-DbaDataTable -SqlInstance localhost -Table dbatools.dbo.lastbackuptests -AutoCreateTable

Let’s break this command down

1. $servers: plain-text list of servers, though they could also be SMO server objects
2. Test-DbaLastBackup: performs all test restores and checkdbs on localhost. Restores both data and log files to the R:\ drive.
3. ConvertTo-DbaDataTable: Casts the results into a DataTable type (instead of being output to screen) so that they can be consumed by SQL Server
4. Write-DbaDataTable: Finally, the datatable is written to the dbatools database, to a table named lastbackuptests. If the table does not exist, it will be automatically created.

The output!

Here is a screenshot of the output, which has been saved to a SQL Server database:

What’s just so cool about this is that, in addition to the backup testing, I can also see how long a database will take to restore on a sub-optimized system, how long a CHECKDB will take and how long an entire instance would take to restore from backups.

Ultimately, it takes about 8 hours to test my entire SQL Server estate. I can check my results the next morning and rest easy knowing that my backups can be restored and the resulting data is corruption free!

Scheduling

Test restores, like entire instance migrations, can be scheduled. Check out the post scheduling a migration for detailed instructions on scheduling PowerShell tasks.

You can even email yourself the results using Send-MailMessage. If you do go this route, ensure you assign the output of Test-DbaLastBackup to a variable so that you can use it to both write to SQL Server and email yourself (ConvertTo-Html will probably be helpful here).

Caveats

If you don’t backup directly to centralized storage, you’ll need to write your own version of Test-DbaLastBackup. Get-DbaDbBackupHistory -Last, Restore-DbaDatabase and Invoke-DbaQuery (for DBCC CHECKDB) will be helpful here.

Also, if you need to use alternative credentials to login to SQL Server, you’d need to do something like

$credential = (Get-Credential sqladmin)
$localcredential = (Get-Credential ad\myadminacct)

foreach ($server in $servers) { 
    Connect-DbaSqlServer -SqlInstance $server -SqlCredential $credential | 
    Test-DbaLastBackup -Destination localhost -DataDirectory R:\ -LogDirectory R:\ -DestinationCredential $localcredential | 
    ConvertTo-DbaDataTable | 
    Write-DbaDataTable -SqlInstance sql2016 -Table tempdb.dbo.lastbackuptests -AutoCreateTable
}

Video

Want to see Test-DbaLastBackup in action? Check out this sample video. The output is a little outdated but you can still see how cool the whole thing is.

Conclusion

Test-DbaLastBackup simplifies one of a DBA’s the most important tasks. It’s also pretty fun 👯! Impress your boss and your friends by rolling your very own dedicated testing solution.

- Chrissy