In the last chapter, we explained how to create a Microsoft Azure Account and how to have a Microsoft Azure Portal.

This time, we will show how to backup our SQL Server Databases to a Microsoft Azure. We are assuming that you already have an Azure account.

Introduction

Microsoft Azure stores your information with redundancy on the web. It is a safe and secure environment and simplifies your administrative tasks.

Getting Started

There are two main steps to backup your database in Microsoft Azure:

1. Create a Storage with a container in the Microsoft Azure portal.
2. Backup your local Database to that container (you will need a credential to connect to the Storage).

Create a Storage and a Container in Microsoft Azure

The first step to backup in Azure is to create a storage.

First, sign in to the Microsoft Azure Portal:

https://manage.windowsazure.com

Click on the Storage link to create a new storage.

Figure 2. The storage in Azure

Create a new storage using the new link.

Figure 3. New Storage creation

Use the Quick create option to create the new storage.

Figure 4. Quick storage creation

For the URL, you can use any name if it does not already exist.

Location/Affinity group, lets you select where you want to store the primary data. It is recommended to select a location closer to your current region. The options available when this article was created were:

• West US
• East US 2
• Central US
• South Central US
• West Europe
• East US
• Southeast Asia
• East Asia
• Japan West
• Japan East

The other options available are:

• Locally redundant storage stores and maintains three copies of your data in a single facility and a single region.
• Geo-Redundant is the default and recommended option. This option lets you create six replicas of your data 3 in your local region and three in other region far away from the other replica.
• Read access geo-redundant creates replicas in a primary region and secondary read-only replica in a second region. In case that the primary replica fails, the secondary replica can be used with read-only permissions.
• Zone-Redundant storage lets you store 2-3 replicas in the same region or two different regions.

Figure 5. Storage options

If everything is OK, a new storage will be created. Click on the sqlshack link just created.

Figure 6. The sqlshack storage created.

Click the containers link.

Figure 7. The containers

Click the Create a container option.

Figure 8. Creating containers

Add a name to the container.

Figure 9. New container information

A new container will be created.

Figure 10. The container URL.

Return to the storage section and click the manage keys option.

Figure 11. Manage keys option

The Manage Access Keys are used to connect to SQL Azure. The use will be explained later.

Figure 12 Azure Storage Access keys

SQL Server Backup in SSMS

We will create a Credential first to connect to Azure.

Figure 13 New Credential

The identity should be the storage name used in figure 5, the Password and Confirm password are the primary keys used in figure 12. Use the primary key as a password and in the confirm password textbox.

Figure 14 Credential information

If you prefer you prefer the T-SQL, you can use the following sentences:

Now, backup a local database.

Figure 15 Back Up Database

In the Back up to option, select URL. In file name, you can write any name or keep the default values. In SQL credential, select the credential created in figure 14. In azure storage container, use the container created in figure 9. Finally, for the URL prefix, used the URL container used in the figure 10. Once this option are set, create the backup.

Figure 16. Destination information

If you prefer the T-SQL, you can use the following sentences:

If everything is OK, you will be able to see the backup created in the Microsoft Azure portal.

Figure 17. The backup created in Microsoft Azure

Testing the backup

In order to test, the backup created, let’s test the backup.

Delete the database to test the backup.

Figure 18. Dropping a Database for testing purposes

If you prefer the T-SQL, you can use the following sentences:

Select the Restore Database option.

Figure 19. Restoring options.

Select the Device option and press the ellipsis button.

Figure 20. Restoring options

In the Backup media type, select the URL option and press the Add Button.

Figure 21. Restoring to URL.

In the Storage Account select the Storage name used in figure 5, in access keys, select the access key of the figure 12. Keep the use of HTTPS option. In the select an existing credential, use the credential created in figure 14 and press Connect.

Figure 22. URL an Credential settings to restore a database

In Containers, select the container created in figure 9 and then on the right pane, select the backup created in figure 17.

Figure 23. Selecting the backup from Microsoft Azure.

Once done, create the backup. The first phase a recovering plan will be created. The restore plan will retrieve all the information related to the backup in the Azure Portal.

Figure 24. Creating the restore plan.

The second phase will be the database restoring Process, which will restore the information from the backup Stored in the Azure Portal.

Figure 25. Restoring the database.

You can also restore the database using T-SQL sentences:

If everything is OK, you will be able to see the database restored.

Figure 26. The local SQL Server Database restored.

Final Recommendations

Your internet connectivity is critical if you want to work with Microsoft Azure. If your internet connectivity is not trustable, your backup and recovery plan will not be trustable either. The same for the Internet Speed. If your Internet Connectivity is not fast, restoring a 32 GB database will take hours. Test your Database in a testing environment before taking a decision to migrate to Azure.

Conclusion

In this article, we show how to create a storage in Microsoft Azure, how to create a container, how to backup a local SQL Server Database to that storage and finally how to restore the database stored from the Microsoft Azure Storage.

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023