Automate your SQL Server Restore Tests with PowerShell, dbatools and PowerBI

Hello everyone,

At work, I got to do some really interesting stuff. What I like is they really give me the freedom to try things out. Ah, and if you wanna join us, we have a position to work with me, posted here: https://grnh.se/4899e2091.

There is a saying, which I don’t exactly remember, is that a backup is only a backup if you test the restore of the same. So, I was working on creating an automated way of restoring the backups of my production databases and give me some sort of reporting around it.

So, I did that work and it is running really fine with the structure that I have. Then, I had the idea to share what I did with the community, mainly because I rely on almost all of my work to dbatools, and without them I would have more trouble/work do, to get the same results.

I shared a screenshot on twitter as you can see below.
image
link: https://twitter.com/marcosfreccia/status/1037031233262374914

I got a few replies from people saying that they would like to see how it is done. So, in this blog post, I’ll explain you all the steps that I had to do, to obtain this result.

The base tables

Everything starts with base tables that helps me to pull out data to my PowerShell Scripts.

DBARepository Database

In my repository database I have the following tables.

  • Applications

image

  • ServerOverview

image

  • Servers

image

This database can stay in whatever server you have, but I usually place it in my Management Server.

DBAUtils Database

In this database, I have the following table.

  • restore_database_staging

image

I also have the following view.

  • vwrestore_database

image

If you didn’t notice, the only difference is the conversion of Restore and DBCC Elapsed from data type TIME(0) to an integer based column.

You may ask: Why another database? For it is pure segregation of duties, the repository in my case means for storing metadata information and the utils database, for storing work and other stuff that I do.

Don’t worry about the script for creating the objects, I placed a link to the Github repository at the end of this article.

PowerShell Script

YsbKHg1 

Here it is where the magic really happens. My whole logic consists in a single function that I call.

  • Test-SQLBackups

This function needs the following parameters to work.

$AutomationServer = “srvsql2017” –> This is where the DBARepository and DBAUtils DB’s are located.
$RepositoryDB = “DBARepository” –> This is where I have the environment information .
$AutomationDB = “DBAUtils” –> Database where the restore and checks results will be stored.
$TargetRestoreServer2016 = “SRVSQL2017\DEV” –> Target Instance for restoring SQL 2016 DB’s.
$TargetRestoreServer2017 = “SRVSQL2017\DEV” –> Target Instance for restoring SQL 2017 DB’s.
$TargetResultsTable = “[dbo].[restore_database_staging]” –> Table in DBAUtils where I store the results of the restore and integrity checks.
$PurgeResultsTable  -> If you use this switch the table restore_database_staging will be truncated.

 

Querying DBARepository Database

An important part of the script is retrieving the database information to perform the restore. For that, I execute the following T-SQL.

image

This piece of T-SQL goes inside an Invoke-Sqlcmd2.

DbaTools

As previously said, the PowerShell part is relying entirely on dbatools. The function that I mainly use in here, is: Test-DbaLastBackup, which the website describes as:

Quickly and easily tests the last set of full backups for a server.

 

If you want to know more, just click on the above link for a more comprehensive explanation.

In addition to that, I also use Invoke-Sqlcmd2, to query the DbaRepository database, but it could be for this case changed to Invoke-Sqlcmd or Invoke-DbaQuery. You are free to use your preferred.

PowerBI Report

The last piece of this solution is the report, where I simply connect to the DBAUtils database and select the view: vwrestore_database.

After this view is imported, I created the following measures in my report.

  • Total Restores = COUNT(‘vwrestore_database'[Database])
  • Successful Restores = COUNTROWS(FILTER(‘vwrestore_database’,’vwrestore_database'[Restore Result]=”Success”))
  • Restore Success Rate = [Successful Restores] / [Total Restores]
  • Failed Restores = IF(ISBLANK(COUNTROWS(FILTER(‘vwrestore_database’,’vwrestore_database'[Restore Result]=”Failed”))),0,COUNTROWS(FILTER(‘vwrestore_database’,’vwrestore_database'[Restore Result]=”Failed”)))
  • Failed Restore Rate = [Failed Restores] / [Total Restores]
  • Successful DBCC’s = COUNTROWS(FILTER(‘vwrestore_database’,’vwrestore_database'[DBCC Result]=”Success”))
  • Restore Integrity Success Rate = [Successful DBCC’s] / [Total Restores]
  • Failed DBCC’s = IF(ISBLANK(COUNTROWS(FILTER(‘vwrestore_database’,’vwrestore_database'[DBCC Result]=”Failed”))),0,COUNTROWS(FILTER(‘vwrestore_database’,’vwrestore_database'[DBCC Result]=”Failed”)))
  • Failed DBCC Rate = [Failed DBCC’s] / [Total Restores]

The end result of this solution, gives you the below report.

image

Scheduling the execution

I test the recovery of the databases on a monthly basis, but that’s up to you to change if necessary. If you do more than once in a month or less than every month, probably you will have to change filters in your PowerBI report.

If you do once in a month, and you keep the restore tests history, you will need this following filter.

image

I am using the filter on a Visual Level, but it works in any level you want.

SQL Agent Job

Regarding the SQL Server Agent Job, I few pictures below.

image
image
image
image

Known Issues?

The only thing so far that you need to be aware of is that Test-DbaLastBackup will place all the data files in the same drive. So, if you have different data files on different disks, the function is not able to Reuse the source folder structure, like you can do in Restore-DbaDatabase.

For this case, you will need a big disk drive to restore your databases.

I have opened an issue here: Extend Parameter options of Test-DbaLastBackup asking for this switch from Restore-DbaDatabase to be ported. Maybe it is my time to start contributing to dbatools 🙂

 

GitHub Repository

The 1 million dollar question is: Where can I find this? Below you can find the link to the GitHub Repository to where all the scripts and report are available.

https://github.com/marcosfreccia/sql-server-restore-tests-automation

If you have questions or have any feedback, leave a comment below. I would love to see if people are using it and find it this solution interesting.

Regards,
Marcos Freccia
Data Platform MVP

About Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Posted on September 24, 2018, in Administração, dbatools, Dicas, GitHub, Powershell, Scripts, VirtualPass and tagged , , , , . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: