Automate your SQL Server Restore Tests with PowerShell, dbatools and PowerBI
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.
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.
In my repository database I have the following tables.
This database can stay in whatever server you have, but I usually place it in my Management Server.
In this database, I have the following table.
I also have the following view.
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.
Here it is where the magic really happens. My whole logic consists in a single function that I call.
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.
This piece of T-SQL goes inside an Invoke-Sqlcmd2.
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.
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.
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.
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.
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 🙂
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.
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.
Data Platform MVP
Posted on September 24, 2018, in Administração, dbatools, Dicas, GitHub, Powershell, Scripts, VirtualPass and tagged automate database restores, automate sql server restores, dbatools, SQL Server, sql server restores with PowerShell. Bookmark the permalink. 1 Comment.