Edit

Share via

managed_backup.sp_backup_config_advanced (Transact-SQL)

  • Article

Applies to: SQL Server 2016 (13.x) and later versions

Configures advanced settings for SQL Server managed backup to Microsoft Azure.

Transact-SQL syntax conventions

Syntax

EXEC managed_backup.sp_backup_config_advanced
    [ @database_name = ] 'database_name'
    , [ @encryption_algorithm = ] 'name of the encryption algorithm'
    , [ @encryptor_type = ] { 'CERTIFICATE' | 'ASYMMETRIC_KEY' }
    , [ @encryptor_name = ] 'name of the certificate or asymmetric key'
    , [ @local_cache_path = ] 'NOT AVAILABLE'
[ ; ]

Arguments

[ @database_name = ] 'database_name'

The database name for enabling managed backup on a specific database.

If @database_name is set to NULL, the settings are applied at instance level (applies to all new databases created on the instance).

[ @encryption_algorithm ] = 'name of the encryption algorithm'

The name of the encryption algorithm used during the backup to encrypt the backup file. @encryption_algorithm is sysname. It's a required parameter when configuring SQL Server managed backup to Microsoft Azure for the first time for the database. Specify NO_ENCRYPTION if you don't wish to encrypt the backup file. When you change the SQL Server managed backup to Microsoft Azure configuration settings, this parameter is optional. If the parameter isn't specified, the existing configuration values are retained. The allowed values for this parameter are:

  • AES_128
  • AES_192
  • AES_256
  • TRIPLE_DES_3KEY
  • NO_ENCRYPTION

For more information on encryption algorithms, see Choose an encryption algorithm.

[ @encryptor_type = ] { 'CERTIFICATE' | 'ASYMMETRIC_KEY' }

The type of encryptor, which can be either CERTIFICATE or ASYMMETRIC_KEY. @encryptor_type is nvarchar(32). This parameter is optional if you specify NO_ENCRYPTION for the @encryption_algorithm parameter.

[ @encryptor_name = ] 'name of the certificate or asymmetric key'

The name of an existing certificate or asymmetric key to use to encrypt the backup. @encryptor_name is sysname. If using an asymmetric key, it must be configured with Extensible Key Management (EKM). This parameter is optional if you specify NO_ENCRYPTION for the @encryption_algorithm parameter.

For more information, see Extensible Key Management (EKM).

[ @local_cache_path = ] 'NOT AVAILABLE'

This parameter isn't yet supported.

Return code values

0 (success) or 1 (failure).

Permissions

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

Examples

The following example sets advanced configuration options for SQL Server managed backup to Microsoft Azure for the instance of SQL Server.

USE msdb;
GO

EXEC managed_backup.sp_backup_config_advanced @encryption_algorithm = 'AES_128',
    @encryptor_type = 'CERTIFICATE',
    @encryptor_name = 'MyTestDBBackupEncryptCert'
GO

Related content