SQL Server managed backup to Microsoft Azure
Applies to: 
SQL Server - Windows only
SQL Server managed backup to Microsoft Azure manages and automates SQL Server backups to Microsoft Azure Blob storage. You can choose to allow SQL Server to determine the backup schedule based on the transaction workload of your database, or use advanced options to define a schedule. The retention settings determine how long the backups are stored in Azure Blob storage. SQL Server managed backup to Microsoft Azure supports point in time restore for the retention time period specified.
Note
In SQL Server 2016 (13.x), the procedures and underlying behavior of SQL Server managed backup to Microsoft Azure have changed. For more information, see Migrate managed backup settings.
SQL Server managed backup to Microsoft Azure is recommended for SQL Server instances running on Microsoft Azure virtual machines.
Benefits
Currently automating backups for multiple databases requires developing a backup strategy, writing custom code, and scheduling backups. Using SQL Server managed backup to Microsoft Azure, you can create a backup plan by specifying only the retention period and storage location. Although advanced settings are available, they aren't required. SQL Server managed backup to Microsoft Azure schedules, performs, and maintains the backups.
SQL Server managed backup to Microsoft Azure can be configured at the database level or at the SQL Server instance level. When configured at the instance level, any new databases are also backed up automatically. Settings at the database level can be used to override instance level defaults on an individual case.
You can also encrypt the backups for added security, and you can set up a custom schedule to control when the backups are taken. For more information on the benefits of using Microsoft Azure Blob storage for SQL Server backups, see SQL Server backup and restore with Azure Blob Storage.
Prerequisites
Microsoft Azure Storage is used by SQL Server managed backup to Microsoft Azure to store the backup files. The following prerequisites are required:
| Prerequisite | Description |
|---|---|
| Microsoft Azure Account | You can get started with Azure with a free trial before exploring purchase options. |
| Azure Storage account | The backups are stored in Azure Blob Storage associated with an Azure storage account. For step-by-step instructions to create a storage account, see Create a storage account. |
| Blob container | Blobs are organized in containers. You specify the target container for the backup files. You can create a container in the Azure Management Portal, or you use the New-AzureStorageContainerAzure PowerShell command. |
| Shared access signature (SAS) | Access to the target container is controlled by a Shared Access Signature (SAS). For an overview of SAS, see Grant limited access to Azure Storage resources using shared access signatures (SAS). You can create a SAS token in code or with the New-AzureStorageContainerSASToken PowerShell command. For a PowerShell script that simplifies this process, see Simplifying creation of SQL Credentials with Shared Access Signature ( SAS ) tokens on Azure Storage with PowerShell. The SAS token can be stored in a SQL Credential for use with SQL Server managed backup to Microsoft Azure. |
| SQL Server Agent | SQL Server Agent must be running for SQL Server managed backup to Microsoft Azure to work. Consider setting the startup option to automatic. |
| Readable AG secondary | If you're offloading your managed backups to a secondary replica of an Always On availability group, the secondary replica must be set to Readable for managed backups to succeed. |
Components
Transact-SQL is the main interface to interact with SQL Server managed backup to Microsoft Azure. System stored procedures are used for enabling, configuring, and monitoring SQL Server managed backup to Microsoft Azure. System functions are used to retrieve existing configuration settings, parameter values, and backup file information. Extended events are used to surface errors and warnings. Alert mechanisms are enabled through SQL Agent jobs and SQL Server Policy Based Management. The following list of objects include a description of their functionality in relation to SQL Server managed backup to Microsoft Azure.
PowerShell cmdlets are also available to configure SQL Server managed backup to Microsoft Azure. SQL Server Management Studio supports restoring backups created by SQL Server managed backup to Microsoft Azure by using the Restore Database task.
| System object | Description |
|---|---|
msdb |
Stores the metadata, backup history for all the backups created by SQL Server managed backup to Microsoft Azure. |
| managed_backup.sp_backup_config_basic | Enables SQL Server managed backup to Microsoft Azure. |
| managed_backup.sp_backup_config_advanced | Configures advanced settings for SQL Server managed backup to Microsoft Azure, such as encryption. |
| managed_backup.sp_backup_config_schedule | Creates a custom schedule for SQL Server managed backup to Microsoft Azure. |
| managed_backup.sp_ backup_master_switch | Pauses and resumes SQL Server managed backup to Microsoft Azure. |
| managed_backup.sp_set_parameter | Enables and configures monitoring for SQL Server managed backup to Microsoft Azure. Examples: enabling extended events, mail settings for notifications. |
| managed_backup.sp_backup_on_demand | Performs an ad hoc backup for a database that is enabled to use SQL Server managed backup to Microsoft Azure without breaking the log chain. |
| managed_backup.fn_backup_db_config | Returns the current SQL Server managed backup to Microsoft Azure status and configuration values for a database, or for all the databases on the instance. |
| managed_backup.fn_is_master_switch_on | Returns the status of the master switch. |
| managed_backup.sp_get_backup_diagnostics | Returns the events logged by Extended Events. |
| managed_backup.fn_get_parameter | Returns the current values for backup system settings such as monitoring and mail settings for alerts. |
| managed_backup.fn_available_backups | Retrieves available backups for a specified database or for all the databases in an instance. |
| managed_backup.fn_get_current_xevent_settings | Returns the current extended event settings. |
| managed_backup.fn_get_health_status | Returns the aggregated counts of errors logged by Extended Events for a specified period. |
Backup strategy
The following sections describe a backup strategy for SQL Server managed backup to Microsoft Azure.
Backup scheduling
You can specify a custom backup schedule using the system stored procedure managed_backup.sp_backup_config_schedule. If you don't specify a custom schedule, the type of backups scheduled and the backup frequency is determined based on the workload of the database. The retention period settings are used to determine the length of time a backup file should be retained in the storage and the ability to recover the database to a point-in-time within the retention period.
Backup file naming conventions
SQL Server managed backup to Microsoft Azure uses the container that you specify, so you have control over the name of the container. For the backup files, non availability databases are named using the following convention: The name is created using the first 40 characters of the database name, the database GUID without the -, and the timestamp. The underscore character is inserted between segments as separators. The .bak file extension is used for full backup and .log for log backups. For databases in an availability group (AG), in addition to the file naming convention described previously, the AG database GUID is added after the 40 characters of the database name. The AG database GUID value is the value for group_database_id in sys.databases.
Full database backup
SQL Server managed backup to Microsoft Azure agent schedules a full database backup if any of the following conditions is true.
A database is SQL Server managed backup to Microsoft Azure enabled for the first time, or when SQL Server managed backup to Microsoft Azure is enabled with default settings at the instance level.
The log growth since last full database backup is equal to or larger than 1 GB.
The maximum time interval of one week has passed since the last full database backup.
The log chain is broken. SQL Server managed backup to Microsoft Azure periodically checks to see whether the log chain is intact by comparing the first and last LSNs of the backup files. If there's break in the log chain for any reason, SQL Server managed backup to Microsoft Azure schedules a full database backup. The most common reason for log chain breaks is probably a backup command issued using Transact-SQL or through the Backup task in SQL Server Management Studio. Other common scenarios include accidental deletion of the backup log files, or accidental overwrites of backups.
Transaction log backup
SQL Server managed backup to Microsoft Azure schedules a log backup if any of the following conditions is true:
No log backup history can be found. This is usually true when SQL Server managed backup to Microsoft Azure is enabled for the first time.
The transaction log space used is 5 MB or larger.
The maximum time interval of 2 hours since the last log backup is reached.
Anytime the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.
Retention period settings
When enabling backup, you must set the retention period in days: The minimum is 1 day, and maximum is 90 days.
SQL Server managed backup to Microsoft Azure based on the retention period settings, assesses the ability to recover to a point in time in the specified time, to determine what backup files to keep and identifying the backup files to delete. The backup_finish_date of the backup is used to determine and match the time specified by the retention period settings.
Considerations
For a database, if there's an existing full database backup job running, then SQL Server managed backup to Microsoft Azure waits for the current job to be completed before doing another full database backup for the same database. Similarly, only one transaction log backup can be running at a given time. However, a full database backup and a transaction log backup can run concurrently. Failures are logged as Extended Events.
If more than 10 concurrent full database backups are scheduled, a warning is issued through the debug channel of Extended Events. SQL Server managed backup to Microsoft Azure then maintains a priority queue for the remaining databases that require a backup until the all backups are scheduled and completed.
Note
SQL Server managed backup isn't supported with proxy servers.
Supportability
The following support limitations and considerations are specific to SQL Server:
Backup of
master,model, andmsdbsystem databases is supported. Backup oftempdbisn't supported.All recovery models are supported (Full, Bulk-logged, and Simple).
SQL Server managed backup to Microsoft Azure agent only supports database full and log backups. File backup automation isn't supported.
The Microsoft Azure Blob Storage is the only supported backup storage option. Backups to disk or tape aren't supported.
SQL Server managed backup to Microsoft Azure uses the Backup to Block Blob feature. The maximum size of a block blob is 200 GB. But by utilizing striping, the maximum size of an individual backup can be up to 12 TB. If your backup requirements exceed this limit, consider using compression, and test the backup file size before setting up SQL Server managed backup to Microsoft Azure. You can either test by backing up to a local disk or manually backing up to Microsoft Azure storage using the
BACKUP TO URLTransact-SQL statement. For more information, see SQL Server backup to URL for Microsoft Azure Blob Storage.SQL Server managed backup to Microsoft Azure might have some limitations when it's configured with other technologies supporting backup, high availability, or disaster recovery.
Backups of databases in an availability group are copy-only backups.
If you're offloading your managed backups to a secondary replica of an Always On availability group, the secondary replica must be set to Readable for managed backups to stripe multiple files.