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

 

Backup SQL Server Databases using Azure Backup - Part 1


By:   |   Last Updated: 2019-06-17   |   Comments (6)   |   Related Tips: More > Azure

Problem

Is there any way by which we can centrally manage database backups across a number of SQL Server instances running on different Azure Virtual Machines (IaaS)? I have heard of a new feature that is available using Azure backups which allows us to easily perform backups of databases on the Azure Virtual Machines (IaaS). Can we have a demo in this tip?

Solution

Azure backup is a zero-infrastructure solution that is available in Azure that can be used to perform database backups of SQL Server instances running on Azure. This is an easy to use tool that can perform the all-important function of backing up the databases in a few simple steps. Comparing this to traditional backup solutions, this utility is far ahead in terms of safeguarding your databases as everything is centrally manageable and offers features like long-term retention and auto protection. The backups are performed using the Recovery Services vault which is the central entity for backing up the resources.

Azure Recovery Services Vault

As a first step, logon to the Azure portal and then set up the recovery services fault. Go to the "All Services" section as shown and search for "Recovery Services Vault".

Azure Recovery Services Vault

Click on the "Recovery Services vaults" option and you will get this window. Click on the "Add" option as shown.

Azure Recovery Services Vault

Once done, enter the details for the Recovery Services Vault as shown. Click on "Create" after filling the details.

Azure Recovery Services Vault

Under the "Notifications" section on the top corner of the Azure portal, you will see confirmation that the deployment completed successfully.

Azure Recovery Services Vault

Click on the "Go to resource" option to view the newly created Recovery Services vault. Once done, you will view the "Overview" section of the Recovery Services vault.

Azure Recovery Services Vault

Click on the "Backup" option to check the details of any backup items. In the screen, you see zero backup items.

Azure Recovery Services Vault

Click next on the +Backup symbol to start your backup goal. You will get this screen as shown. In the first question "Where is your workload running", you will see these options to select.

Azure Recovery Services Vault

In our case, for this demo, we select "Azure". Once done, for the next question "What do you want to backup", you select "SQL Server in Azure VM" as our plan is to back up the SQL Server databases running on an Azure VM.

Azure Recovery Services Vault

Discovery of databases for Azure Recovery Services Vault

Once you select the options for these questions, the next step is to start the discovery process to identify the databases that need to be backed up. Refer the screenshot below. Click on the "Start Discovery" option to start the discovery process.

Azure Recovery Services Vault

This will start the process of discovery and usually takes a few minutes based on the number of servers that are available. In this screenshot, you can see the process running to discover the Azure VMs.

Azure Recovery Services Vault

Once the process completes, you can view the list of servers that were discovered.

Azure Recovery Services Vault

In this demo, we would like to back up the databases on "sql2019". Select this server as shown and click on "Discover DBs". Once done, the deployment process will start which you can view by clicking on the top right hand corner of the portal.

Azure Recovery Services Vault

Once you click on the "Deployment in progress" option, you can view details of the deployment.

Azure Recovery Services Vault

This is an operation that happens in the background in order to manage the backup workload.

Confirm that the databases were discovered correctly for Azure Recovery Services Vault

In order to confirm that the databases were discovered as per your selection, go back to the Recovery Services Vault you created and select the Backup goal (by selecting +Backup) and click on the "View Details" as shown. Here, you can view the confirmation of the selection and the number of databases that are available to be backed up.

Azure Recovery Services Vault

Once you click on "View Details", you can view the confirmation of the backup readiness of the selected server. In our case, it is the demo "sql2019" server.

Azure Recovery Services Vault

Click on the "Backup Goal" option to go back to the menu and select the Step 2 to Configure Backup.

Azure Recovery Services Vault

Configure Backup for Azure Recovery Services Vault

This has two steps first select the items to backup and then select the backup policy. Here, database [C100] is selected to be backed up. Click "OK" once done. You can also see the option Autoprotect. This can be useful especially in dynamic environments where databases are constantly created or deleted. This feature will ensure that no new databases are missed from the backup. With Autoprotect, Azure takes care of the process of discovering the databases and you do not have to do this manually.

Azure Recovery Services Vault

In the next section, configure the backup policy. I have just selected the default, that is, "HourlyLogBackup" and click OK. You can see in this backup policy, the full backup occurs once followed by the hourly log backups.

Azure Recovery Services Vault

Once done, make sure to "Enable Backup" which will start the process for deployment which you can see in the right-hand side top corner of the Azure portal.

Azure Recovery Services Vault

Once the deployment process completes, you can see the status of deployment as shown on the top right-hand corner.

Azure Recovery Services Vault

Reviewing the configuration on the Recovery Services Vault

Now that you have configured the backup of the databases on the Azure VM, we can check the recovery services vault to validate if everything is correct. Go back to the Recovery Services Vault home page as shown to check the backup items that were configured as part of the setup. Refer the screenshot below.

Azure Recovery Services Vault

Once you click on the "Backup Jobs" item on the left you can see that the SQL Server is registered successfully and also the configuration for the backup job for the database c100 is completed successfully. Next click on the "Backup Items" section.

Azure Recovery Services Vault

Click on the "SQL in Azure VM" to check details of the Azure VM. Once done, you can view details of the database -C100 that was configured for performing the backup.

Azure Recovery Services Vault

You can see that the backup status is "Healthy" and when you login to the Azure VM and check the database, you can validate that the full backup for the database has completed successfully.

Azure Recovery Services Vault

I noticed that this backup process takes some time to trigger, but you can validate if it completed successfully as shown above or you can manually trigger the backup from the backup item configuration in the recovery services vault.

Azure Recovery Services Vault

With these steps, you have successfully configured backups for your databases using the Azure Backup for SQL Server on Azure VMs. In the next tip, you will see a demo on how to restore the databases that you configured for backup. You will see in detail step by step instructions for the restore operations using Azure backup.

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 backups can be configured in few simple steps
  • You also learned that this backup solution requires zero infrastructure and can be centrally managed
  • Features like Auto-protect further enables automation as Azure itself takes care of any new databases that are added
  • 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-17


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.



    



Monday, June 24, 2019 - 3:13:05 PM - M Moinudheen Back To Top

Hi Anne,

For Azure SQL DB, we wouldn't have this feature as we don't have to configure backups this way. Backups happen automatically for the Azure SQL DB

Thanks


Monday, June 24, 2019 - 1:08:57 PM - Anne Cao Back To Top

Thanks for the great tip!

One question, do we have to have Azure SQL server VMs service to use this?

If we only have Azure SQL database service, is this feature available?

Thanks


Monday, June 17, 2019 - 5:54:08 PM - Mohammed Back To Top

Hi Vijay,

Thank you for the comment, the part 2 of the tip will have details about restoring databases using Recovery Services Vault.

Stay tuned.

Thanks


Monday, June 17, 2019 - 5:52:53 PM - Mohammed Back To Top

Thank you Srinath


Monday, June 17, 2019 - 12:54:04 PM - Srinath Back To Top

That's a great read. Thanks Moinu for the tip.


Monday, June 17, 2019 - 8:07:02 AM - Vijay Manthri Back To Top

Hi Mohammad,

Thank you for sharing this valuable tip. I hope those who refer to this tip can easily understand the steps to configure SQL backups on Azure.

As next step, can you also let me know if any tip available for restoring the database from Recovery Valut Backup.

Thanking You,

Vijay Manthri


Learn more about SQL Server tools