Backup and Restore Options for SQL Server in Azure VMs

Notes from the official documentation -

Options Automated Backup Azure Backup for SQL Manual backup
Description * Allows you to schedule regular backups for all databases on a SQL Server VM.
* Backups are stored in Azure storage for up to 30 days.
* Beginning with SQL Server 2016, Automated Backup v2 offers additional options such as configuring manual scheduling and the frequency of full and log backups.
* Enabled by the SQL Server IaaS Agent Extension, which is automatically installed on SQL Server Windows virtual machine images in the Azure portal.
* Can centrally manage backups for multiple servers and thousands of databases.
* All backups are stored and managed in a Recovery Services vault.
* Policy driven backup and retention
* Support for SQL Always On
* Databases can be restored to a specific point in time in the portal.
* It offers a customizable retention policy that can maintain backups for years.
* This feature is currently in public preview.
* RBAC
* Depending on your version of SQL Server, there are various techniques to manually backup and restore SQL Server running on an Azure VM
* Several manual backup and restore options:
- Backup to attached disks
Backup to URL
Managed Backup

Supported SQL versions 2014
2016
2017
2012
2014
2016
2017
All
Requires additional Azure service Yes
Configure backup policy in Azure portal Yes Yes
Restore databases in Azure portal Yes
Manage multiple servers in one dashboard Yes
Point-in-time restore Yes Yes Yes
15-minute Recovery Point Objective (RPO) Yes Yes Yes
Short-term backup retention policy (days) Yes Yes
Long-term backup retention policy (months, years) Yes
Built-in support for SQL Server Always On Yes
Backup to Azure Storage account(s) Yes(automatic) Yes(automatic) Yes(customer managed)
Management of storage and backup files Yes
Backup to attached disks on the VM Yes
Central customizable backup reports Yes
Consolidated email alerts for failures Yes
Customize monitoring based on OMS Yes
Monitor backup jobs with SSMS or Transact-SQL scripts Yes Yes Yes
Restore databases with SSMS or Transact-SQL scripts Yes Yes

Comments