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.
Microsoft Azure stores your information with redundancy on the web. It is a safe and secure environment and simplifies your administrative tasks.
There are two main steps to backup your database in Microsoft Azure:
- Create a Storage with a container in the Microsoft Azure portal.
- 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:
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.
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.
Click the containers link.
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.
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.
If you prefer you prefer the T-SQL, you can use the following sentences:
CREATE CREDENTIAL [sqlshackcredential]
WITH IDENTITY = N'sqlshack',
SECRET = N'asssssddddddffqqq'
Now, backup a local 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.
If you prefer the T-SQL, you can use the following sentences:
BACKUP DATABASE [d1]
TO URL = N'https://sqlshack.blob.core.windows.net/sqlshackcontainer/d1_backup_2015_01_19_171804.bak'
WITH CREDENTIAL = N'sqlshackcredential' , NOFORMAT, NOINIT,
NAME = N'd1-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
If everything is OK, you will be able to see the backup created in the Microsoft Azure portal.
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:
DROP DATABASE D1
Select the Restore Database option.
Figure 19. Restoring options.
Select the Device option and press the ellipsis button.
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.
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.
The second phase will be the database restoring Process, which will restore the information from the backup Stored in the Azure Portal.
You can also restore the database using T-SQL sentences:
RESTORE DATABASE [d1]
FROM URL = N'https://sqlshack.blob.core.windows.net/sqlshackcontainer/d1_backup_2015_01_19_140352.bak'
WITH CREDENTIAL = N'sqlshackcredential' , FILE = 1, NOUNLOAD, STATS = 5
If everything is OK, you will be able to see the database restored.
Figure 26. The local SQL Server Database restored.
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.
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.
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 is also a writer for SQL Server training material for certification exams.
View all posts by Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
- Functions and stored procedures comparisons in SQL Server - February 20, 2017
- Functions vs stored procedures in SQL Server - February 14, 2017
- How to generate random SQL Server test data using T-SQL - January 26, 2017