Backup and Restore for SQL Server in Azure Virtual Machines
Azure Storage maintains 3 copies of every Azure VM disk to guarantee protection against data loss or physical data corruption. Thus, unlike on-premises, you don't need to worry about these. However, you should still backup your SQL Server databases to protect against application or user errors (e.g inserting wrong data or deleting a table) and being able to restore to a point in time.
Azure has two different deployment models for creating and working with resources: Resource Manager and classic. This article covers using both models, but Microsoft recommends that most new deployments use the Resource Manager model.
For SQL Server running in Azure VMs, you can use native backup and restore techniques using attached disks for the destination of the backup files. However, there is a limit to the number of disks you can attach to an Azure virtual machine, based on the size of the virtual machine. There is also the overhead of disk management to consider.
Beginning with SQL Server 2014, you can back up and restore to Microsoft Azure Blob storage. SQL Server 2016 also provides enhancements for this option. In addition, for database files stored in Microsoft Azure Blob storage, SQL Server 2016 provides an option for nearly instantaneous backups and for rapid restores using Azure snapshots. This article provides an overview of these options, and additional information can be found at SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.
For a discussion of the options for backing up very large databases, see Multi-Terabyte SQL Server Database Backup Strategies for Azure Virtual Machines.
The sections below include information specific to the different versions of SQL Server supported in an Azure virtual machine.
SQL Server Virtual Machines
When your SQL Server instance is running on an Azure Virtual Machine, your database files already reside on data disks in Azure. These disks live in Azure Blob storage. So the reasons for backing up your database and the approaches you take change slightly. Consider the following.
- You no longer need to perform database backups to provide protection against hardware or media failure because Microsoft Azure provides this protection as part of the Microsoft Azure service.
- You still need to perform database backups to provide protection against user errors, or for archival purposes, regulatory reasons, or administrative purposes.
- You can store the backup file directly in Azure. For more information, see the following sections that provide guidance for the different versions of SQL Server.
SQL Server 2016
Microsoft SQL Server 2016 supports backup and restore with Azure blobs features found in SQL Server 2014. But it also includes the following enhancements:
|Striping||When backing up to Microsoft Azure blob storage, SQL Server 2016 supports backing up to multiple blobs to enable backing up large databases, up to a maximum of 12.8 TB.|
|Snapshot Backup||Through the use of Azure snapshots, SQL Server File-Snapshot Backup provides nearly instantaneous backups and rapid restores for database files stored using the Azure Blob storage service. This capability enables you to simplify your backup and restore policies. File-snapshot backup also supports point in time restore. For more information, see Snapshot Backups for Database Files in Azure.|
|Managed Backup Scheduling||SQL Server Managed Backup to Azure now supports custom schedules. For more information, see SQL Server Managed Backup to Microsoft Azure.|
For a tutorial of the capabilities of SQL Server 2016 when using Azure Blob storage, see Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases.
SQL Server 2014
SQL Server 2014 includes the following enhancements:
Backup and Restore to Azure:
- SQL Server Backup to URL now has support in SQL Server Management Studio. The option to back up to Azure is now available when using Backup or Restore task, or maintenance plan wizard in SQL Server Management Studio. For more information, see SQL Server Backup to URL.
- SQL Server Managed Backup to Azure has new functionality that enables automated backup management. This is especially useful for automating backup management for SQL Server 2014 instances running on an Azure Machine. For more information, see SQL Server Managed Backup to Microsoft Azure.
- Automated Backup provides additional automation to automatically enable SQL Server Managed Backup to Azure on all existing and new databases for a SQL Server VM in Azure. For more information, see Automated Backup for SQL Server in Azure Virtual Machines.
- For an overview of all the options for SQL Server 2014 Backup to Azure, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.
- Encryption: SQL Server 2014 supports encrypting data when creating a backup. It supports several encryption algorithms and the use osf a certificate or asymmetric key. For more information, see Backup Encryption.
SQL Server 2012
For detailed information on SQL Server Backup and Restore in SQL Server 2012, see Backup and Restore of SQL Server Databases (SQL Server 2012).
Starting in SQL Server 2012 SP1 Cumulative Update 2, you can back up to and restore from the Azure Blob Storage service. This enhancement can be used to back up SQL Server databases on a SQL Server running on an Azure Virtual Machine or an on-premises instance. For more information, see SQL Server Backup and Restore with Azure Blob Storage Service.
Some of the benefits of using the Azure Blob storage service include the ability to bypass the 16 disk limit for attached disks, ease of management, the direct availability of the backup file to another instance of SQL Server instance running on an Azure virtual machine, or an on-premises instance for migration or disaster recovery purposes. For a full list of benefits to using an Azure blob storage service for SQL Server backups, see the Benefits section in SQL Server Backup and Restore with Azure Blob Storage Service.
For Best Practice recommendations and troubleshooting information, see Backup and Restore Best Practices (Azure Blob Storage Service).
SQL Server 2008
For SQL Server Backup and Restore in SQL Server 2008 R2, see Backing up and Restoring Databases in SQL Server (SQL Server 2008 R2).
For SQL Server Backup and Restore in SQL Server 2008, see Backing up and Restoring Databases in SQL Server (SQL Server 2008).
If you are planning your deployment of SQL Server in an Azure VM, you can find provisioning guidance in the following tutorial: Provisioning a SQL Server Virtual Machine on Azure with Azure Resource Manager.
Although backup and restore can be used to migrate your data, there are potentially easier data migration paths to SQL Server on an Azure VM. For a full discussion of migration options and recommendations, see Migrating a Database to SQL Server on an Azure VM.