In this article, we will provide an overview of methods for backing up Microsoft SQL Server databases to Microsoft Azure. This information is helpful for both backup and disaster recovery procedures. You will learn how to configure database backup to the cloud, how to implement a disaster recovery solution using Azure VM instances and how to back up SQL Server databases to the Azure SQL database service.
Table of Contents
SQL Server Database Backup to Azure Blob Storage
Reasons for Backing Up to Azure
Cloud storage is typically used to provide an additional level of protection in case something goes completely wrong with local data backups. This is why users normally choose full or differential SQL Server backups for backups to the cloud. Transaction log (T-log) backups are a part of “hot” data backups that can be used first during disaster recovery, and which therefore make most sense to store on-site.
Let's take a look at how Azure Blob Storage can host your database copies.
BLOB is an acronym that stands for Binary Large OBject. To put it in plain English, a blob is a file. Thus, Microsoft Azure Blob Storage is a type of storage for files.
Further reading Azure as a Backup Storage Destination
Storing backups in Azure cloud allows you to eliminate hardware-related headaches and provides access to unlimited storage space. More importantly, it gives you a flexible disaster recovery solution, which can be implemented within a few hours.
Microsoft SQL Server will send your backups to a dedicated Azure container, which you need to configure.
Now, let’s take a look at how to set up an Azure-based backup environment for Microsoft SQL Server and create a backup plan for your SQL Server databases using the backup wizard in MSP360’s software for MS SQL Server backup.
How to Back Up SQL Server Databases to Azure
First, you need a Microsoft Account in order to gain access to Azure Cloud. Create an Azure account if you don't have one (a free trial is available). If you run into any difficulties, check our separate guide.
Note: We will use Microsoft SQL Server 2017 in this example, but most steps are similar for earlier versions of SQL Server.
1Install MSP360 Backup for MS SQL Server onto the server with the database, using the default installation options.
2After installation, launch MSP360 Backup Management Console and click MS SQL Server in the top left-hand corner to launch the backup wizard.
3Choose Local or Cloud Backup and click Add new storage account to connect to Azure Blob. You will need to provide an account and shared key (you can create these in the Home - Storage accounts section, or just type “storage account” into the search field).
4Keep the default options on the next few screens until you get to Select MS SQL Server Instance step. On this step, choose your SQL Server instance and authentication method (Windows Authentication is good for most cases).
5Next, choose which databases you need to back up. You can choose Back up all user databases only in order to skip system databases and protect only the users’ data.
If you do not configure a regular backup plan, consider checking the copy-only flag, which allows for the creation of a data copy with no effect on previous backup chains; it will therefore not truncate transaction logs.
6 On the following screens of the backup wizard you can specify other backup options, such as compression, deduplication, retention policy, scheduling, etc. Then, click Finish to complete the configuration wizard.
Image-Based Backup of SQL Server to Azure VM
If you have an Azure cloud subscription, you can implement a disaster recovery solution for SQL Server even more easily, as well as for any other server apps. MSP360 Backup allows you to create image-level server backups and place them into Azure as a VM instance.
The key difference between this approach and regular cloud backup is that you can instantly run a server instance as a virtual machine in the cloud and use it to restore server operations.
In such cases, you only need to change the path to the SQL database for web apps or clients, then continue normal operations. Although the overall performance will be slower, your users will be able to continue their work.
You do not need to create a separate backup plan: you can use one image-level backup for system restoration as well as for cloud-based disaster recovery.
Here is how it works:
Create an image-level backup for the server with Microsoft SQL Server and point to Azure Blob Storage as the target backup storage. You can even use Amazon S3 as a storage for your backup and still be able to restore the backup to Azure VM, if desired. However, storing the data in one single cloud storage location will improve restoration performance, because you won’t have to wait for data to transfer between clouds.
Follow the steps below to create an image-based backup of your SQL Server.
How to Create an Image-Based Backup of SQL Server
1In MSP360 Backup, click the Image Based button in the top left-hand corner, then choose Local or Cloud Backup as a template.
2In the next step, select the previously created Azure blob storage account as a target, click Next and then select the Image Based Backup option.
3The Select Partitions step allows you to determine whether MSP360 Backup should create a backup of only selected partitions, only system ones, or all partitions present.
We suggest using the Backup All Drives option only in situations where SQL data is stored on a separate partition. This will protect you from mistakes in the future.
4Set the rest of the backup options on the next few steps of the wizard, such as block-level backup (which backs up only modified image blocks to decrease the amount of data transferred to the cloud), compression, deduplication, scheduling, etc. Then, finish the creation of the backup plan.
SQL Server Restore to Azure VM
In case you ever need to quickly recover the entire SQL server from malfunction, just run Restore to Azure VM wizard from the home panel.
Follow the wizard’s steps as described in the How to Restore to Azure VM Using MSP360 Backup guide, to complete the restoration.
Backup to Azure SQL Database
There is one more SQL Server database cloud backup scenario worth mentioning: migration to Azure SQL database. We can split such situations into two cases:
- Replication to a cloud SQL Server instance for high availability or faster disaster recovery.
- Copying an existing local SQL database to a cloud SQL service if you would like to move the entire database to the cloud.
For replication to a VM-based SQL instance, you can use Azure Data Sync client, which allows you to:
- Optimize network transfers.
- Connect to a database behind a firewall.
- Perform replication between cloud-based and on-premises SQL Servers.
- Replicate the data as frequently as every five minutes.
To avoid sync issues, you need to provision the database schema first. Learn how to do that in this configuration guide from Microsoft.
If you want to migrate a local database to Azure, you only need to create an empty SQL database in Azure, then use the Data Migration Assistant (DMA) to import the local database. SQL compatibility mode is available for imported databases with an older schema version. Here is a full configuration guide by Microsoft.
If you are running a business-critical SQL Server database, we suggest implementing local and off-site backup schemes (you can read more in our 3-2-1 backup strategy tutorial); this allows you to restore the data in any situation, as well as to resume an entire SQL Server using an Azure VM.
Remember to implement local SQL Server backups using full/differential and T-log backups as a “hot” data protection measure. See for yourself how MSP360 Backup for MS SQL Server can simplify your data protection management using our free 15-day trial.