You are required to restore a workload from Production to a QA Lab; but, you have a concern about restoring private, or classified, data? Can you restore a Production workload, and at the same time remove the private, or classified, data without running through a very long process? Perhaps you could automate the process?
In this week’s blog post, I want to tell you about a great feature, that in my opinion, hasn’t got the appropriate attention it needs. We did hear about the European GDPR standard (General Data Protection Regulation, European Union), which draws less attention for those of us outside Europe, Stage Restore can be also helpful and useful to anyone trying to perform a restore and not necessary concerning about GDPR.
With Stage Restore, the Backup Admins can restore any workload to a temporary location. While in this temporary location, a custom script can be run to alter the restored data before restoring it back to the Production environment. The capability to alter the data before restoring it is a help in many scenarios, such as:
- Compliance
- Security
- Privacy, and more
Before I begin the demonstration showing you how to configure this feature, let’s consider the following scenario I encountered with one of my customers a couple of weeks ago. The scenario goes like this; as a Backup Admin, you are requested to restore the company SQL server to the QA Lab. From the QA Lab, the company DB and QA team are going to test a newly developed tool; but before they do that, the management team has requested the following modifications to the data:
- The Finance Databases must be removed from the restored VM; and
- The HR_Holidays table from the HR DB must be removed
To accomplish that modification to the data, you use the stage restore, an easy task, as follows.
Preparing your Script
To get started, we must create our script to delete the Finance Database and the HR_Holidays table from the DR database. Let’s create a simple script called sqlScript.cmd to accomplish this task:
sqlcmd -d Master -Q “DROP Database FinanceDB”
sqlcmd -d HRDB -Q “DROP TABLE HR_Holidays”
exit 0
Note: To successfully complete the stage restore, you are also required to create a virtual lab. To learn more about creating a virtual lab, browse to this link.
Restore the SQL server to the QA Lab
After the scripts have been created and saved on the backup server, we can start configuring the restore job. See below:
Chose the VM you are going to Restore; in our example here, it is the SQL server CrmSqlSrv, and we chose to Restore entire VM…
Select the radio button for Staged restore from the Restore Mode options.
Select the QA Esxi host to where you will restore the VM:
In this example, from the Host tab, our VM location Name is CrmSqlSrv, and our host is nucesxi06.oasis.org
Select the Datastore where you are going to restore your SQL server:
In this example, the Datastore is Hard disk 1 on CrmSqlSrv.
Attach the VM to the Network at the QA lab infrastructure:
In this example, our Network connections are Source, CrmSqlSrv ProdWorkLoad (vDProdSwitch) and the Target is SPWorkload (vDOmnitraSwitch)
and then point to the script in Staged Restore:
In this example, remember the script name we created earlier, sqlScript.cmd
and then start the restore process:
After the restore process has completed, the SQL server will be restored and ready for the QA and DB team, without the Finance Databases and the HR_Holidays table:
Summary
Staged Restore is a great feature that can be used in many use cases beyond the GDPR requirement, as I have demonstrated here on this blog post. The customer was very happy with the outcome, as he achieved several goals with one restore process. First, he simplified the restore process. Second, he protected the privacy of the company employees, and finally, made the management team happy over their privacy concern using straightforward steps. A very good day all round.
