Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Restore SQL Server Databases on Azure VM (IaaS) using Azure Backup - Part 2


By:   |   Last Updated: 2019-06-27   |   Comments   |   Related Tips: More > Azure

Problem

In the previous tip, you described in detail the steps required to perform backups of databases running SQL Server on Azure Virtual machines. I noticed that the database backups are running successfully. However, I would like to perform restore of the databases using the backups and test if they are indeed working as expected. Can we have a detailed demo in this tip for performing a database restore?

Solution

In the previous tip, we had configured Azure backups for a demo database named "C100". We also confirmed as part of the demo that the backups are running successfully. Assuming in this demo, that you are planning to restore the database using the Azure backups, let us go through the steps one by one. Similar to how you performed the backups using Azure, the restore process is also simple which can be completed in a few easy steps.

Restore the database using the backups performed using Azure backup

As a first step, logon to the Azure portal and then go to the recovery services vault. As you had already configured the "Recovery Services Vault" to perform the backups, you will be able to view it in your resource group.

restore databases with azure backup

Click on the "Recovery Services Vault" which you configured. In this demo, click on "RSVaultTips", and click on the "Backup Items" option on the left-hand pane. Once done, click on the "SQL in Azure VM" backup type.

restore databases with azure backup

Once you click on the counter (2) – "SQL in Azure VM", you will see this window.

restore databases with azure backup

Here, you can view the database "C100" which has a backup status of "Healthy".

Restore as a new database

In order to begin the restore process, click on the symbol on the right as shown to view the options available.

restore databases with azure backup

As shown, you can view the options that are available to perform the database restore. Click on the "Restore DB" option as shown. You will see this screen with these options.

Restore New DB Name

In this demo, we are restoring the database on to the same server where the database is being backed up. We could also restore the database on to another SQL Server on an Azure VM. We are restoring the database with a new name as shown. Click on ‘OK’. Once done, it will take you to this screen where you can select the "Restore point".

restore databases with azure backup

In this example, we are using the last full backup as a restore point. Click on the "Time" option of "Full Backup" as shown in the screenshot and click on "Ok". Once done, it will take you to the "Advanced Configuration" section as shown.

restore databases with azure backup

Review this screen, to confirm if you want to restore the database with the "NoRecovery" option or not and verify the target locations if they are available. Click on "Ok". Finally, click on the "Restore" option to begin the process of restoration.

restore databases with azure backup

Database Restore Process from Azure

Once you click on the "Restore" option, the restore trigger gets initiated. You can view the progress of the trigger on the right-hand top corner of the Azure portal.

restore databases with azure backup

Once the restore is triggered successfully for the database, you can view the confirmation on the notifications pane on the right-hand top corner of the Azure portal as shown.

restore databases with azure backup

From the above screenshot, you can also see the information regarding monitoring progress of the restore in the backup jobs page. Once the restore process is triggered, you can view the progress of the restore process under the "Backup Jobs" pane on the left-hand side of the "Recovery Services Vault".

restore databases with azure backup

Here, you can view the status of the restore process as "In Progress". You can also view the status of the other backup operations that have completed successfully. You can check the status of the restore process on this window and you will notice that the status gets changed to "Completed" once the restore process is done. Refer to the screenshot below for status change on completion.

restore databases with azure backup

Click on the "Completed" status to check detailed information of the restore process.

restore databases with azure backup

As seen from the screenshot above, you can view summary information of the restore process.

Check Restored Database

Now that the database is restored, you can logon to the SQL Server in order to confirm that the database is indeed restored. Refer to the screenshot below of the restored database on the destination server.

 restore databases with azure backup

You can query the msdb database in order to view information of the database restore.

select * from msdb.dbo.restorehistory
order by restore_date desc
restore databases with azure backup

Here, you can view details of a login named – [NT Service\AzureWLBackupPluginSvc] which was used by the restore process. This login gets created automatically as part of the Azure backups when backups are configured first and has sysadmin access on the SQL Server instance.

Overwrite existing database with "NoRecovery" and with Point in Time recovery option

In the previous example, you restored the database on the same server, but with a new name. In this demo, we will try overwriting the existing database with the "NoRecovery" option and also perform a point in time restore activity. As you know with the "NoRecovery" restore option database will not be accessible after restoration. Go back to the "Backup Items" blade as done before and click on the "Restore DB" option.

restore databases with azure backup

In the next screen to restore the database, click on the option to "Overwrite" the database as shown and click "OK".

restore databases with azure backup

This will take you to the "Restore Point" section, where you can perform the point in time restore of the database as shown.

restore databases with azure backup

Here, you select the restore point as 8:28 PM and click on "OK".

restore databases with azure backup

In this window, you select the "NoRecovery" option as Enabled which means the database will not be accessible after the restore. Click on "Ok" and then the "Restore" button to trigger the restore process. Similar to the first example while restoring with a new database name, you can check the status and progress of the restore jobs in the "Backup Jobs" pane of the Recovery Services Vault. Once the restore process completes, you can view the status of the database on the destination server. Below is the screenshot of the database after the restore process completed.

restore databases with azure backup

You can see that the database status is "Restoring" as we had selected the "NoRecovery" option for the database restore. When you query the msdb database, you will view the details as shown.

restore databases with azure backup

As you can see from the last column "stop_at" which we used for performing point in time restore of the database. If you look at the previous screenshots, we had selected the point of recovery as 8:28 PM.

With these examples, you have successfully restored databases on to SQL Server running on Azure VMs using the backups that were configured using Azure backups. As you can see, the process to perform these restore operations were simple and required hardly any kind of high-level technology expertise.

Next Steps
  • In this tip, you learned about using the Azure backup for SQL Server on Azure VM
  • In this tip, you learned how the database restores can be performed using simple steps
  • You learnt that this restore solution requires zero infrastructure and can be centrally managed
  • You learnt how to restore the backup with a new database name
  • You learnt how to perform the restore by overwriting the existing database
  • You also learnt how easily point in time restores can be done using the database backups
  • Try out the Azure backup feature described in this tip and explore the options using a demo database
  • Refer to the different Azure articles available on MSSQLTips


Last Updated: 2019-06-27


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools