Export (0) Print
Expand All

Configure Managed Backup (SQL Server Management Studio)

SQL Server 2014

The Managed Backup dialog allows you to configure SQL Server Managed Backup to Windows Azure defaults for the instance. This topic describes how to use this dialog to configure SQL Server Managed Backup to Windows Azure default settings for the instance and options you must consider when doing so. When SQL Server Managed Backup to Windows 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 Windows Azure for a specific database, see Enable and Configure SQL Server Managed Backup for a Database.

In this release, you can only configure instance level default settings using the Management Backup interface. You cannot configure SQL Server Managed Backup to Windows Azure for a database, pause or resume SQL Server Managed Backup to Windows 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 Windows Azure - Retention and Storage Settings.

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 Windows 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.

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 Windows 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.

Caution note Caution

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

Considerations

If you configure SQL Server Managed Backup to Windows 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 Windows Azure on previously existing databases, you must configure each database specifically. For more information, see Enable and Configure SQL Server Managed Backup for a Database.

If SQL Server Managed Backup to Windows 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 Windows Azure services and the configuration settings will take into effect. For more information on the stored procedure, see smart_admin.sp_ backup_master_switch.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft