Backup and restore SQL Server to Azure Blob storage

Microsoft Azure provides storage services, where you can store any kind of data. You can also take a backup of your database in the Azure storage. In this article, you will learn how you can backup and restore SQL Server to Azure Blob storage.

• How to create a storage account in Azure
• SQL Server backup to Azure blob storage
• SQL Server restore database from Azure blob storage

For storing your SQL Server backup on the Azure blob storage you need to create a storage account in Microsoft Azure. The below section will help you to create a storage account in Microsoft Azure. You can skip this section if you already have a storage account.

Read Backup Azure database to local SQL Server

How to create a storage account in Azure

In this section, we will explain the step-by-step procedure for creating a storage account in Microsoft Azure. We will be creating the storage account from the Azure portal.

This method is very easy and convenient to use. You can follow the below steps to create a storage account in Microsoft Azure from the portal:

• Open the Azure portal and login with your Azure account.
• In the Azure Dashboard, open the side navigation bar and then click on Storage accounts. Then you will be able to manage the existing storage accounts or create a new storage account.

• Click on Create to create a new storage account.

• Now select the Subscription type under which you will create the storage account.
• Select the resource group under which you will create the storage account. You can click on Create New if you want to create a new resource group.
• Define a name for the storage account and the geographical region of the data center.
• Select the Performance type according to your requirements. For this demo, I will be using the Standard performance.
• Select the type of Redundancy according to your needs. I will choose the Locally-redundant storage.

• After setting all of the above options, click on Next to go to the Advanced tab.
• In this Advanced tab, there are multiple security options that you can set according to your requirement under the Security option.
• Under the Blob storage, select the access tier. I will be using the Cool access tier.

• Click on Next to go the Networking tab.
• Under the Networking tab, select the connectivity method i.e. which users you want to connect to this storage account. You can use any Private endpoint or a Public endpoint.
• Also select the type of network routing. If you do not know about this you can choose the Microsoft Network routing because this option is recommended by Microsoft.

• Click on Next to go to the Data Protection tab.
• Under the Data Protection tab, you will be provided a number of features the protection of your data from any kind of accidental deletion or modification. You can enable these features according to your needs.

• Click on Next. You can create tags here to group the resources. Click on Next.
• Finally, review your storage account configurations. Once reviewed, click on Create to create the storage account.

• It will take some time to create the storage account. You have to wait for a while.
• Now you can navigate to the Storage accounts from the navigation bar and navigate to your storage account in the accounts list.

Thus, you might have learned how to create a storage account in Microsoft Azure from the portal.

Read How to Create a Single Database in Azure SQL

SQL Server backup to Azure blob storage

You can store the backup of your database created on a local SQL server on the Azure blob storage. Follow the below steps to create a backup of your SQL Server database to the Azure blob storage:

• Open the Azure portal and login with your Azure account.
• Navigate to Storage Accounts and select the storage account that you want to use for the backup.
• Navigate to Containers in the side navigation bar and click on +Container. Specify the name and the public access level for your new container and then click on Create to create a new container. See the below image for reference.

• Now open the SQL Server Management Studio and connect to your local SQL Server.
• Once you have connected to the server, right click on the database whose backup you want to create in the Object Explorer Window. Click on Tasks and then click on Back Up… to open the Back Up database wizard.

• In the Destination drop-down menu, select URL. Click on Add to add a new URL where you will save the database backup.

• Now you will be asked to specify the destination location for the backup database. Click on New Container.
• Click on Sign In and you will see a prompt asking you to sign in to your Microsoft account.
• Once you have signed in, select the subscription type to use, the name of your storage account and the blob container where you want to store the backup. Refer the below image.

• Click on Create Credential to generate the Shared Access Signature. Note down this shared access signature as you will need it while restoring the database.
• Click on OK and the backup process will start.
• Once the backup process is complete, you can verify the backup database on the Azure portal.
• Open the Azure portal and navigate to your container. You can see that the backup database file is successfully created.

Thus, you might have learned how to backup your database to Azure blob storage.

Read How to rename a database in Azure SQL

SQL Server restore database from Azure blob storage

In the above section, we explained how to backup your database to the Azure blob storage. Now we will explain how you can restore the backed-up database from the Azure blob storage to the local SQL Server.

• Connect to the SQL Server instance from SQL Server Management Studio.
• Expand the SQL Server instance in the object explorer window and right click on Databases. Click on Restore Database to open the Restore Database wizard.

• Select Device as the source database and click on the browse option to specify the device details.
• In the Select backup devices dialog box, select the Backup media type as URL and click on Add to add a new device.
• In the Select a backup file location dialog box ,you have to specify the link to your azure blob container and the shared access signature to access the azure blob container.

• Click on Add to add a new credential and create access to the azure blob container. Firstly, you have to sign in to your Azure account.
• Once you have signed into the Azure account, you have to select the subscription type, storage account name, blob container name and the shared access signature token. You can click on Create Credential to generate the shared access signature token. Click OK, once you have filled all the inputs.

• Now you will be asked to browse the backup file in your azure blob container. Select the backup file and click on OK.

• Firstly, the backup source media wil be verified.
• Also, make sure to verify the destination database name and the point of time to restore. You can restore to an existing database or a new one.
• Then the restore process will start and will take some time to complete.
• Once the restore database process is complete, you can verify that the database is restored.

You can like the below articles:

• How to create table in azure sql database
• Connect to Azure SQL database using Python
• Cannot open server requested by the login. client is not allowed to access the server
• Cannot open backup device operating system error 50 azure
• Azure SQL database column encryption
• Azure SQL database configure firewall

Thus, you might have learned how to back up a database to the Azure blob container and also restore it.

• How to create a storage account in Azure
• SQL Server backup to Azure blob storage
• SQL Server restore database from Azure blob storage