Enhanced Automated Backup for SQL Server 2016 in Azure Virtual Machines

Inlägg på 2 februari, 2017

Program Manager II, Azure SQL

We are excited to announce some great enhancements to our Automated Backup feature, which greatly extends your control over backups when running SQL Server 2016 in Azure Virtual Machines. You can now control the schedule of your backups and backup system databases. You can easily enable this feature through the Azure Portal or PowerShell on Azure Virtual Machines running SQL Server 2016 Enterprise, Standard, or Developer.

For those of you not familiar with Automated Backup, this feature allows you to automatically backup all the databases in a SQL Server VM running in Azure to one of your storage accounts. Automated Backup ensures a consistent backup chain at all times, so you can always recover your databases to any point in time. Even better, it manages the desired retention for the backups, keeping them only for the time you specify. If you’re curious, Automated Backup is implemented on top of the SQL Server IaaS Agent Extension and the SQL Server Managed Backup feature.

New capabilities

Backup system databases

Automated Backup now gives you the option to schedule backups for System databases in addition to User databases. If you choose to enable this option, your System databases, and all their important instance-level objects, will be backed up on the same schedule as your User databases.

Scheduling backups

Automated Backup now allows you to schedule a time window and frequency, daily or weekly, for full backups so that these don’t impact performance during business hours. In addition, you can specify how often to take log backups.

Remember that Azure Storage keeps 3 copies of every VM disk to guarantees no data loss, thus the purpose of these backups is to recover from human errors (e.g. deleting a table).

For disaster recovery purposes or compliance reasons, consider storing these backups in a geo-replicated storage account, preferably readable. This will make the backups available also in a remote Azure region.

How to find Automated Backup v2

For new SQL Server VMs

When creating a new virtual machine running SQL Server 2016 in the Azure portal, you will be presented with several SQL Server configuration options. Here you can enable and configure Automated Backup according to your requirements.

Configure Automated Backup v2 when creating a SQL VM

For existing SQL Server VMs

If you have an existing virtual machine running SQL Server 2016, you can enable and configure Automated backup v2 from the Azure Portal or PowerShell. If you find your SQL Server virtual machine in the Azure Portal, you can find Automated Backup under SQL Server configuration.

Configure Automated Backup v2 for an existing SQL VM

To learn more, check out the documentation for this feature.

Try out this feature today in the Azure Portal!

If you do not have an Azure subscription, you can easily sign up for a free trial!