Restore an Azure SQL VM using Recovery Services Vault


By:   |   Updated: 2020-05-07   |   Comments   |   Related: More > Azure


Problem

I see that I need to restore an Azure SQL VM to a point in time. I had already backed up the Azure VM using the Recovery Services vault. What are the steps I need to perform to accomplish this task?

Solution

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 this link which provides a detailed step by step instructions for creating a Windows based Azure VM. The steps are straight forward and you can get a SQL Server Azure VM running in a few minutes. You can refer to this link where you can see detailed step by step instructions to set up a Recovery Services Vault and backup SQL databases on SQL Server. In this link you can see detailed step by step instructions to restore a SQL database that was backed up using the Recovery Services Vault.

Backup an Azure SQL VM using Recovery Services Vault

This tip assumes you already have an existing Azure SQL VM running and it is backed up using Recovery Services Vault as described in the referral links mentioned above. Just make sure that you select this option to back up the entire VM.

microsoft azure

After selecting this option, go through the sequence of steps as described in the earlier link to enable backup for the virtual machine. This will ensure that your Azure VM is backed up and you will be able to continue with this demo. When you click on the 'Backup Items' option on the Recovery Services Vault, you will be able to view details of the Azure virtual machine as shown.

microsoft azure

You can click on the 'Azure Virtual Machine' option to check if the VM is backed up. Here, you can see confirmation of the backup status. In your case, if the backup is not done yet, you may trigger a manual backup by clicking on the ellipsis on the right-hand corner.

azure virtual machine

From the screenshot, you can see the backup for the Azure VM is completed and details of the latest restore point is available.

Databases on the Azure SQL VM before performing backup

For the purpose of this demo, I had taken a screenshot of the databases on the Azure SQL VM before the backup was performed using the Recovery Services Vault.

sql server

You can see couple of databases – A and B. After the backup was performed, I created two more databases as shown. These new databases will not be part of the backup available on the Recovery Services Vault.

sql server

Steps to restore the Azure SQL VM

In order to restore the Azure SQL VM, go to the 'Backup items' option on the Recovery Services Vault and click on 'Azure Virtual Machine'. Once you click on the 'Azure Virtual Machine' option, you will view details of the backup items.

azure virtual machine

As you can see, on the left-hand corner is the name of the Azure Virtual machine. Just click on it, and you will see details of the backup that is available.

sql server

You can also see that the snapshot available is application consistent. As you can see there are three types of snapshot consistency- crash consistent, application consistent and file-system consistent. As the name suggests, with an application consistent snapshot, the VM can be recovered with no data loss and in an entirely consistent state.

Restore the Azure VM

You can click on the option 'Restore VM' which will take you to this screenshot. Here, you can select the restore point.

microsoft azure

When you click on the 'All restore points' option, you will be able to view the different types of snapshot consistent restore points that are available. In our case, as this is just a demo, you see the application consistent snapshot restore point which is available. Select the snapshot restore point and click on 'Ok'. Once done, you will see this screen.

restore configuration

Here, you can see the option to create a new VM using the snapshot or to replace the existing VM. In this tip, we will try the option to replace existing VM. On clicking on the 'Replace existing' option, you will see this screen.

restore configuration

Here, you can see that the disks from the selected restore point will get replaced in your existing VM. Select the 'Restore Type' and the 'Staging Location' which is a temporary location used during the restore process. Click on 'OK'. You may see this validation error.

restore configuration

From the validation error, you see that the VM needs to be shut down for the replace disk operation to occur.

Try replacing disks again after shutting down VM

Just shut down the Azure VM and retry the same replace disk steps as shown before. Once the VM is successfully shutdown, you will see this screen when you try the replace disk option.

restore point

Click on the 'Restore' option and this will take you back to the 'Backup Item' page as shown below.

microsoft azure

Click on 'View all jobs' option and you can see the status of the jobs currently running.

backup jobs

As you can see the backup and restore jobs get triggered at the same time. Backup job will backup the existing VM and restore will replace the existing VM. You can click on each of the jobs to view more details. The status of the backup job is as shown.

microsoft azure

You can see the snapshot of the existing VM is completed and it is waiting on transferring data to the vault. You will also see a new crash consistent snapshot showing up under backup items as you can see a snapshot of a virtual machine that was shutdown is completed. The crash consistent snapshot is the backup of the VM that you had shutdown earlier.

sql server

The status of the restore job is as shown.

microsoft azure

You can see the transfer of the data from vault is completed and the process is waiting on updating the virtual machine. Also, you can see details of the data that (2.12 TBs) is transferred. Both the backup and restore processes take a while to complete.

Status of backup and restore jobs

In this demo, the restore job completed in around 21 minutes.

backup jobs

The backup job is still running and you can see the 'Transfer data to vault' option is still in progress.

vm name

Test the Virtual Machine after restore

As the restore operation is completed, start the VM again as it was shut down for the restore and connect to it. Once done, connect to the SQL instance to validate that the databases – A and B exists.

sql server

You can see that these databases were backed up earlier in the snapshot and the two other databases that we had created after the backup was performed are no longer available. With this, you can confirm that the restore of the Azure VM using the Recovery Services Vault has completed successfully with expected results.

Next Steps
  • In this tip, you saw a reference to a tip to create a new Azure windows SQL VM
  • In the tip, you saw a reference to a tip on backing up SQL databases using Recovery Services Vault
  • In this tip, you saw a reference to a tip on restoring SQL databases using Recovery Services Vault
  • In the demo, you learnt about the different snapshot consistent states that are available
  • In the demo, you saw how we can restore an Azure VM using the snapshots that are available
  • In the demo, you saw how we can view the status of the jobs that are running under the 'Backup Item' section of the Recovery Services Vault
  • Try out this demo on your own Azure environment
  • Refer other articles available on MSSQLTips related to Azure


Last Updated: 2020-05-07


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Process Blob Files Automatically using an Azure Function with Blob Trigger

Using Azure Blueprints to deploy Azure SQL Server and Database with Key Vault Secrets

Managing Azure Blueprints with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools