By: Mohammed Moinudheen | Updated: 2020-07-10 | Comments | Related: > Azure Backup and Restore
Problem
I have SQL related files on one of my Azure SQL VM and I would like to restore those files locally on my test server. Is it possible to restore those files on to my test server as the Azure SQL VM is already backed up using Recovery Services Vault? What are the steps I need to perform to accomplish this task?
Solution
In this tip, you saw step by step instructions on how to restore an entire Azure SQL VM that is backed up using Recovery Services Vault. If you refer to this tip, in the section under 'Steps to restore the Azure SQL VM', you will see a screenshot where there is an option to perform a 'file recovery'.
In this tip, we will learn about this 'file recovery' option. This tip assumes that you already have resources in your Azure environment and you are familiar with using the Azure portal. If you are new to Azure, you can subscribe to a trial account here, which will give you access to Azure services for a year and a credit of USD $200. You can refer to this link where you can see detailed step by step instructions to set up a Recovery Services Vault.
This tip assumes that you already have an Azure SQL VM and it is backed up using Recovery Services Vault.
Checking if an Azure SQL VM is backed up using Recovery Services Vault
You can check if an Azure VM is backed up by clicking on the 'Backup' option under the 'Operations' section on the left-hand pane as shown. From this screenshot, you can see that the backup is not yet enabled for the Azure VM as the Recovery Services Vault itself is not created.
If the backup is enabled and if the Recovery services vault is already configured, then the screenshot would be something like below. Here, you can see that the backup is enabled and the pre-checks have passed and the last backup status is also showing as successful.
SQL related files on Azure SQL VM
In this demo, we will try to recover a few SQL related files from the data folder present on the SQL VM.
There are few SQL files on the data drive (F).
Step 1: Select Recovery Point to Perform File Recovery
For performing a file recovery, click on the 'File Recovery' option that is available on the 'Backup' Operations option on the Azure VM. Just make sure you perform these operations on the server where you want to restore the files.
Once you click on 'File Recovery', you will see this screen where you need to select the recovery point.
When you click on the drop down under 'Select Recovery Point', you will see the various restore points that are available to use. Select the one you need, in this demo, I used the latest one available.
Step 2: Download Executable
Once you select the recovery point, go to the next step which will download the required scripts to recover the files. Click on the 'Download Executable' option.
Once you click on 'Download Executable', a process to generate the script and password will start.
This process runs for a few minutes and once it completes you will get this confirmation.
You can see there is an option to 'Download', click on it, and also copy the password which you need to run the script. Once you click on 'Download', the script file gets downloaded and you can then open the file.
When you click on 'Open file', a new window will open prompting you for a password.
This is the password that was generated when you ran the step to download the executable. This is a long password that gets generated, just use the option to copy the password to the clipboard as it is much easier. Paste the copied password to the prompt and press 'Enter'. Once you input the generated password, the process to attach the volumes starts and completes in a few minutes. Once the process completes, you will get this screen.
Here, you can see that the volumes from the SQL VM get attached. The volumes on the actual SQL VM are as shown below.
The screenshot below is from the test server where the volumes are attached. You can see, the attached volumes on the test server have different drive letters.
However, the volume names remain the same. This way, you will be able to identify the correct drive and the required files. The files we require in this demo are in the SQLVMDATA1 drive and we can check the drive to confirm that the files exist.
As you can see, from the screenshot, the files that we require are present in this drive. In this demo, you saw how easy it is to recover files from an Azure VM that is backed up using Recovery Services Vault.
Step 3: Unmount Disks
Once you are done with the recovery steps, you can unmount the disks by clicking on Step 3.
This will trigger the process to unmount the disks and it completes quite quickly. Once this is done, you will no longer see those mounted disks. With this, you saw the steps required to perform a file recovery for files in an Azure VM that is backed up using the Recovery Services Vault.
Next Steps
- In this tip, you saw a reference to a tip on creating Azure Recovery Services Vault step by step
- In this tip, you saw a reference to a tip to restore an Azure SQL VM using Recovery Services Vault
- In the demo, you learned how file recovery is possible for an Azure SQL VM that is backed up using Recovery Services Vault
- In the demo, you learned how to check if backup is enabled for an Azure SQL VM or not
- In the demo, you saw how the volumes get mounted on the server where you wish to perform the restore
- In the demo, you also saw that the mounted volume names remain the same as that of the original SQL VM on the server where the recovery is done
- Try out this demo on your own Azure environment
- Refer other articles available on MSSQLTips related to Azure
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-07-10
