Microsoft SQL Server Backup to Azure

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, what is Azure SQL server backup and how to perform it to the Azure SQL database service.

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 find out how to backup SQL Azure database to Blob storage.

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

Azure SQL Server backups will be sent 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 an Azure SQL database backup plan using the backup wizard in MSP360’s software for MS SQL Server backup.

FREE WHITEPAPER

SQL Server Backup and Recovery

Check out the ultimate guide with best practices for MSPs:

Backup SQL Azure Database to Blob Storage

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.

1. Install MSP360 Backup for MS SQL Server onto the server with the database, using the default installation options.
2. After installation, launch MSP360 Backup Management Console and click MS SQL Server in the top left-hand corner to launch the backup wizard.
   
3. Choose 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).
   
4. Keep the default options on the next few screens until you get to Select MS SQL Server Instance step. In this step, choose your SQL Server instance and authentication method (Windows Authentication is good for most cases).
   
5. Next, 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

Use Cases

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.

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

1. In MSP360 Backup, click the Image Based button in the top left-hand corner, then choose Local or Cloud Backup as a template.
   
2. In the next step, select the previously created Azure blob storage account as a target, click Next and then select the Image Based Backup option.
3. The 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.
4. Set 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:

1. Replication to a cloud SQL Server instance for high availability or faster disaster recovery.
2. 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.

Conclusion

If you are running a business-critical SQL Server database, we suggest implementing local and off-site Azure SQL Server 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.

If you need a higher RTO, consider implementing Azure Data Sync, which allows you to create geo-spread mixed SQL replication. Data Sync also allows you to lower the RPO to 5 minutes.

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.

Denis G

Denis is a content manager at MSP360. He learned the product and cloud services the hard way - as a support team member. He finds the simplest descriptions to difficult concepts and makes comparisons between services, features, products and other comparisons.

See Full Bio

MSP360 Backup for MS SQL Server

• Full, differential and transaction log backup
• On-site and off-site backup options
• Image-based and system state backup