Configure Managed Backup (SQL Server Management Studio)

Article
08/23/2017

The Managed Backup dialog allows you to configure SQL Server Managed Backup to Microsoft Azure defaults for the instance. This topic describes how to use this dialog to configure SQL Server Managed Backup to Microsoft Azure default settings for the instance and options you must consider when doing so. When SQL Server Managed Backup to Microsoft Azure is configured for the instance, the settings are applied to any new database created thereafter.

If you want to configure SQL Server Managed Backup to Microsoft Azure for a specific database, see Enable and Configure SQL Server Managed Backup to Azure for a Database.

Note

SQL Server Managed Backup is not supported with proxy servers.

Task List

SQL Server Managed Backup to Microsoft Azure Functions Using Managed Backup Interface in SQL Server Management Studio

In this release, you can only configure instance level default settings using the Management Backup interface. You cannot configure SQL Server Managed Backup to Microsoft Azure for a database, pause or resume SQL Server Managed Backup to Microsoft Azure operations, or setup email notifications. For information on how to perform operations not currently supported through the Managed Backup interface, see SQL Server Managed Backup to Azure - Retention and Storage Settings.

Permissions

View Managed Backup Node is SQL Server Management Studio: To view Managed Backup node in Object Explorer, you must either be a System Admin or have the following permissions specifically granted to your user account:

db_backupoperator
VIEW SERVER STATE
ALTER ANY CREDENTIAL
VIEW ANY DEFINITION
EXECUTE on smart_admin.fn_is_master_switch_on.
SELECT on smart_admin.fn_backup_instance_config.

To Configure Managed Backup: to configure SQL Server Managed Backup to Microsoft Azure in SQL Server Management Studio, you must be a System Administrator or have the following permissions:

Membership in db_backupoperator database role, with ALTER ANY CREDENTIAL permissions, and EXECUTE permissions on sp_delete_backuphistory stored procedure.

SELECT permissions on the smart_admin.fn_get_current_xevent_settings function.

EXECUTE permissions on the smart_admin.sp_get_backup_diagnostics stored procedure. In addition, it requires VIEW SERVER STATE permissions as it internally calls other system objects that require this permission.

EXECUTE permissions on smart_admin.sp_set_instance_backup, and smart_admin.sp_backup_master_switch.

Configure SQL Server Managed Backup to Microsoft Azure using SQL Server Management Studio

From the object explorer, expand the Management node, and right click on Managed Backup. Select Configure. This opens the Managed Backup dialog.

Check Enable Managed Backup option and specify the configuration values:

The File retention period is specified in days and should be between 1 and 30.

The SQL Credential you select should match the storage account. If you currently do not have a SQL Credential that stores the authentication information, you can create one by clicking Create. You can also create credential by using the CREATE CREDENTIAL Transact-SQL statement, and provide the storage account name for Identity and the access key for the SECRET parameters. For more information, see Create a Credential.

Specify the Storage URL for the Azure storage account, the SQL Credential that stores the authentication information for the storage account, and the retention period for the backup files.

The storage URL format is: https://<StorageAccount>.blob.core.windows.net/

To set the encryption settings at the instance level, check Encrypt Backup option, and specify the algorithm and a Certificate or Asymmetric Key to use for the encryption. This is set at the instance level is used for all the new databases created once this configuration has been applied.

Warning

This dialog cannot be used to specify the encryption options without configuring SQL Server Managed Backup to Microsoft Azure. These encryption options only apply to SQL Server Managed Backup to Microsoft Azure operations. To use encryption for other backup procedures, see Backup Encryption.

Considerations

If you configure SQL Server Managed Backup to Microsoft Azure at the instance level, the settings are applied to any new database created thereafter. However, existing database will not automatically inherit these settings. To configure SQL Server Managed Backup to Microsoft Azure on previously existing databases, you must configure each database specifically. For more information, see Enable and Configure SQL Server Managed Backup to Azure for a Database.

If SQL Server Managed Backup to Microsoft Azure has been paused using the smart_admin.sp_backup_master_switch, you will see a warning message " Managed Backup is disabled and the current configurations will not take effect..." when you try to complete the configuration. Use the smart_admin.sp_backup_master_switch stored and set the @new_state=1. This will resume SQL Server Managed Backup to Microsoft Azure services and the configuration settings will take into effect. For more information on the stored procedure, see smart_admin.sp_ backup_master_switch (Transact-SQL).

Share via